Last week I noticed quite good example to illustrate the problem of finding records with empty association, i.e. when particular has_many association returns zero records.
In this post I’d love to go a little bit further into proposed solution and figure out why it actually works.
Following original question, let’s say we’ve two classes:
1 2 3
1 2 3
And while solution is easy:
It might not feel quite obvious, for example, some peoples critiques:
Isn’t it looking for photos that don’t have a city_id? That’s not the same as cities for which there is no photo with that particular city’s id as the foreign key
In order to figure that out let’s refer to the documentation:
According to which, conditions or orders that references the tables other than the main one, cause Active Record to fall back to LEFT OUTER JOIN based strategy.
This will result in a single SQL query with joins along the lines of:
Or in our case with cities and photos:
The key thing here is the
LEFT OUTER JOIN which, after the “normal” join,
adds remaining records from cities which are not in the result set and placing
NULLs for all the fields of photos.
Let’s see by example:
1st Step: Inner Join
2nd Step: Left Outer Join
Resulting Table: WHERE clause
And that’s exactly what we’re looking for :)
Good article explaining outer joins: http://www.postgresqlforbeginners.com/2010/11/sql-outer-joins.html