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

# 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

apply(model, opts={})

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={})
     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

[Validate]