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.

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.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

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 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

[Validate]