How to do joins on subqueries in AREL within Rails

Question

I have a simple model

class User
    has_many :logs


class Logs

related in the usual way through the foreign key logs.user_id. I'm trying to do the following using Arel and according to the Arel doc it should work.

u_t = Arel::Table::new :users
l_t = Arel::Table::new :logs

counts = l_t.
    group(l_t[:user_id]).
    project(
        l_t[:user_id].as("user_id"),
        l_t[:user_id].count.as("count_all")
    )

l_t.joins(counts).on(l_t[:id].eq(counts[:user_id]))

When I do that I get the error

TypeError: Cannot visit Arel::SelectManager

However the author of Arel explicitly suggests that Arel can do this kind of thing.

Please do not write responses on how I can achieve the same query with raw sql, another type of Arel query etc. It is the pattern I am interested in not the specific results of this query.

Sackett

Answer 1

You can use join_sources to retrieve the Arel::Nodes::Join from the instance of Arel::SelectManager, and pass that to joins

Using your example:

l_t.joins(counts.join_sources).on(l_t[:id].eq(counts[:user_id]))

Petry

l_t[:id].eq(counts[:user_id]) does this work? I mean, why should the id of a log be equal to the id of the user? – Autocracy

Answer 2

This achieves a join of nested select subquery with Arel:

You can add the nested inner_query and an outer_query scope in your Model file and use ...

  inner_query = Model.inner_query(params)
  result = Model.outer_query(params).joins(Arel.sql("(#{inner_query.to_sql})"))
   .group("...")
   .order("...")

For variations on this, for example to use INNER JOIN on the subquery, do the following:

  inner_query = Model.inner_query(params)
  result = Model.outer_query(params).joins(Arel.sql("INNER JOIN (#{inner_query.to_sql}) tablealias ON a.id = b.id"))
   .group("...")
   .order("...")

Add in the specific joins, constraints and groupings to each of the queries' scopes to modify the sql statement further ie:

scope :inner_query , -> (company_id, name) {
    select("...")
    .joins("left join table1 on table1.id = table2.id")
    .where("table1.company_id = ? and table1.name in (?)", company_id, name)
    .group("...")
}

This allows you to put WHERE conditions on the nested query as well as the outer query

Coral