2

I have an Album object that has many Tracks. Tracks have a youtube_uid column. I'd like to query albums where all of their tracks' youtube_uids are present. I know the technique to find albums with tracks where there's at least one track with a youtube_uid:

Album.left_outer_joins(:tracks).where.not(tracks: { youtube_uid: nil })

What would be the ideal query to find an album where every one of its' tracks has a youtube_uid?

Carl Edwards
  • 13,826
  • 11
  • 57
  • 119
  • Hmm, I think you need to do something like `WHERE NOT EXISTS(SELECT 1 FROM tracks WHERE tracks.youtube_uid IS NULL AND tracks.album_id = album.id)`. – max Aug 26 '20 at 14:08
  • @max This sounds like we're getting close but run into a syntax error. `PG::SyntaxError: ERROR: syntax error at or near "WHERE" (ActiveRecord::StatementInvalid) LINE 1: SELECT "albums".* FROM "albums" WHERE (WHERE NOT EXISTS(SELE...` – Carl Edwards Aug 26 '20 at 14:11
  • `Album.where('NOT EXISTS(SELECT 1 FROM tracks WHERE tracks.youtube_uid IS NULL AND tracks.album_id = albums.id)')` – max Aug 26 '20 at 14:16
  • @max That seemed to do the trick. Feel free to post this solution and I can mark this as the correct answer. – Carl Edwards Aug 26 '20 at 14:25

2 Answers2

3

As I understand your question, you want to find all albums that do not have a track with missing (empty) youtube-uid. So afaik you need a NOT EXISTS query.

In sql I would write something like

 select * from albums a
 where not exists (select * from tracks where album_id = a.id and youtube_uid is null)

So how do we best translate this to activerecord? I see two possibilities:

 sql = <<-SQL
   select * from albums a
   where not exists (select * from tracks where album_id = a.id and youtube_uid is null) 
 SQL

 Album.find_by_sql(sql)

while this works, and for me being pretty at home in SQL, this feels fine, it is not very "rails-like", so can we improve this?

There is a shorter form:

Album.where("not exists (select * from tracks where album_id = albums.id and youtube_uid is null")

but this still feels a little too verbose. Luckily there exists a more rails-like way. In rails 4 you can write:

  Album.where(Track.where("album_id = albums.id").where(youtube_uid: nil).exists.not)

In rails 5/6 this is no longer possible, and you have to write:

  Album.where(Track.where("album_id = albums.id").where(youtube_uid: nil).arel.exists.not)

you can easily verify this generates the good sql by adding to_sql at the end.

nathanvda
  • 49,707
  • 13
  • 117
  • 139
  • The query is solid but you go astray when it comes to adapting it to Rails. You don't need to use `find_by_sql` as you can just use `Album.where('NOT EXISTS ...')`. `.exists.not` would work if you called it on the `arel_table` but it won't work on a ActiveRecord Relation. See https://stackoverflow.com/questions/7152424/rails-3-arel-for-not-exists – max Aug 26 '20 at 14:22
  • Thanks. I ran into this error: ` undefined method `exists' for # (NoMethodError) Did you mean? exists?`. After trying out the suggested method I got: `PG::UndefinedTable: ERROR: missing FROM-clause entry for table "albums" (ActiveRecord::StatementInvalid) LINE 1: SELECT "tracks".* FROM "tracks" WHERE (album_id = albums.id)...`. Do you think I need a joins clause? – Carl Edwards Aug 26 '20 at 14:23
  • Thank you @max good suggestions. "going astray" is probably a bit strong, the `find_by_sql` does work. But right, I tested this in rails 4, where it works. In rails 5/6 you have to write `where(...).arel.exists`. I updated the answer accordingly. – nathanvda Aug 26 '20 at 14:42
  • 1
    I agree that astray was a bit harsh. The results from `find_by_sql` are less useful though as it returns raw query results instead of a ActiveRecord::Relation object. – max Aug 26 '20 at 16:02
  • To be explicit: the `find_by_sql` does return `Album`s (so not entirely "raw"), but yes it is no longer chainable, which makes it a lot less useful indeed. So definitely we would/should try to avoid using `find_by_sql`; but in difficult cases I first try to "solve" it using SQL, and then try to write in `ActiveRecord` (if possible) (so I was showing my thought-process as well which is probably a bit too much information). – nathanvda Aug 26 '20 at 16:32
  • It's worth noting that by using an inverse EXISTS rather than an inner join, this query will also return albums with no tracks at all; it also skips any scopes defined explicitly on the Album::tracks relation. – inopinatus Aug 27 '20 at 00:35
1

We shall achieve it with Group by and having as well:

Album.left_outer_joins(:tracks).group(:id).having('COUNT(tracks.id) = COUNT(tracks.youtube_uid)')
user11350468
  • 1,357
  • 1
  • 6
  • 22