Get properties from all children in a recursive SQL structure

Question

In our product, Company is a model that can have many Companies belonging to it; so some Companies are parents, some are children, and many are both.

We have a helper method Company.root that has as its children all Companies that do not actually have a parent; in this way, accessing Company.root and recursively iterating over the children will touch every Company in the database.

For context, 'branches' are just Companies with a special flag set (namely that they cannot have children of their own).

We have a method Company#get_deep_props that makes one SQL call per Company below the queried Company. This has abysmal performance on some pages, and for instance when we need to do Company validation we need to check other companies in the chain for a few different values to ensure pseudo-uniqueness (can not make the column unique).

How can we improve this performance of this method? Can we cache the companies on the first call and use the ActiveRecord::Selection in subsequent calls? Not all properties of Companies are in the database (some are computed) so I don't think we can replace the code with a (sufficiently complex) SQL query in ALL cases, but we might be able to do it for some.

has_many :subdealers, class_name: "Company", foreign_key: "dealer_id"

def children
  root ? Company.where("dealer_id IS NULL") : subdealers
end

def get_deep_props(prop, options = {})
  prop            = prop.to_sym
  default_options = { :with_branches => true }
  options         = default_options.merge(options)
  child_props     = []

  children.each do |child|
    next if (child.branch? && !options[:with_branches])
    child_props.concat(child.get_deep_props(prop, options))
  end

  case prop
  when :dba_tags
    child_props.concat(read_attribute(prop).split)
  when :branches
    child_props.concat(branches)
  when :terminals
    child_props.concat(terminals)
  when :employees
    child_props.concat(employees)
  when :self
    child_props.push(self)
  else
    child_props.push(read_attribute(prop))
  end

  child_props
end

In the database, Company's dealer_id is the id of the parent Company, or null if the company has no parent.

Answer 1

There is more than one way to represent a tree and they have different pro's and con's.

In your case you may want to look at modified preorder tree traveral. You can read more about the algorithm here for example.

By giving all nodes a proper left and right value pair you can load an entire subtree in one query for example, which would improve your situation a lot.