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 is unlike most plugins in that it doesn‘t add any class, instance, or dataset modules. It only has a single apply method, which 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:
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:
Create the appropriate parent, children, ancestors, and descendants associations for the model.
# File lib/sequel/plugins/rcte_tree.rb, line 97 97: def self.apply(model, opts={}) 98: model.plugin :tree, opts 99: 100: opts = opts.dup 101: opts[:class] = model 102: opts[:methods_module] = Module.new 103: model.send(:include, opts[:methods_module]) 104: 105: key = opts[:key] ||= :parent_id 106: prkey = opts[:primary_key] ||= model.primary_key 107: 108: parent = opts.merge(opts.fetch(:parent, {})).fetch(:name, :parent) 109: childrena = opts.merge(opts.fetch(:children, {})).fetch(:name, :children) 110: 111: ka = opts[:key_alias] ||= :x_root_x 112: t = opts[:cte_name] ||= :t 113: opts[:reciprocal] = nil 114: c_all = SQL::ColumnAll.new(model.table_name) 115: 116: a = opts.merge(opts.fetch(:ancestors, {})) 117: ancestors = a.fetch(:name, :ancestors) 118: a[:read_only] = true unless a.has_key?(:read_only) 119: a[:eager_loader_key] = key 120: a[:dataset] ||= proc do 121: model.from(t). 122: with_recursive(t, model.filter(prkey=>send(key)), 123: model.join(t, key=>prkey). 124: select(c_all)) 125: end 126: aal = Array(a[:after_load]) 127: aal << proc do |m, ancs| 128: unless m.associations.has_key?(parent) 129: parent_map = {m[prkey]=>m} 130: child_map = {} 131: child_map[m[key]] = m if m[key] 132: m.associations[parent] = nil 133: ancs.each do |obj| 134: obj.associations[parent] = nil 135: parent_map[obj[prkey]] = obj 136: if ok = obj[key] 137: child_map[ok] = obj 138: end 139: end 140: parent_map.each do |parent_id, obj| 141: if child = child_map[parent_id] 142: child.associations[parent] = obj 143: end 144: end 145: end 146: end 147: a[:after_load] ||= aal 148: a[:eager_loader] ||= proc do |eo| 149: id_map = eo[:key_hash][key] 150: parent_map = {} 151: children_map = {} 152: eo[:rows].each do |obj| 153: parent_map[obj[prkey]] = obj 154: (children_map[obj[key]] ||= []) << obj 155: obj.associations[ancestors] = [] 156: obj.associations[parent] = nil 157: end 158: r = model.association_reflection(ancestors) 159: model.eager_loading_dataset(r, 160: model.from(t). 161: with_recursive(t, model.filter(prkey=>id_map.keys). 162: select(SQL::AliasedExpression.new(prkey, ka), c_all), 163: model.join(t, key=>prkey). 164: select(SQL::QualifiedIdentifier.new(t, ka), c_all)), 165: r.select, 166: eo[:associations], eo).all do |obj| 167: opk = obj[prkey] 168: if in_pm = parent_map.has_key?(opk) 169: if idm_obj = parent_map[opk] 170: idm_obj.values[ka] = obj.values[ka] 171: obj = idm_obj 172: end 173: else 174: obj.associations[parent] = nil 175: parent_map[opk] = obj 176: (children_map[obj[key]] ||= []) << obj 177: end 178: 179: if roots = id_map[obj.values.delete(ka)] 180: roots.each do |root| 181: root.associations[ancestors] << obj 182: end 183: end 184: end 185: parent_map.each do |parent_id, obj| 186: if children = children_map[parent_id] 187: children.each do |child| 188: child.associations[parent] = obj 189: end 190: end 191: end 192: end 193: model.one_to_many ancestors, a 194: 195: d = opts.merge(opts.fetch(:descendants, {})) 196: descendants = d.fetch(:name, :descendants) 197: d[:read_only] = true unless d.has_key?(:read_only) 198: la = d[:level_alias] ||= :x_level_x 199: d[:dataset] ||= proc do 200: model.from(t). 201: with_recursive(t, model.filter(key=>send(prkey)), 202: model.join(t, prkey=>key). 203: select(SQL::ColumnAll.new(model.table_name))) 204: end 205: dal = Array(d[:after_load]) 206: dal << proc do |m, descs| 207: unless m.associations.has_key?(childrena) 208: parent_map = {m[prkey]=>m} 209: children_map = {} 210: m.associations[childrena] = [] 211: descs.each do |obj| 212: obj.associations[childrena] = [] 213: if opk = obj[prkey] 214: parent_map[opk] = obj 215: end 216: if ok = obj[key] 217: (children_map[ok] ||= []) << obj 218: end 219: end 220: children_map.each do |parent_id, objs| 221: parent_map[parent_id].associations[childrena] = objs 222: end 223: end 224: end 225: d[:after_load] = dal 226: d[:eager_loader] ||= proc do |eo| 227: id_map = eo[:key_hash][prkey] 228: associations = eo[:associations] 229: parent_map = {} 230: children_map = {} 231: eo[:rows].each do |obj| 232: parent_map[obj[prkey]] = obj 233: obj.associations[descendants] = [] 234: obj.associations[childrena] = [] 235: end 236: r = model.association_reflection(descendants) 237: base_case = model.filter(key=>id_map.keys). 238: select(SQL::AliasedExpression.new(key, ka), c_all) 239: recursive_case = model.join(t, prkey=>key). 240: select(SQL::QualifiedIdentifier.new(t, ka), c_all) 241: if associations.is_a?(Integer) 242: level = associations 243: no_cache_level = level - 1 244: associations = {} 245: base_case = base_case.select_more(SQL::AliasedExpression.new(0, la)) 246: recursive_case = recursive_case.select_more(SQL::AliasedExpression.new(SQL::QualifiedIdentifier.new(t, la) + 1, la)).filter(SQL::QualifiedIdentifier.new(t, la) < level - 1) 247: end 248: model.eager_loading_dataset(r, 249: model.from(t).with_recursive(t, base_case, recursive_case), 250: r.select, 251: associations, eo).all do |obj| 252: if level 253: no_cache = no_cache_level == obj.values.delete(la) 254: end 255: 256: opk = obj[prkey] 257: if in_pm = parent_map.has_key?(opk) 258: if idm_obj = parent_map[opk] 259: idm_obj.values[ka] = obj.values[ka] 260: obj = idm_obj 261: end 262: else 263: obj.associations[childrena] = [] unless no_cache 264: parent_map[opk] = obj 265: end 266: 267: if root = id_map[obj.values.delete(ka)].first 268: root.associations[descendants] << obj 269: end 270: 271: (children_map[obj[key]] ||= []) << obj 272: end 273: children_map.each do |parent_id, objs| 274: parent_map[parent_id].associations[childrena] = objs.uniq 275: end 276: end 277: model.one_to_many descendants, d 278: end