Subqueries in activerecord

ruby-on-rails-3.1 subquery arel
gucki picture gucki · Mar 30, 2011 · Viewed 55.2k times · Source

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

Christopher Lindblom picture Christopher Lindblom · May 31, 2012

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)

Related questions

ExecJS and could not find a JavaScript runtime

I'm trying to use the Mongoid / Devise Rails 3.1 template (Mongoid and Devise), and I keep getting an error stating ExecJS cannot find a JavaScript runtime. Fair enough when I didn't have any installed, but I've tried installing Node.js, Mustang …

Read More
execJs: 'Could not find a JavaScript runtime' but execjs AND therubyracer are in Gemfile

I'm getting this error: rake aborted! Could not find a JavaScript runtime. See https://github.com/sstephenson/execjs, I have already spent more hours searching google then I am willing to admit. I believe this is an execJs bug. From …

Read More
Rails - Could not find a JavaScript runtime?

I created a new Rails project using rails 3.1.0.rc4 on my local machine but when I try to start the server I get: Could not find a JavaScript runtime. See here for a list of available runtimes. (ExecJS::RuntimeUnavailable) Note: …

Read More