ActiveRecord’s `where.associated`

What’s the point?

Recently we finished upgrading Florence to Rails 7. Naturally, we started exploring all of the features we now had access to. This led to an unexpectedly interesting discussion: What’s the point of ActiveRecord’s where.associated method?

Here is the SQL which the method generates:

Post.where.associated(:author)

SELECT "posts".* FROM "posts"
INNER JOIN "authors" ON "authors"."id" = "posts"."author_id"
WHERE "authors"."id" IS NOT NULL

My question was this:

What is the purpose of that WHERE clause?

That is to say, would the following query not produce the same behaviour?

Post.joins(:author)

SELECT "posts".* FROM "posts"
INNER JOIN "authors" ON "authors"."id" = "posts"."author_id"

I couldn’t see why not.

I posed the question to our CTO over the water cooler and this was his response:

I think this is for situations where the foreign keys can be blank e.g. post with an author_id but where authors.id is not a required field.

In this obscure case, just using Post.joins(:author) will return joined results where post has no author id and all authors without an id, which may not be desired, whereas Post.where.associated(:author) would ignore the null cases.

A standard join will check the joined fields match, even if it means joining null values to null values.

I can’t think of where we do anything like this though.

This made sense to me. associated and joins would only differ in behaviour in the (perhaps obscure) case where you are joining on two columns which both contain NULL values.

I had also posed the question to my colleague Daniel whose SQL is leagues better than mine. He weighed in with the following:

I don’t think joins can ever happen on NULLs, since in most SQL databases NULLs are not equal.

I gave it a try:

SELECT * FROM "posts" WHERE NULL = NULL

As usual, Daniel was correct; this query matched no rows!

This was new to me. It confirmed that we were able to disregard the scenario of joining rows on NULL values. Now I was again under the impression that where.associated was functionally equivalent to joins.

I decided to track down the pull request which introduced the associated method to see if it would shed any light. It did. The consensus seemed to be that the associated and joins strategies were equivalent. https://github.com/rails/rails/pull/40696

The conclusion of the office discussion was the same as that of the original Pull Request: the associated method provides a piece of syntactic sugar to allow your Rails code to more clearly signal its intent. It also neatly mirrors Rails 6’s where.missing method.

I’m not sure I agree that the intent of where.associated is more clear than that of joins. The ActiveRecord joins method tells me that we are performing an SQL INNER JOIN. In this regard, the use of ActiveRecord’s associated method adds a layer of abstraction which, for me, makes the intention of the code less clear.

That being said, ActiveRecord has always allowed us to query databases without needing to know SQL syntax or semantics and the behaviour of this new method is certainly easier to grasp for anybody unfamiliar with SQL.