How to add Active Record join aliases or "why do I have aliases in my joins?"
If you've been using Rails for some time, you most probably have written queries using Active Record, so might've also used some join clauses. Maybe using ActiveRecord::QueryMethods#joins and/or ActiveRecord::QueryMethods#includes (similar way but with a different final purpose). And if you've ever wanted to add aliases to your query you had to do it the "hard" way, combining the join part with the other ORM-ish parts, e.g;
User.joins('INNER JOIN posts my_beautiful_posts ON my_beautiful_posts.user_id = users.id')
And that's all you can do. As joins are very flexible there are many ways you can end up writing them, so it's not a surprise they're not fully supported in Rails (although yes, technically you also have joins and left_outer_joins but you leave out the others).
(Or, if you wanna get creative you can use Arel and complicate your life and the life of others for a few gain :))
So it might seem interesting to you that if you mistakenly reference a table name within a query that already has a join clause, then Rails will add that as an alias to your query, e.g:
# example models to show their relations
class User < ApplicationRecord
has_many :posts
end
class Post < ApplicationRecord
belongs_to :users
end
Post
.joins(:user)
.where(user: { id: 1 })
# SELECT "posts".*
# FROM "posts"
# INNER JOIN "users" "user"
# ON "user"."id" = "posts"."user_id"
# WHERE "user"."id" = ? [["id", 1]]
And that's all that you need to introduce a bug in your application.
At first glance that will work fine because we're referencing the aliased join table in the where clause. So there's nothing wrong there, the problem will arise when time later code like this must be updated; let's say you have to extract the full name of the user from the database and for that, you use a "custom" select clause;
Post
.joins(:user)
.where(user: { id: 1 })
.select("CONCAT(users.firstname, ' ', users.lastname) AS fullname") # <- this is new
Your first attempt might be to prefix the table name while referencing the columns you're using, and for that, we do it by using the table name as it exists in the database - in plural. But now there's no table users available in our query; it's called user (singular) (which I hope it doesn't take that much time to figure out as it did to me :)).
What might seem a good feature (if any, if intended) ends up being more of a headache.
If a query without joins fails when trying to use a table name other than the ones available, then I'd expect it to work the way when doing it otherwise - when there's a join - not silently adding aliases and making a code that's supposed not to work, to work.
As I was checking if this was documented (which is not) I found an issue that more or less mentions this behavior, but only refers to the part that aliases change between different Rails versions, not whether they should or not be added to the resulting query.
But the cause (line #1311) seems just to be an un-validated modification of the self.references_values while building the where clause. The table name as it is - user from the condition hash { user: { id: 1 } } - is added to the ActiveRecord_Relation object without any prior check whether it exists or not in the database or it's available in the current query.
So, in this case, the easier seems just to try to avoid doing any mistakes while writing your queries with Active Record :)