Rails - scope for records that are not in a join table alongside a specific association

Question

I have two models in a Rails app - Tournament and Player associated through a join table:

class Tournament < ApplicationRecord

  has_many :tournament_players
  has_many :players, through: :tournament_players

end


class Player < ApplicationRecord

  has_many :tournament_players
  has_many :tournaments, through: :tournament_players

  scope :selected, -> (tournament) { includes(:tournaments).where(tournaments: {id: tournament.id}) }

end

I have lots of Tournaments, and each one can have lots of Players. Players can play in lots of Tournaments. The scope

scope :selected, -> (tournament) { includes(:tournaments).where(tournaments: {id: tournament.id}) }

successfuly finds all the players already added to a tournament, given that tournament as an argument.

What I'd like is a scope that does the opposite - returns all the players not yet added to a given tournament. I've tried

scope :not_selected, -> (tournament) { includes(:tournaments).where.not(tournaments: {id: tournament.id}) }

but that returns many of the same players, I think because the players exist as part of other tournaments. The SQL for that looks something like:

SELECT "players".*, "tournaments”.* FROM "players" LEFT OUTER JOIN
"tournament_players" ON "tournament_players"."player_id" =
"players"."id" LEFT OUTER JOIN "tournaments" ON "tournaments"."id" =
"tournament_players"."tournament_id" WHERE ("tournaments"."id" != $1)
ORDER BY "players"."name" ASC  [["id", 22]]

I've also tried the suggestions on this question - using

scope :not_selected, -> (tournament) { includes(:tournaments).where(tournaments: {id: nil}) }

but that doesn't seem to work - it just returns an empty array, again I think because the Players exist in the join table as part of a separate Tournament. The SQL for that looks something like:

SELECT "players”.*, "tournaments”.* FROM "players" LEFT OUTER JOIN
"tournament_players" ON "tournament_players"."player_id" = 
"players"."id" LEFT OUTER JOIN "tournaments" ON "tournaments"."id" = 
"tournament_players"."tournament_id" WHERE "tournaments"."id" IS NULL 
ORDER BY "players"."name" ASC

Possible duplicate of [Rails find record with zero has\_many records associated](http://stackoverflow.com/questions/9613717/rails-find-record-with-zero-has-many-records-associated) — David Aldridge, Sep 10 '16 at 12:21
See my edit - from what I can tell those solutions aren't quite right. — Sean Hawkridge, Sep 10 '16 at 18:56
sql added. I just can't get my head wrapped around the search I need! — Sean Hawkridge, Sep 10 '16 at 19:27

Pelle · Accepted Answer · 2016-09-12T14:08:39.803

4

What you need to do is:

Make a left join with the reference table, with an additional condition on the tournament ID matching the one that you want to find the not-selected players for
Apply a WHERE clause indicating that there was no JOIN made.

This code should do it:

# player.rb
scope :not_selected, -> (tournament) do 
  joins("LEFT JOIN tournament_players tp ON players.id = tp.player_id AND tp.tournament_id = #{tournament.id}").where(tp: {tournament_id: nil})
end

If only Rails had a nicer way to write LEFT JOIN queries with additional conditions...

A few notes:

Don't join the actual relation (i.e. Tournament), it dramatically decreases performance of your query, and it's unnecessary, because all your condition prerequisites are inside the reference table. Besides, all the rows you're interested in return NULL data from the tournaments table.
Don't use eager_load. Besides to my best knowledge its not supporting custom conditions, it would create models for all related objects, which you don't need.

edited Sep 12 '16 at 14:08

answered Sep 10 '16 at 20:03

Pelle

6,423
4
33
50

Thanks for this! It's throwing a few errors though. The first is that `Player` doesn't know what the argument `tournament_id` is that's being passed to `find_not_in_tournament`. The second is that `find_not_in_tournament` is undefined? Also the scope is missing the opening `do`. – Sean Hawkridge Sep 11 '16 at 06:37
Scrap that - I'm not clear what the `find_not_in_tournament` method call at the start is for, but without it this works perfectly. If you edit it to include the missing `do` and remove the `find_not_in_tournament`, I'll mark it as the correct answer. Thanks! – Sean Hawkridge Sep 11 '16 at 06:51
I wrote this from the top of my head, and I wrote a method first, after which I read that what you needed was a scope. I guess I was in a hurry. Thanks for the update. – Pelle Sep 12 '16 at 14:09

Bartłomiej Gładys · Answer 2 · 2016-09-10T13:07:48.917

0

ok try this:

includes(:tournaments).distinct.where.not(tournaments: {id: tournament.id})

edited Sep 10 '16 at 13:07

answered Sep 10 '16 at 12:25

Bartłomiej Gładys

4,525
1
14
24

Thanks - but the `:not_selected` scope throws this error: `ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "tournaments"` – Sean Hawkridge Sep 10 '16 at 12:35
can you change , instead `includes` put `joins` ? – Bartłomiej Gładys Sep 10 '16 at 12:38
Nope - get this error instead: `ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "tournaments.id" must appear in the GROUP BY clause or be used in an aggregate function` – Sean Hawkridge Sep 10 '16 at 12:40
can you try second option ? – Bartłomiej Gładys Sep 10 '16 at 12:43
That one just returns an empty array. – Sean Hawkridge Sep 10 '16 at 12:46
Unfortunately that just returns every player. – Sean Hawkridge Sep 10 '16 at 13:12

Rails - scope for records that are not in a join table alongside a specific association

2 Answers2