ruby-on-rails activerecord ruby-on-rails-7 arel

How do I order by lower-cased field on an aliased association without manually specifying a join?

I have the following models, with matching tables and FKs.

class Organization < ActiveRecord
end

class Identifier < ActiveRecord
  belongs_to :owner, class_name: "Organization"
  belongs_to :organization
end

ie both identifiers.owner_id and identifiers.organization_id are FKs into organizations. This is quite a common pattern I see a lot in Rails apps.

I am trying to order identifiers by owner name, case-insenstive. IE I need the SQL:

SELECT identifiers.* FROM identifiers 
INNER JOIN public.organizations owner ON owner.id = identifiers.owner_id 
ORDER BY LOWER(owner.name) ASC

I can run Identifier.joins(:owner).order("owner.name ASC"), and Rails will helpfully spot that owner referenced in .order references the join to owner, so aliases organizations to owner. However, as soon as I try:

Identifier.joins(:owner).order("LOWER(owner.name) ASC")

Rails no longer knows to alias the association to organizations to owner.

I have tried playing around with different strings and with Arel, but I can't for the life of me get anything to work. Instead, I have to manually do:

Identifier.joins("JOIN organizations AS owners ON identifiers.owner_id = owners.id")
.order("LOWER(owners.name) ASC")

This works, but it's very annoying having to manually specify the JOIN when it's already specified on the model (implementation is leaking).

Does anyone know if it is possible in Rails without manually specifying the join? If not, I might try and open a PR on the Rails repo...

Solution

I would just accept that ActiveRecord assocations are a leaky abstraction and just reference the table by it's name instead.

Identifier.joins(:owner)
          .order(Organization.arel_table[:name].lower.desc)

Since this is not referencing the other assocation there is no ambigiouty if that's what you're worried about.

Because of how joins interprets hashes as nested joins there isn't any good way to specify an alias unless you use a SQL string or Arel.

If you want to reuse the join from the query iterface you can deconstruct it:

j = Identifier.joins(:owner).arel # converts the query into a Arel::SelectManager
              .join_sources[0]    # join_sources is an array of the joins
j.left = j.left.alias('owner')    # right and left are the tables of the join
Identifier.joins(j)

Identifier Load (0.6ms)  
SELECT "identifiers".* 
FROM "identifiers" 
INNER JOIN "organizations" "owner" 
  ON "organizations"."id" = "identifiers"."owner_id"

Not so sure if that's really better from a redability standpoint than just creating the whole thing from scratch.

i, o = Identifier.arel_table, Organization.arel_table.alias('owner')
j = i.create_join(o, i.create_on(i[:owner_id].eq(o[:id])))
Identifier.joins(j)

If not, I might try and open a PR on the Rails repo...

It's not a bug. The signature of the method is what it is.

The way that ActiveRecord aliases tables in joins is admittedly pretty stupid:

irb(main):066:0> Identifier.joins(:owner, :organization)                                                                  Identifier Load (6.9ms)
SELECT "identifiers".* 
FROM "identifiers" 
INNER JOIN "organizations" 
  ON "organizations"."id" = "identifiers"."owner_id" 
INNER JOIN "organizations" "organizations_identifiers" 
  ON "organizations_identifiers"."id" = "identifiers"."organization_id"

But it's the intended behavior at this point and changing it would likely break a lot of existing apps and the parts of Rails that rely on the current behavior. If you search the issues you will probally find multiple failed attempts to fix this.