How to join on subqueries using ARel?

sql ruby-on-rails-3 activerecord ruby-on-rails-3.2 arel
Pierre Schambacher picture Pierre Schambacher · Apr 3, 2013 · Viewed 7.6k times · Source

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

Answer

Ashwin Saval picture Ashwin Saval · Nov 6, 2013

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.

Related questions

How do you manually execute SQL commands in Ruby On Rails using NuoDB

I'm trying to manually execute SQL commands so I can access procedures in NuoDB. I'm using Ruby on Rails and I'm using the following command: ActiveRecord::Base.connection.execute("SQL query") The "SQL query" could be any SQL command. Like …

Read More
ActiveRecord: List columns in table from console

I know that you can ask ActiveRecord to list tables in console using: ActiveRecord::Base.connection.tables Is there a command that would list the columns in a given table?

Read More
Find all records which have a count of an association greater than zero

I'm trying to do something that I thought it would be simple but it seems not to be. I have a project model that has many vacancies. class Project < ActiveRecord::Base has_many :vacancies, :dependent => :destroy end I …

Read More