How to join subqueries using AREL?

Question

I have several massive SQL queries involving combining different models in my rails application. One query can include from 6 to 10 tables.

To make the query faster, I want to use subqueries in joins (this way I can filter these tables before joining and reduce the columns to the ones I need). I am trying to achieve this with AREL.

I thought I found a solution to my problem there: How to do joins in AREL subqueries inside Rails , but everything should change, because I get an undefined method '[]' for Arel::SelectManager .

Does anyone know how to achieve this (without using strings)?

Answer 1

Pierre, I thought the best solution could be the following (inspiration is this point ):

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

There is no particular reason for naming an alias as "intm_table", I just thought it would be less confusing.

Answer 2

OK, so my main problem was that you cannot join Arel :: SelectManager ... BUT you can join the table alias. Therefore, 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 Bc > 4) B ON A.id = B.a_id