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.
There are two types of common models for storing tree structured data in an SQL database, the adjacency list model and the nested set model. Before recursive common table expressions (or similar capabilities such as CONNECT BY for Oracle), the nested set model was the only easy way to retrieve all ancestors and descendants in a single query. However, it has significant performance corner cases.
On PostgreSQL 8.4, with a significant number of rows, the nested set model is almost 500 times slower than using a recursive common table expression with the adjacency list model to get all descendants, and almost 24,000 times slower to get all descendants to a given level.
Considering that the nested set model requires more difficult management than the adjacency list model, it‘s almost always better to use the adjacency list model if your database supports common table expressions. See explainextended.com/2009/09/24/adjacency-list-vs-nested-sets-postgresql/ for detailed analysis.
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.filter(:id=>[1, 2]).eager(:ancestors, :descendants).all # Eager loading children and grand children Model.filter(:id=>[1, 2]).eager(:descendants=>2).all # Eager loading children, grand children, and great grand children Model.filter(: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 95 95: def self.apply(model, opts=OPTS) 96: model.plugin :tree, opts 97: 98: opts = opts.dup 99: opts[:class] = model 100: opts[:methods_module] = Module.new 101: model.send(:include, opts[:methods_module]) 102: 103: key = opts[:key] ||= :parent_id 104: prkey = opts[:primary_key] ||= model.primary_key 105: ka = opts[:key_alias] ||= :x_root_x 106: t = opts[:cte_name] ||= :t 107: c_all = if model.dataset.recursive_cte_requires_column_aliases? 108: # Work around Oracle/ruby-oci8 bug that returns integers as BigDecimals in recursive queries. 109: conv_bd = model.db.database_type == :oracle 110: col_aliases = model.dataset.columns 111: model_table = model.table_name 112: col_aliases.map{|c| SQL::QualifiedIdentifier.new(model_table, c)} 113: else 114: [SQL::ColumnAll.new(model.table_name)] 115: end 116: 117: bd_conv = lambda{|v| conv_bd && v.is_a?(BigDecimal) ? v.to_i : v} 118: 119: key_array = Array(key) 120: prkey_array = Array(prkey) 121: if key.is_a?(Array) 122: key_conv = lambda{|m| key_array.map{|k| m[k]}} 123: key_present = lambda{|m| key_conv[m].all?} 124: prkey_conv = lambda{|m| prkey_array.map{|k| m[k]}} 125: key_aliases = (0...key_array.length).map{|i| "#{ka}_#{i}""#{ka}_#{i}"} 126: ka_conv = lambda{|m| key_aliases.map{|k| m[k]}} 127: ancestor_base_case_columns = prkey_array.zip(key_aliases).map{|k, ka_| SQL::AliasedExpression.new(k, ka_)} + c_all 128: descendant_base_case_columns = key_array.zip(key_aliases).map{|k, ka_| SQL::AliasedExpression.new(k, ka_)} + c_all 129: recursive_case_columns = prkey_array.zip(key_aliases).map{|k, ka_| SQL::QualifiedIdentifier.new(t, ka_)} + c_all 130: extract_key_alias = lambda{|m| key_aliases.map{|ka_| bd_conv[m.values.delete(ka_)]}} 131: else 132: key_present = key_conv = lambda{|m| m[key]} 133: prkey_conv = lambda{|m| m[prkey]} 134: key_aliases = [ka] 135: ka_conv = lambda{|m| m[ka]} 136: ancestor_base_case_columns = [SQL::AliasedExpression.new(prkey, ka)] + c_all 137: descendant_base_case_columns = [SQL::AliasedExpression.new(key, ka)] + c_all 138: recursive_case_columns = [SQL::QualifiedIdentifier.new(t, ka)] + c_all 139: extract_key_alias = lambda{|m| bd_conv[m.values.delete(ka)]} 140: end 141: 142: parent = opts.merge(opts.fetch(:parent, {})).fetch(:name, :parent) 143: childrena = opts.merge(opts.fetch(:children, {})).fetch(:name, :children) 144: 145: opts[:reciprocal] = nil 146: a = opts.merge(opts.fetch(:ancestors, {})) 147: ancestors = a.fetch(:name, :ancestors) 148: a[:read_only] = true unless a.has_key?(:read_only) 149: a[:eager_loader_key] = key 150: a[:dataset] ||= proc do 151: base_ds = model.filter(prkey_array.zip(key_array.map{|k| get_column_value(k)})) 152: recursive_ds = model.join(t, key_array.zip(prkey_array)) 153: if c = a[:conditions] 154: (base_ds, recursive_ds) = [base_ds, recursive_ds].collect do |ds| 155: (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.filter(*c) : ds.filter(c) 156: end 157: end 158: table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym 159: model.from(SQL::AliasedExpression.new(t, table_alias)). 160: with_recursive(t, col_aliases ? base_ds.select(*col_aliases) : base_ds.select_all, 161: recursive_ds.select(*c_all), 162: :args=>col_aliases) 163: end 164: aal = Array(a[:after_load]) 165: aal << proc do |m, ancs| 166: unless m.associations.has_key?(parent) 167: parent_map = {prkey_conv[m]=>m} 168: child_map = {} 169: child_map[key_conv[m]] = m if key_present[m] 170: m.associations[parent] = nil 171: ancs.each do |obj| 172: obj.associations[parent] = nil 173: parent_map[prkey_conv[obj]] = obj 174: if ok = key_conv[obj] 175: child_map[ok] = obj 176: end 177: end 178: parent_map.each do |parent_id, obj| 179: if child = child_map[parent_id] 180: child.associations[parent] = obj 181: end 182: end 183: end 184: end 185: a[:after_load] ||= aal 186: a[:eager_loader] ||= proc do |eo| 187: id_map = eo[:id_map] 188: parent_map = {} 189: children_map = {} 190: eo[:rows].each do |obj| 191: parent_map[prkey_conv[obj]] = obj 192: (children_map[key_conv[obj]] ||= []) << obj 193: obj.associations[ancestors] = [] 194: obj.associations[parent] = nil 195: end 196: r = model.association_reflection(ancestors) 197: base_case = model.filter(prkey=>id_map.keys). 198: select(*ancestor_base_case_columns) 199: recursive_case = model.join(t, key_array.zip(prkey_array)). 200: select(*recursive_case_columns) 201: if c = r[:conditions] 202: (base_case, recursive_case) = [base_case, recursive_case].collect do |ds| 203: (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.filter(*c) : ds.filter(c) 204: end 205: end 206: table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym 207: ds = model.from(SQL::AliasedExpression.new(t, table_alias)). 208: with_recursive(t, base_case, recursive_case, 209: :args=>((key_aliases + col_aliases) if col_aliases)) 210: ds = r.apply_eager_dataset_changes(ds) 211: ds = ds.select_append(ka) unless ds.opts[:select] == nil 212: model.eager_load_results(r, eo.merge(:loader=>false, :initalize_rows=>false, :dataset=>ds, :id_map=>nil)) do |obj| 213: opk = prkey_conv[obj] 214: if parent_map.has_key?(opk) 215: if idm_obj = parent_map[opk] 216: key_aliases.each{|ka_| idm_obj.values[ka_] = obj.values[ka_]} 217: obj = idm_obj 218: end 219: else 220: obj.associations[parent] = nil 221: parent_map[opk] = obj 222: (children_map[key_conv[obj]] ||= []) << obj 223: end 224: 225: if roots = id_map[extract_key_alias[obj]] 226: roots.each do |root| 227: root.associations[ancestors] << obj 228: end 229: end 230: end 231: parent_map.each do |parent_id, obj| 232: if children = children_map[parent_id] 233: children.each do |child| 234: child.associations[parent] = obj 235: end 236: end 237: end 238: end 239: model.one_to_many ancestors, a 240: 241: d = opts.merge(opts.fetch(:descendants, {})) 242: descendants = d.fetch(:name, :descendants) 243: d[:read_only] = true unless d.has_key?(:read_only) 244: la = d[:level_alias] ||= :x_level_x 245: d[:dataset] ||= proc do 246: base_ds = model.filter(key_array.zip(prkey_array.map{|k| get_column_value(k)})) 247: recursive_ds = model.join(t, prkey_array.zip(key_array)) 248: if c = d[:conditions] 249: (base_ds, recursive_ds) = [base_ds, recursive_ds].collect do |ds| 250: (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.filter(*c) : ds.filter(c) 251: end 252: end 253: table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym 254: model.from(SQL::AliasedExpression.new(t, table_alias)). 255: with_recursive(t, col_aliases ? base_ds.select(*col_aliases) : base_ds.select_all, 256: recursive_ds.select(*c_all), 257: :args=>col_aliases) 258: end 259: dal = Array(d[:after_load]) 260: dal << proc do |m, descs| 261: unless m.associations.has_key?(childrena) 262: parent_map = {prkey_conv[m]=>m} 263: children_map = {} 264: m.associations[childrena] = [] 265: descs.each do |obj| 266: obj.associations[childrena] = [] 267: if opk = prkey_conv[obj] 268: parent_map[opk] = obj 269: end 270: if ok = key_conv[obj] 271: (children_map[ok] ||= []) << obj 272: end 273: end 274: children_map.each do |parent_id, objs| 275: parent_obj = parent_map[parent_id] 276: parent_obj.associations[childrena] = objs 277: objs.each do |obj| 278: obj.associations[parent] = parent_obj 279: end 280: end 281: end 282: end 283: d[:after_load] = dal 284: d[:eager_loader] ||= proc do |eo| 285: id_map = eo[:id_map] 286: associations = eo[:associations] 287: parent_map = {} 288: children_map = {} 289: eo[:rows].each do |obj| 290: parent_map[prkey_conv[obj]] = obj 291: obj.associations[descendants] = [] 292: obj.associations[childrena] = [] 293: end 294: r = model.association_reflection(descendants) 295: base_case = model.filter(key=>id_map.keys). 296: select(*descendant_base_case_columns) 297: recursive_case = model.join(t, prkey_array.zip(key_array)). 298: select(*recursive_case_columns) 299: if c = r[:conditions] 300: (base_case, recursive_case) = [base_case, recursive_case].collect do |ds| 301: (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.filter(*c) : ds.filter(c) 302: end 303: end 304: if associations.is_a?(Integer) 305: level = associations 306: no_cache_level = level - 1 307: associations = {} 308: base_case = base_case.select_more(SQL::AliasedExpression.new(Sequel.cast(0, Integer), la)) 309: recursive_case = recursive_case.select_more(SQL::AliasedExpression.new(SQL::QualifiedIdentifier.new(t, la) + 1, la)).filter(SQL::QualifiedIdentifier.new(t, la) < level - 1) 310: end 311: table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym 312: ds = model.from(SQL::AliasedExpression.new(t, table_alias)). 313: with_recursive(t, base_case, recursive_case, 314: :args=>((key_aliases + col_aliases + (level ? [la] : [])) if col_aliases)) 315: ds = r.apply_eager_dataset_changes(ds) 316: ds = ds.select_append(ka) unless ds.opts[:select] == nil 317: model.eager_load_results(r, eo.merge(:loader=>false, :initalize_rows=>false, :dataset=>ds, :id_map=>nil, :associations=>{})) do |obj| 318: if level 319: no_cache = no_cache_level == obj.values.delete(la) 320: end 321: 322: opk = prkey_conv[obj] 323: if parent_map.has_key?(opk) 324: if idm_obj = parent_map[opk] 325: key_aliases.each{|ka_| idm_obj.values[ka_] = obj.values[ka_]} 326: obj = idm_obj 327: end 328: else 329: obj.associations[childrena] = [] unless no_cache 330: parent_map[opk] = obj 331: end 332: 333: if root = id_map[extract_key_alias[obj]].first 334: root.associations[descendants] << obj 335: end 336: 337: (children_map[key_conv[obj]] ||= []) << obj 338: end 339: children_map.each do |parent_id, objs| 340: objs = objs.uniq 341: parent_obj = parent_map[parent_id] 342: parent_obj.associations[childrena] = objs 343: objs.each do |obj| 344: obj.associations[parent] = parent_obj 345: end 346: end 347: end 348: model.one_to_many descendants, d 349: end