Finding objects only where all of their has_many collections have a value for a specific column

user_3046413 0
Viewed 269

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?

relational-division postgresql activerecord ruby-on-rails
2 Answers

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.

user_2165130

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)')
user_113504680
Related