Module Sequel::Plugins::RcteTree
In: lib/sequel/plugins/rcte_tree.rb

Overview

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.

Background

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.

Usage

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

Options

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)

Methods

apply  

Public Class methods

Create the appropriate parent, children, ancestors, and descendants associations for the model.

[Source]

     # File lib/sequel/plugins/rcte_tree.rb, line 97
 97:       def self.apply(model, opts=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:         ka = opts[:key_alias] ||= :x_root_x
108:         t = opts[:cte_name] ||= :t
109:         c_all = if model.dataset.recursive_cte_requires_column_aliases?
110:           # Work around Oracle/ruby-oci8 bug that returns integers as BigDecimals in recursive queries.
111:           conv_bd = model.db.database_type == :oracle
112:           col_aliases = model.dataset.columns
113:           model_table = model.table_name
114:           col_aliases.map{|c| SQL::QualifiedIdentifier.new(model_table, c)}
115:         else
116:           [SQL::ColumnAll.new(model.table_name)]
117:         end
118:         
119:         bd_conv = lambda{|v| conv_bd && v.is_a?(BigDecimal) ? v.to_i : v}
120: 
121:         key_array = Array(key)
122:         prkey_array = Array(prkey)
123:         if key.is_a?(Array)
124:           key_conv = lambda{|m| key_array.map{|k| m[k]}}
125:           key_present = lambda{|m| key_conv[m].all?}
126:           prkey_conv = lambda{|m| prkey_array.map{|k| m[k]}}
127:           key_aliases = (0...key_array.length).map{|i| "#{ka}_#{i}""#{ka}_#{i}"}
128:           ka_conv = lambda{|m| key_aliases.map{|k| m[k]}}
129:           ancestor_base_case_columns = prkey_array.zip(key_aliases).map{|k, ka_| SQL::AliasedExpression.new(k, ka_)} + c_all
130:           descendant_base_case_columns = key_array.zip(key_aliases).map{|k, ka_| SQL::AliasedExpression.new(k, ka_)} + c_all
131:           recursive_case_columns = prkey_array.zip(key_aliases).map{|k, ka_| SQL::QualifiedIdentifier.new(t, ka_)} + c_all
132:           extract_key_alias = lambda{|m| key_aliases.map{|ka_| bd_conv[m.values.delete(ka_)]}}
133:         else
134:           key_present = key_conv = lambda{|m| m[key]}
135:           prkey_conv = lambda{|m| m[prkey]}
136:           key_aliases = [ka]
137:           ka_conv = lambda{|m| m[ka]}
138:           ancestor_base_case_columns = [SQL::AliasedExpression.new(prkey, ka)] + c_all
139:           descendant_base_case_columns = [SQL::AliasedExpression.new(key, ka)] + c_all
140:           recursive_case_columns = [SQL::QualifiedIdentifier.new(t, ka)] + c_all
141:           extract_key_alias = lambda{|m| bd_conv[m.values.delete(ka)]}
142:         end
143:         
144:         parent = opts.merge(opts.fetch(:parent, {})).fetch(:name, :parent)
145:         childrena = opts.merge(opts.fetch(:children, {})).fetch(:name, :children)
146:         
147:         opts[:reciprocal] = nil
148:         a = opts.merge(opts.fetch(:ancestors, {}))
149:         ancestors = a.fetch(:name, :ancestors)
150:         a[:read_only] = true unless a.has_key?(:read_only)
151:         a[:eager_loader_key] = key
152:         a[:dataset] ||= proc do
153:           base_ds = model.filter(prkey_array.zip(key_array.map{|k| get_column_value(k)}))
154:           recursive_ds = model.join(t, key_array.zip(prkey_array))
155:           if c = a[:conditions]
156:             (base_ds, recursive_ds) = [base_ds, recursive_ds].collect do |ds|
157:               (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.filter(*c) : ds.filter(c)
158:             end
159:           end
160:           table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym
161:           model.from(SQL::AliasedExpression.new(t, table_alias)).
162:            with_recursive(t, col_aliases ? base_ds.select(*col_aliases) : base_ds.select_all,
163:             recursive_ds.select(*c_all),
164:             :args=>col_aliases)
165:         end
166:         aal = Array(a[:after_load])
167:         aal << proc do |m, ancs|
168:           unless m.associations.has_key?(parent)
169:             parent_map = {prkey_conv[m]=>m}
170:             child_map = {}
171:             child_map[key_conv[m]] = m if key_present[m]
172:             m.associations[parent] = nil
173:             ancs.each do |obj|
174:               obj.associations[parent] = nil
175:               parent_map[prkey_conv[obj]] = obj
176:               if ok = key_conv[obj]
177:                 child_map[ok] = obj
178:               end
179:             end
180:             parent_map.each do |parent_id, obj|
181:               if child = child_map[parent_id]
182:                 child.associations[parent] = obj
183:               end
184:             end
185:           end
186:         end
187:         a[:after_load] ||= aal
188:         a[:eager_loader] ||= proc do |eo|
189:           id_map = eo[:id_map]
190:           parent_map = {}
191:           children_map = {}
192:           eo[:rows].each do |obj|
193:             parent_map[prkey_conv[obj]] = obj
194:             (children_map[key_conv[obj]] ||= []) << obj
195:             obj.associations[ancestors] = []
196:             obj.associations[parent] = nil
197:           end
198:           r = model.association_reflection(ancestors)
199:           base_case = model.filter(prkey=>id_map.keys).
200:            select(*ancestor_base_case_columns)
201:           recursive_case = model.join(t, key_array.zip(prkey_array)).
202:            select(*recursive_case_columns)
203:           if c = r[:conditions]
204:             (base_case, recursive_case) = [base_case, recursive_case].collect do |ds|
205:               (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.filter(*c) : ds.filter(c)
206:             end
207:           end
208:           table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym
209:           ds = model.from(SQL::AliasedExpression.new(t, table_alias)).
210:             with_recursive(t, base_case, recursive_case,
211:              :args=>((key_aliases + col_aliases) if col_aliases))
212:           ds = r.apply_eager_dataset_changes(ds)
213:           ds = ds.select_append(ka) unless ds.opts[:select] == nil
214:           model.eager_load_results(r, eo.merge(:loader=>false, :initalize_rows=>false, :dataset=>ds, :id_map=>nil)) do |obj|
215:             opk = prkey_conv[obj]
216:             if parent_map.has_key?(opk)
217:               if idm_obj = parent_map[opk]
218:                 key_aliases.each{|ka_| idm_obj.values[ka_] = obj.values[ka_]}
219:                 obj = idm_obj
220:               end
221:             else
222:               obj.associations[parent] = nil
223:               parent_map[opk] = obj
224:               (children_map[key_conv[obj]] ||= []) << obj
225:             end
226:             
227:             if roots = id_map[extract_key_alias[obj]]
228:               roots.each do |root|
229:                 root.associations[ancestors] << obj
230:               end
231:             end
232:           end
233:           parent_map.each do |parent_id, obj|
234:             if children = children_map[parent_id]
235:               children.each do |child|
236:                 child.associations[parent] = obj
237:               end
238:             end
239:           end
240:         end
241:         model.one_to_many ancestors, a
242:         
243:         d = opts.merge(opts.fetch(:descendants, {}))
244:         descendants = d.fetch(:name, :descendants)
245:         d[:read_only] = true unless d.has_key?(:read_only)
246:         la = d[:level_alias] ||= :x_level_x
247:         d[:dataset] ||= proc do
248:           base_ds = model.filter(key_array.zip(prkey_array.map{|k| get_column_value(k)}))
249:           recursive_ds = model.join(t, prkey_array.zip(key_array))
250:           if c = d[:conditions]
251:             (base_ds, recursive_ds) = [base_ds, recursive_ds].collect do |ds|
252:               (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.filter(*c) : ds.filter(c)
253:             end
254:           end
255:           table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym
256:           model.from(SQL::AliasedExpression.new(t, table_alias)).
257:            with_recursive(t, col_aliases ? base_ds.select(*col_aliases) : base_ds.select_all,
258:             recursive_ds.select(*c_all),
259:             :args=>col_aliases)
260:           end
261:         dal = Array(d[:after_load])
262:         dal << proc do |m, descs|
263:           unless m.associations.has_key?(childrena)
264:             parent_map = {prkey_conv[m]=>m}
265:             children_map = {}
266:             m.associations[childrena] = []
267:             descs.each do |obj|
268:               obj.associations[childrena] = []
269:               if opk = prkey_conv[obj]
270:                 parent_map[opk] = obj
271:               end
272:               if ok = key_conv[obj]
273:                 (children_map[ok] ||= []) << obj
274:               end
275:             end
276:             children_map.each do |parent_id, objs|
277:               parent_obj = parent_map[parent_id]
278:               parent_obj.associations[childrena] = objs
279:               objs.each do |obj|
280:                 obj.associations[parent] = parent_obj
281:               end
282:             end
283:           end
284:         end
285:         d[:after_load] = dal
286:         d[:eager_loader] ||= proc do |eo|
287:           id_map = eo[:id_map]
288:           associations = eo[:associations]
289:           parent_map = {}
290:           children_map = {}
291:           eo[:rows].each do |obj|
292:             parent_map[prkey_conv[obj]] = obj
293:             obj.associations[descendants] = []
294:             obj.associations[childrena] = []
295:           end
296:           r = model.association_reflection(descendants)
297:           base_case = model.filter(key=>id_map.keys).
298:            select(*descendant_base_case_columns)
299:           recursive_case = model.join(t, prkey_array.zip(key_array)).
300:            select(*recursive_case_columns)
301:           if c = r[:conditions]
302:             (base_case, recursive_case) = [base_case, recursive_case].collect do |ds|
303:               (c.is_a?(Array) && !Sequel.condition_specifier?(c)) ? ds.filter(*c) : ds.filter(c)
304:             end
305:           end
306:           if associations.is_a?(Integer)
307:             level = associations
308:             no_cache_level = level - 1
309:             associations = {}
310:             base_case = base_case.select_more(SQL::AliasedExpression.new(Sequel.cast(0, Integer), la))
311:             recursive_case = recursive_case.select_more(SQL::AliasedExpression.new(SQL::QualifiedIdentifier.new(t, la) + 1, la)).filter(SQL::QualifiedIdentifier.new(t, la) < level - 1)
312:           end
313:           table_alias = model.dataset.schema_and_table(model.table_name)[1].to_sym
314:           ds = model.from(SQL::AliasedExpression.new(t, table_alias)).
315:             with_recursive(t, base_case, recursive_case,
316:               :args=>((key_aliases + col_aliases + (level ? [la] : [])) if col_aliases))
317:           ds = r.apply_eager_dataset_changes(ds)
318:           ds = ds.select_append(ka) unless ds.opts[:select] == nil
319:           model.eager_load_results(r, eo.merge(:loader=>false, :initalize_rows=>false, :dataset=>ds, :id_map=>nil, :associations=>{})) do |obj|
320:             if level
321:               no_cache = no_cache_level == obj.values.delete(la)
322:             end
323:             
324:             opk = prkey_conv[obj]
325:             if parent_map.has_key?(opk)
326:               if idm_obj = parent_map[opk]
327:                 key_aliases.each{|ka_| idm_obj.values[ka_] = obj.values[ka_]}
328:                 obj = idm_obj
329:               end
330:             else
331:               obj.associations[childrena] = [] unless no_cache
332:               parent_map[opk] = obj
333:             end
334:             
335:             if root = id_map[extract_key_alias[obj]].first
336:               root.associations[descendants] << obj
337:             end
338:             
339:             (children_map[key_conv[obj]] ||= []) << obj
340:           end
341:           children_map.each do |parent_id, objs|
342:             objs = objs.uniq
343:             parent_obj = parent_map[parent_id]
344:             parent_obj.associations[childrena] = objs
345:             objs.each do |obj|
346:               obj.associations[parent] = parent_obj
347:             end
348:           end
349:         end
350:         model.one_to_many descendants, d
351:       end

[Validate]