How to join on subqueries using ARel?

Question

I have a few massive SQL request involving join across various models in my rails application. A single request can involve 6 to 10 tables.

To run the request faster I want to use sub-queries in the joins (that way I can filter these tables before the join and reduce the columns to the ones I need). I'm trying to achieve this using ARel.

I thought I found the solution to my problem there: How to do joins on subqueries in AREL within Rails, but things must have changed because I get undefined method '[]' for Arel::SelectManager.

Does anybody have any idea how to achieve this (without using strings) ?

Sletten

Can you show the query you are attempting? – Jonell

Well to simplify it to the extreme level: SELECT A.* INNER JOIN (SELECT B.a_id FROM B WHERE B.c > 4) B ON A.id = B.a_id – Sletten

Can you should the Ruby code you are attempting for the query? – Jonell

Answer 1

Pierre, I thought a better solution could be the following (inspiration from this gist):

a = A.arel_table  
b = B.arel_table

subquery = b.project(b[:a_id].as('A_id')).where{c > 4}  
subquery = subquery.as('intm_table')  
query = A.join(subquery).on(subquery[:A_id].eq(a[:id]))

No particular reason for naming the alias as "intm_table", I just thought it would be less confusing.

Chutney

A.join(subquery).on(subquery[:A_id].eq(a[:id])) return error NoMethodError: undefined method join' for #<Class` – Trueblood

Answer 2

OK so my main problem was that you can't join a Arel::SelectManager ... BUT you can join a table aliasing. So to generate the request in my comment above:

a = A.arel_table
b = B.arel_table

subquery = B.select(:a_id).where{c > 4}
query = A.join(subquery.as('B')).on(b[:a_id].eq(a[:id])
query.to_sql # SELECT A.* INNER JOIN (SELECT B.a_id FROM B WHERE B.c > 4) B ON A.id = B.a_id 

Sletten

Answer 3

Was looking for this, and was helped by the other answers, but there are some error in both, e.g. A.join(... should be a.join(....
And I also missed how to build an ActiveRecord::Relation.

Here is how to build an ActiveRecord::Relation, in Rails 4

a = A.arel_table
b = B.arel_table

subsel = B.select(b[:a_id]).where(b[:c].gt('4')).as('sub_select')
joins  = a.join(subsel).on(subsel[:a_id].eq(a[:id])).join_sources
rel    = A.joins(joins)
rel.to_sql
#=> "SELECT `a`.* FROM `a` INNER JOIN (SELECT `b`.`a_id` FROM `b` WHERE (`b`.`c` > 4)) sub_select ON sub_select.`a_id` = `a`.`id`"

Hofmann