Module | Sequel::Plugins::RcteTree |
In: |
lib/sequel/plugins/rcte_tree.rb
|
The rcte_tree plugin deals with tree structured data stored in the database using the adjacency list model (where child rows have a foreign key pointing to the parent rows), using recursive common table expressions to load all ancestors in a single query, all descendants in a single query, and all descendants to a given level (where level 1 is children, level 2 is children and grandchildren etc.) in a single query.
The rcte_tree plugin adds four associations to the model: parent, children, ancestors, and descendants. Both the parent and children are fairly standard many_to_one and one_to_many associations, respectively. However, the ancestors and descendants associations are special. Both the ancestors and descendants associations will automatically set the parent and children associations, respectively, for current object and all of the ancestor or descendant objects, whenever they are loaded (either eagerly or lazily). Additionally, the descendants association can take a level argument when called eagerly, which limits the returned objects to only that many levels in the tree (see the Overview).
Model.plugin :rcte_tree # Lazy loading model = Model.first model.parent model.children model.ancestors # Populates :parent association for all ancestors model.descendants # Populates :children association for all descendants # Eager loading - also populates the :parent and children associations # for all ancestors and descendants Model.where(id: [1, 2]).eager(:ancestors, :descendants).all # Eager loading children and grandchildren Model.where(id: [1, 2]).eager(descendants: 2).all # Eager loading children, grandchildren, and great grandchildren Model.where(id: [1, 2]).eager(descendants: 3).all
You can override the options for any specific association by making sure the plugin options contain one of the following keys:
:parent : | hash of options for the parent association |
:children : | hash of options for the children association |
:ancestors : | hash of options for the ancestors association |
:descendants : | hash of options for the descendants association |
Note that you can change the name of the above associations by specifying a :name key in the appropriate hash of options above. For example:
Model.plugin :rcte_tree, parent: {name: :mother}, children: {name: :daughters}, descendants: {name: :offspring}
Any other keys in the main options hash are treated as options shared by all of the associations. Here‘s a few options that affect the plugin:
:key : | The foreign key in the table that points to the primary key of the parent (default: :parent_id) |
:primary_key : | The primary key to use (default: the model‘s primary key) |
:key_alias : | The symbol identifier to use for aliasing when eager loading (default: :x_root_x) |
:cte_name : | The symbol identifier to use for the common table expression (default: :t) |
:level_alias : | The symbol identifier to use when eagerly loading descendants up to a given level (default: :x_level_x) |
Create the appropriate parent, children, ancestors, and descendants associations for the model.
# File lib/sequel/plugins/rcte_tree.rb, line 77 77: def self.apply(model, opts=OPTS) 78: model.plugin :tree, opts 79: 80: opts = opts.dup 81: opts[:class] = model 82: opts[:methods_module] = Module.new 83: model.send(:include, opts[:methods_module]) 84: 85: key = opts[:key] ||= :parent_id 86: prkey = opts[:primary_key] ||= model.primary_key 87: ka = opts[:key_alias] ||= :x_root_x 88: t = opts[:cte_name] ||= :t 89: c_all = if model.dataset.recursive_cte_requires_column_aliases? 90: # Work around Oracle/ruby-oci8 bug that returns integers as BigDecimals in recursive queries. 91: conv_bd = model.db.database_type == :oracle 92: col_aliases = model.dataset.columns 93: model_table = model.table_name 94: col_aliases.map{|c| SQL::QualifiedIdentifier.new(model_table, c)} 95: else 96: [SQL::ColumnAll.new(model.table_name)] 97: end 98: 99: bd_conv = lambda{|v| conv_bd && v.is_a?(BigDecimal) ? v.to_i : v} 100: 101: key_array = Array(key) 102: prkey_array = Array(prkey) 103: if key.is_a?(Array) 104: key_conv = lambda{|m| key_array.map{|k| m[k]}} 105: key_present = lambda{|m| key_conv[m].all?} 106: prkey_conv = lambda{|m| prkey_array.map{|k| m[k]}} 107: key_aliases = (0...key_array.length).map{|i| "#{ka}_#{i}""#{ka}_#{i}"} 108: ka_conv = lambda{|m| key_aliases.map{|k| m[k]}} 109: ancestor_base_case_columns = prkey_array.zip(key_aliases).map{|k, ka_| SQL::AliasedExpression.new(k, ka_)} + c_all 110: descendant_base_case_columns = key_array.zip(key_aliases).map{|k, ka_| SQL::AliasedExpression.new(k, ka_)} + c_all 111: recursive_case_columns = prkey_array.zip(key_aliases).map{|k, ka_| SQL::QualifiedIdentifier.new(t, ka_)} + c_all 112: extract_key_alias = lambda{|m| key_aliases.map{|ka_| bd_conv[m.values.delete(ka_)]}} 113: else 114: key_present = key_conv = lambda{|m| m[key]} 115: prkey_conv = lambda{|m| m[prkey]} 116: key_aliases = [ka] 117: ka_conv = lambda{|m| m[ka]} 118: ancestor_base_case_columns = [SQL::AliasedExpression.new(prkey, ka)] + c_all 119: descendant_base_case_columns = [SQL::AliasedExpression.new(key, ka)] + c_all 120: recursive_case_columns = [SQL::QualifiedIdentifier.new(t, ka)] + c_all 121: extract_key_alias = lambda{|m| bd_conv[m.values.delete(ka)]} 122: end 123: 124: parent = opts.merge(opts.fetch(:parent, {})).fetch(:name, :parent) 125: childrena = opts.merge(opts.fetch(:children, {})).fetch(:name, :children) 126: 127: opts[:reciprocal] = nil 128: a = opts.merge(opts.fetch(:ancestors, {})) 129: ancestors = a.fetch(:name, :ancestors) 130: a[:read_only] = true unless a.has_key?(:read_only) 131: a[:eager_loader_key] = key 132: a[:dataset] ||= proc do 133: base_ds = model.where(prkey_array.zip(key_array.map{|k| get_column_value(k)})) 134: recursive_ds = model.join(t, key_array.zip(prkey_array)) 135: if c = a[:conditions] 136: (base_ds, recursive_ds) = [base_ds, recursive_ds].map do |ds| 137: (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.where(*c) : ds.where(c) 138: end 139: end 140: table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym 141: model.from(SQL::AliasedExpression.new(t, table_alias)). 142: with_recursive(t, col_aliases ? base_ds.select(*col_aliases) : base_ds.select_all, 143: recursive_ds.select(*c_all), 144: :args=>col_aliases) 145: end 146: aal = Array(a[:after_load]) 147: aal << proc do |m, ancs| 148: unless m.associations.has_key?(parent) 149: parent_map = {prkey_conv[m]=>m} 150: child_map = {} 151: child_map[key_conv[m]] = m if key_present[m] 152: m.associations[parent] = nil 153: ancs.each do |obj| 154: obj.associations[parent] = nil 155: parent_map[prkey_conv[obj]] = obj 156: if ok = key_conv[obj] 157: child_map[ok] = obj 158: end 159: end 160: parent_map.each do |parent_id, obj| 161: if child = child_map[parent_id] 162: child.associations[parent] = obj 163: end 164: end 165: end 166: end 167: a[:after_load] ||= aal 168: a[:eager_loader] ||= proc do |eo| 169: id_map = eo[:id_map] 170: parent_map = {} 171: children_map = {} 172: eo[:rows].each do |obj| 173: parent_map[prkey_conv[obj]] = obj 174: (children_map[key_conv[obj]] ||= []) << obj 175: obj.associations[ancestors] = [] 176: obj.associations[parent] = nil 177: end 178: r = model.association_reflection(ancestors) 179: base_case = model.where(prkey=>id_map.keys). 180: select(*ancestor_base_case_columns) 181: recursive_case = model.join(t, key_array.zip(prkey_array)). 182: select(*recursive_case_columns) 183: if c = r[:conditions] 184: (base_case, recursive_case) = [base_case, recursive_case].map do |ds| 185: (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.where(*c) : ds.where(c) 186: end 187: end 188: table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym 189: ds = model.from(SQL::AliasedExpression.new(t, table_alias)). 190: with_recursive(t, base_case, recursive_case, 191: :args=>((key_aliases + col_aliases) if col_aliases)) 192: ds = r.apply_eager_dataset_changes(ds) 193: ds = ds.select_append(ka) unless ds.opts[:select] == nil 194: model.eager_load_results(r, eo.merge(:loader=>false, :initalize_rows=>false, :dataset=>ds, :id_map=>nil)) do |obj| 195: opk = prkey_conv[obj] 196: if parent_map.has_key?(opk) 197: if idm_obj = parent_map[opk] 198: key_aliases.each{|ka_| idm_obj.values[ka_] = obj.values[ka_]} 199: obj = idm_obj 200: end 201: else 202: obj.associations[parent] = nil 203: parent_map[opk] = obj 204: (children_map[key_conv[obj]] ||= []) << obj 205: end 206: 207: if roots = id_map[extract_key_alias[obj]] 208: roots.each do |root| 209: root.associations[ancestors] << obj 210: end 211: end 212: end 213: parent_map.each do |parent_id, obj| 214: if children = children_map[parent_id] 215: children.each do |child| 216: child.associations[parent] = obj 217: end 218: end 219: end 220: end 221: model.one_to_many ancestors, a 222: 223: d = opts.merge(opts.fetch(:descendants, {})) 224: descendants = d.fetch(:name, :descendants) 225: d[:read_only] = true unless d.has_key?(:read_only) 226: la = d[:level_alias] ||= :x_level_x 227: d[:dataset] ||= proc do 228: base_ds = model.where(key_array.zip(prkey_array.map{|k| get_column_value(k)})) 229: recursive_ds = model.join(t, prkey_array.zip(key_array)) 230: if c = d[:conditions] 231: (base_ds, recursive_ds) = [base_ds, recursive_ds].map do |ds| 232: (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.where(*c) : ds.where(c) 233: end 234: end 235: table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym 236: model.from(SQL::AliasedExpression.new(t, table_alias)). 237: with_recursive(t, col_aliases ? base_ds.select(*col_aliases) : base_ds.select_all, 238: recursive_ds.select(*c_all), 239: :args=>col_aliases) 240: end 241: dal = Array(d[:after_load]) 242: dal << proc do |m, descs| 243: unless m.associations.has_key?(childrena) 244: parent_map = {prkey_conv[m]=>m} 245: children_map = {} 246: m.associations[childrena] = [] 247: descs.each do |obj| 248: obj.associations[childrena] = [] 249: if opk = prkey_conv[obj] 250: parent_map[opk] = obj 251: end 252: if ok = key_conv[obj] 253: (children_map[ok] ||= []) << obj 254: end 255: end 256: children_map.each do |parent_id, objs| 257: parent_obj = parent_map[parent_id] 258: parent_obj.associations[childrena] = objs 259: objs.each do |obj| 260: obj.associations[parent] = parent_obj 261: end 262: end 263: end 264: end 265: d[:after_load] = dal 266: d[:eager_loader] ||= proc do |eo| 267: id_map = eo[:id_map] 268: associations = eo[:associations] 269: parent_map = {} 270: children_map = {} 271: eo[:rows].each do |obj| 272: parent_map[prkey_conv[obj]] = obj 273: obj.associations[descendants] = [] 274: obj.associations[childrena] = [] 275: end 276: r = model.association_reflection(descendants) 277: base_case = model.where(key=>id_map.keys). 278: select(*descendant_base_case_columns) 279: recursive_case = model.join(t, prkey_array.zip(key_array)). 280: select(*recursive_case_columns) 281: if c = r[:conditions] 282: (base_case, recursive_case) = [base_case, recursive_case].map do |ds| 283: (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.where(*c) : ds.where(c) 284: end 285: end 286: if associations.is_a?(Integer) 287: level = associations 288: no_cache_level = level - 1 289: associations = {} 290: base_case = base_case.select_append(SQL::AliasedExpression.new(Sequel.cast(0, Integer), la)) 291: recursive_case = recursive_case.select_append(SQL::AliasedExpression.new(SQL::QualifiedIdentifier.new(t, la) + 1, la)).where(SQL::QualifiedIdentifier.new(t, la) < level - 1) 292: end 293: table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym 294: ds = model.from(SQL::AliasedExpression.new(t, table_alias)). 295: with_recursive(t, base_case, recursive_case, 296: :args=>((key_aliases + col_aliases + (level ? [la] : [])) if col_aliases)) 297: ds = r.apply_eager_dataset_changes(ds) 298: ds = ds.select_append(ka) unless ds.opts[:select] == nil 299: model.eager_load_results(r, eo.merge(:loader=>false, :initalize_rows=>false, :dataset=>ds, :id_map=>nil, :associations=>{})) do |obj| 300: if level 301: no_cache = no_cache_level == obj.values.delete(la) 302: end 303: 304: opk = prkey_conv[obj] 305: if parent_map.has_key?(opk) 306: if idm_obj = parent_map[opk] 307: key_aliases.each{|ka_| idm_obj.values[ka_] = obj.values[ka_]} 308: obj = idm_obj 309: end 310: else 311: obj.associations[childrena] = [] unless no_cache 312: parent_map[opk] = obj 313: end 314: 315: if root = id_map[extract_key_alias[obj]].first 316: root.associations[descendants] << obj 317: end 318: 319: (children_map[key_conv[obj]] ||= []) << obj 320: end 321: children_map.each do |parent_id, objs| 322: objs = objs.uniq 323: parent_obj = parent_map[parent_id] 324: parent_obj.associations[childrena] = objs 325: objs.each do |obj| 326: obj.associations[parent] = parent_obj 327: end 328: end 329: end 330: model.one_to_many descendants, d 331: end