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

Christopher Lindblom · Answer 1 · 2018-06-14T04:59:53.157

133

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)

edited Jun 14 '18 at 04:59

answered May 31 '12 at 17:39

Christopher Lindblom

1,376
1
8
6

6

How to do the same if the condition is NOT IN? – coorasse Feb 23 '13 at 18:35
13

@coorasse: If you are using Rails 4, there is a now a [`not` condition](http://guides.rubyonrails.org/active_record_querying.html#not-conditions). I was able to accomplish it in Rails 3 by adjusting the approach in [this post](http://pivotallabs.com/hacking-a-subselect-in-activerecord/): `subquery = Profile.select("user_id").where(gender: 'm')).to_sql; Message.where('user_id NOT IN (#{subquery}))` Basically, `ActiveRecord` methods are used to create the completed, properly quoted subquery, which is then inlined into the outer query. The main downside is that subquery params are not bound. – twelve17 Jul 23 '13 at 20:35
4

Just to finish @twelve17's point about Rails 4, the specific not syntax is `Message.where.not(user_id: Profile.select("user_id").where(gender: 'm'))` - that generates a "NOT IN" subselect. Just solved my problem.. – Steve Midgley Sep 30 '14 at 22:05
1

@ChristopherLindblom When you say Rails "now" does this by default, what exactly do you mean? As of Rails 3.2? It would be nice if we could change the answer to say, "Rails does this by default as of version X". – Jason Swett Aug 02 '17 at 21:08
@JasonSwett Im sorry i dont know, it was probably 3.2 as you say as it was the current version of the times and only ran the released versions. Will think about future proofing anwers going forward, thank you for pointing this out. – Christopher Lindblom Jun 14 '18 at 04:59
is there a way to put a subquery as `value` on an update query. Like `model.updates_columns(field: my_subquery_here)`? I made a question [here](https://stackoverflow.com/questions/61703714/activerecord-model-update-column-with-subquery) – CamiloVA May 11 '20 at 22:26

Scott · Answer 2 · 2011-04-19T09:31:59.373

43

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!

edited Apr 19 '11 at 09:31

answered Apr 18 '11 at 21:06

Scott

17,127
5
53
64

Yes, but this is exactly what I do *not* want because it generates two separate queries and not a single one with containing one subquery. – gucki Apr 19 '11 at 07:32
Apologies for misunderstanding your question. Could you give an example of what you want your SQL to look like? – Scott Apr 19 '11 at 08:43
No problem. It's already mentioned above: SELECT * FROM users WHERE id IN (SELECT user_id FROM accounts WHERE ..) – gucki Apr 19 '11 at 08:50
1

Ah, okay. I get what you're saying now. I see what you mean about 2 queries being generated. Fortunately, I know how to fix your problem! (see revised answer) – Scott Apr 19 '11 at 09:52

score 24 · Answer 3 · answered Jan 03 '12 at 05:55

24

I was looking for the answer to this question myself, and I came up with an alternative approach. I just thought I'd share it - hope it helps someone! :)

# 1. Build you subquery with AREL.
subquery = Account.where(...).select(:id)
# 2. Use the AREL object in your query by converting it into a SQL string
query = User.where("users.account_id IN (#{subquery.to_sql})")

Bingo! Bango!

Works with Rails 3.1

answered Jan 03 '12 at 05:55

John

9,254
12
54
75

4

it executes the first query twice. it's better to do `subquery = Account.where(...).select(:id).to_sql` `query = User.where("users.account_id IN (#{subquery})")` – coorasse Feb 23 '13 at 18:33
10

It would only execute the first query twice in your REPL because its calling to_s on the query to display it. It would only execute it once in your application. – Ritchie Jan 14 '15 at 06:24
What if we want multiple columns from account tables ? – Ahmad hamza Jun 01 '18 at 11:09

score 0 · Answer 4 · answered Sep 26 '18 at 18:00

0

Another alternative:

Message.where(user: User.joins(:profile).where(profile: { gender: 'm' })

answered Sep 26 '18 at 18:00

lobati

9,284
5
40
61

aabiro · Answer 5 · 2019-02-15T09:54:39.040

This is an example of a nested subquery using rails ActiveRecord and using JOINs, where you can add clauses on each query as well as the result :

You can add the nested inner_query and an outer_query scopes in your Model file and use ...

  inner_query = Account.inner_query(params)
  result = User.outer_query(params).joins("(#{inner_query.to_sql}) alias ON users.id=accounts.id")
   .group("alias.grouping_var, alias.grouping_var2 ...")
   .order("...")

An example of the scope:

   scope :inner_query , -> (ids) {
    select("...")
    .joins("left join users on users.id = accounts.id")
    .where("users.account_id IN (?)", ids)
    .group("...")
   }

Subqueries in activerecord

5 Answers5

Linked