Subqueries in activerecord

Question

With SQL I can easily do sub-queries like this

User.where(:id => Account.where(..).select(:user_id)) 

This produces:

SELECT * FROM users WHERE id IN (SELECT user_id FROM accounts WHERE ..) 

How can I do this using rails' 3 activerecord/ arel/ meta_where?

I do need/ want real subqueries, no ruby workarounds (using several queries).

Answer 1

Rails now does this by default :)

Message.where(user_id: Profile.select("user_id").where(gender: 'm')) 

will produce the following SQL

SELECT "messages".* FROM "messages" WHERE "messages"."user_id" IN (SELECT user_id FROM "profiles" WHERE "profiles"."gender" = 'm') 

(the version number that "now" refers to is most likely 3.2)

Answer 2

In ARel, the where() methods can take arrays as arguments that will generate a "WHERE id IN..." query. So what you have written is along the right lines.

For example, the following ARel code:

User.where(:id => Order.where(:user_id => 5)).to_sql 

... which is equivalent to:

User.where(:id => [5, 1, 2, 3]).to_sql 

... would output the following SQL on a PostgreSQL database:

SELECT "users".* FROM "users" WHERE "users"."id" IN (5, 1, 2, 3)"  

Update: in response to comments

Okay, so I misunderstood the question. I believe that you want the sub-query to explicitly list the column names that are to be selected in order to not hit the database with two queries (which is what ActiveRecord does in the simplest case).

You can use project for the select in your sub-select:

accounts = Account.arel_table User.where(:id => accounts.project(:user_id).where(accounts[:user_id].not_eq(6))) 

... which would produce the following SQL:

SELECT "users".* FROM "users" WHERE "users"."id" IN (SELECT user_id FROM "accounts" WHERE "accounts"."user_id" != 6) 

I sincerely hope that I have given you what you wanted this time!