Rails active record querying association with 'exists'

Question

I am working on an app that allows Members to take a survey (Member has a one to many relationship with Response). Response holds the member_id, question_id, and their answer.

The survey is submitted all or nothing, so if there are any records in the Response table for that Member they have completed the survey.

My question is, how do I re-write the query below so that it actually works? In SQL this would be a prime candidate for the EXISTS keyword.

 def surveys_completed
    members.where(responses: !nil ).count
 end

MrYoshiji · Accepted Answer · 2020-03-15T17:19:18.257

123

You can use includes and then test if the related response(s) exists like this:

def surveys_completed
  members.includes(:responses).where('responses.id IS NOT NULL')
end

Here is an alternative, with joins:

def surveys_completed
  members.joins(:responses)
end

The solution using Rails 4:

def surveys_completed
  members.includes(:responses).where.not(responses: { id: nil })
end

Alternative solution using activerecord_where_assoc: This gem does exactly what is asked here: use EXISTS to to do a condition. It works with Rails 4.1 to the most recent.

members.where_assoc_exists(:responses)

It can also do much more!

Similar questions:

edited Mar 15 '20 at 17:19

answered Aug 14 '13 at 14:50

MrYoshiji

54,334
13
124
117

1

The first option gave this deprecation notice http://pastebin.com/KbsNiqLy. The second returned the same records 68 times because their are 68 questions so I modified it like this: members.joins(:responses).uniq.count – Lee Aug 14 '13 at 14:59
2

The deprecation notice can't be avoided because we need to type a string in order to says "IS NOT NULL" (not possible to translate this in pure ActiveRecord unless you use Rails 4). I will put a third possibily in a sec @lee – MrYoshiji Aug 14 '13 at 15:03
I looked more into the deprecation notice, and did this: members.includes(:responses).where('responses.id IS NOT NULL').references(:responses).count and this also works. So now it is a question of which way is the "best" way – Lee Aug 14 '13 at 15:05
Oh, and I am using Rails 4 – Lee Aug 14 '13 at 15:06
@lee Oh cool then you can translate this to ActiveRecord and not have to write any line of SQL! – MrYoshiji Aug 14 '13 at 15:09
The Rails console still says that there is a syntax error http://pastebin.com/0phjpZ0h – Lee Aug 14 '13 at 15:22
I ended up going with this: members.includes(:responses).where('responses.id IS NOT NULL').references(:responses).count – Lee Aug 14 '13 at 15:55
3

@MrYoshiji actually it's possible to do this in Rails 3 without using literal SQL strings as well: members.includes(:responses).where(Response.arel_table[:id].not_eq(nil)).count – Cameron Mar 25 '14 at 05:41
For the second answer don't forget to use `members.joins(:responses).uniq` if you don't want duplicates – camilo.forero Oct 30 '19 at 12:32

score 7 · Answer 2 · answered Sep 03 '15 at 14:55

7

You can use SQL EXISTS keyword in elegant Rails-ish manner using Where Exists gem:

members.where_exists(:responses).count

Of course you can use raw SQL as well:

members.where("EXISTS" \
  "(SELECT 1 FROM responses WHERE responses.member_id = members.id)").
  count

answered Sep 03 '15 at 14:55

EugZol

6,476
22
41

1

The other answer will not generate SQL which uses the EXIST keyword. This way will be much more performant! – rept Nov 19 '15 at 23:30

score 7 · Answer 3 · answered Oct 29 '18 at 22:14

If you are on Rails 5 and above you should use left_joins. Otherwise a manual "LEFT OUTER JOINS" will also work. This is more performant than using includes mentioned in https://stackoverflow.com/a/18234998/3788753. includes will attempt to load the related objects into memory, whereas left_joins will build a "LEFT OUTER JOINS" query.

def surveys_completed
  members.left_joins(:responses).where.not(responses: { id: nil })
end

Even if there are no related records (like the query above where you are finding by nil) includes still uses more memory. In my testing I found includes uses ~33x more memory on Rails 5.2.1. On Rails 4.2.x it was ~44x more memory compared to doing the joins manually.

See this gist for the test: https://gist.github.com/johnathanludwig/96fc33fc135ee558e0f09fb23a8cf3f1

score 6 · Answer 4 · answered Aug 04 '16 at 14:42

6

You can also use a subquery:

members.where(id: Response.select(:member_id))

In comparison to something with includes it will not load the associated models (which is a performance benefit if you do not need them).

answered Aug 04 '16 at 14:42

Markus

5,667
4
48
64

Marian13 · Answer 5 · 2021-04-15T18:32:21.477

where.missing (Rails 6.1+)

Rails 6.1 introduces a new way to check for the absence of an association - where.missing.

Please, have a look at the following code snippet:

# Before:
Post.left_joins(:author).where(authors: { id: nil })

# After:
Post.where.missing(:author)

And this is an example of SQL query that is used under the hood:

Post.where.missing(:author)
# SELECT "posts".* FROM "posts"
# LEFT OUTER JOIN "authors" ON "authors"."id" = "posts"."author_id"
# WHERE "authors"."id" IS NULL

As a result, your particular case can be rewritten as follows:

def surveys_completed
  members.where.missing(:response).count
end

Thanks.

Sources:

Notes:

where.associated - a counterpart for checking for the presence of an association is also available starting from Rails 7.
See offical docs and this answer.

Great stuff! This resolves the performance concerns of `includes` raised by other answers and is a better choice for more recent versions of Rails. — Jacob Crofts, Sep 20 '22 at 17:33

Rails active record querying association with 'exists'

5 Answers5

where.missing (Rails 6.1+)

Linked

Related