Find records with no has_many :through record matching criteria

Question

Using Rails 4, and given the following models:

class Draft < ActiveRecord::Base
  has_many :drafters
  has_many :users, through: :drafters
end

class Drafter < ActiveRecord::Base
  belongs_to :draft
  belongs_to :user
end

class User < ActiveRecord::Base
  has_many :drafters
  has_many :drafts, through: :drafters
end

How can I retrieve all Drafts which are not associated with the User instance current_user? That is, all Drafts d for which there is no Drafter belonging to d and current_user.

I have Squeel available if it helps.

potashin · Answer 1 · 2016-07-11T12:51:37.060

1

You can implement it with includes:

Draft.includes(:drafters)
     .where('(drafters.user_id <> ? or drafters.user_id is null)', current_user.id})
     .references(:drafters)

edited Jul 11 '16 at 12:51

answered Jul 11 '16 at 11:49

potashin

44,205
11
83
107

This doesn't quite work. Firstly, it doesn't find those Drafts which have _no_ drafters at all. Secondly, using it produces a deprecation: `DEPRECATION WARNING: It looks like you are eager loading table(s) (one of: drafts, drafters) that are referenced in a string SQL snippet.` - full text [here](https://gist.githubusercontent.com/asilano/823cb76f7b29af02f2ef41d24b8bc7d3/raw/3bd4470b4eb26a45bc21a4e2b9ba92a82bd31a95/gistfile1.txt) – Chowlett Jul 11 '16 at 12:13
@Chowlett: Updated. `references` removes the deprecation warning (i.e. forces `left join`), while `is null` check permits drafts with no drafters. – potashin Jul 11 '16 at 12:19
Uh, wow. That's way uglier than I was expecting to be possible. Why does it work? The SQL produced is a LEFT OUTER JOIN on Draft<->Drafter, so should turn up rows where the Drafter side is absent. Why do I need `drafters.user_id is null` to pick up the cases where no Drafter row matches on `drafter.draft_id`? – Chowlett Jul 11 '16 at 12:24
...nope, changing it to `Draft.joins...` goes back to the problem of missing Drafts with no Drafters at all. – Chowlett Jul 11 '16 at 12:27
@Chowlett: It's a pickle. Use `left join`, but check, if `id` of the left-joined table is present. – potashin Jul 11 '16 at 12:30
You've edited back to an incorrect version (the current one _still_ misses out drafts with no drafter, which I want to retrieve). If you take out the `.where.not` clause, I think it works and I'll mark it correct. While still slightly baffled. – Chowlett Jul 11 '16 at 12:37
1

Poking about in psql, I now understand the reason. First, the WHERE doesn't affect the JOIN, it just filters the result. Second NULL always compares false. That is, (NULL <> 1) is false. Thanks! – Chowlett Jul 11 '16 at 13:02
Bother. No, doesn't work. This also returns `Draft`s with multiple `Drafter`s, at least one of which isn't `current_user`; _even if_ one of the `Drafter`s is `current_user`. (Which makes sense; the draft matches "has a drafter that isn't current_user". What I need is "has no drafters that are current_user") – Chowlett Jul 22 '16 at 11:39

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

Draft.includes(:drafters).where(:drafters => { :draft_id => nil } )

will return all drafts with no drafter at all.

Draft.includes(:drafters).where.not(:drafter => { user_id => current_user.od })

will return all drafts not belonging to current_user.

For more details, look at the difference between outer an inner join.

edited May 23 '17 at 11:51

Community

1
1

answered Jul 11 '16 at 11:55

muichkine

2,890
2
26
36

score 1 · Answer 3 · edited Jul 12 '16 at 08:21

1

With Squeel, you can do:

Draft.joins{drafters.outer}.where{(drafters.user_id != current_user.id) | (drafters.user_id.eq nil)}

which will generate:

SELECT "drafts".* FROM "drafts" LEFT OUTER JOIN "drafters" ON "drafters"."draft_id" = "drafts"."id" WHERE ("drafters"."user_id" != 1 OR "drafters"."user_id" IS NULL)

edited Jul 12 '16 at 08:21

Chowlett

45,935
20
116
150

answered Jul 11 '16 at 13:06

lcguida

3,787
2
33
56

I tried this one, but in the end found it returned multiple Drafts where there were more than one non-current_user Drafters. The `includes` method appears to be the only way to go. – Chowlett Jul 14 '16 at 08:30

score 0 · Accepted Answer · answered Jul 22 '16 at 11:58

I've found an answer (with squeel) based on the following nested query:

SELECT "drafts"."id" FROM "drafts"
    WHERE "drafts"."id" NOT IN 
        (SELECT "drafters"."draft_id" FROM "drafters" 
            WHERE (("drafters"."draft_id" = "drafters"."id" 
                    AND "drafters"."user_id" = 2)))

That's convertible to the Rails+squeel query:

Draft.where{ id.not_in(Drafter.select(:draft_id).where{
        (draft_id == drafts.id) & (user_id == omit_user_id)})}

Find records with no has_many :through record matching criteria

4 Answers4