Use Arel for a nested set & join query and convert to ActiveRecord::Relation

Question

I have a model for organisations (nested set). I have a model for people. A person can have another person as deputy. An organisation is owned by a person. An organisation is only visible to the owning person or their deputy.

I would like to retrieve all organisations visible by a given person, ie. all organisations owned by that person or owned by people, for which the given person is a deputy:

o = Arel::Table.new(:organisations)
p = Arel::Table.new(:people)
pd = p.where(p[:id].eq(3).or(p[:deputy_id].eq(3))).project(:id)
op = o.join(p).where(o[:person_id].in(pd)).project("distinct organisations.*)

There is probably a better way to formulate the last join, but I would like to split the query for people and their deputies from the query of organisations visible to people and their deputies.

The last join returns a Arel::SelectManager (for which there seems no useful documentation anywhere).

Is there a way to convert the SelectManager back into an ActiveRecord::Relation to benefit from the whole concept of "closure under composition"?

How do I self join the above query on organisations again to obtain all descendants of the organisations visible to a person or their deputy? I know the SQL but always fail with the SelectManager to do the self join on organisations.

Answer 1

Seems there are no takers for any answer and I've found a solution approach myself:

1. Convert the last join to an ActiveRecord::Relation

Organisation.where(o[:id].in(op))

The only issue with this is that this calls Arel::SelectManager.to_a which comes with a deprecation warning (and is also an expensive operation). I haven't found an alternative though (suspect there is none and this deprecation warning is just one of the inconsistencies observable in Arel and it's adoption in ActiveRecord).

2. Self-join on nested set to get all descendants

o = Organisation.scoped.table
op = Organisation.where(o[:person_id].in(Person.self_and_deputies(person_id).project(:id))).arel
o1 = Arel::Table.new(:organisations, :as => "o1")
o2 = Arel::Table.new(:organisations, :as => "o2")
o3 = o1.join(o2).on(
     o1[:lft].gteq(o2[:lft]).and(
     o1[:rgt].lteq(o2[:rgt]))).where(
     o2[:id].in(op)).project("distinct o1.id")
Organisation.where(o[:id].in(o3))

Answer 2

You should be able to call join_sources on an instance of Arel::SelectManager, which can be passed to ActiveRecord::Relation#joins. Your query would look like this (untested):

o = Organisation.scoped.table
op = Organisation.where(o[:person_id].in(Person.self_and_deputies(person_id).project(:id))).arel
o1 = Arel::Table.new(:organisations, :as => "o1")
o2 = Arel::Table.new(:organisations, :as => "o2")
o3 = Organization.joins(
  o1.join(o2).
    on(o1[:lft].gteq(o2[:lft]).and(o1[:rgt].lteq(o2[:rgt]))).join_sources).
  where(o2[:id].in(op)).
  project("distinct o1.id")

Answer 3

You could also do:

Organisation.joins(op.join_sql).where(op.where_sql)

I got this after searching a while too. This will allow you to stack any other scope over it.