ORDER BY joined associations in Rails 6.1

rubyonrails-core
1

Correct me if I’m wrong, but as far as I understand there’s still no official support in Rails 6.1 for adding an ORDER BY clause using a column from a joined table. In Rails < 6.1 this wasn’t a huge problem because we could just “guess” the table alias and write raw SQL and most of the time the table would not be aliased anyway. This changed in Rails 6.1, however:

class Foo < ApplicationRecord
  belongs_to :bar
end

class Bar < ApplicationRecord
end

Some example queries:

irb(main):001:0> Foo.joins(:bar).to_sql
   (0.2ms)  SELECT sqlite_version(*)
=> "SELECT \"foos\".* FROM \"foos\" INNER JOIN \"bars\" ON \"bars\".\"id\" = \"foos\".\"bar_id\""

irb(main):002:0> Foo.joins(:bar).where(bar: {id: [1, 2]}).to_sql
=> "SELECT \"foos\".* FROM \"foos\" INNER JOIN \"bars\" bar ON bar.\"id\" = \"foos\".\"bar_id\" WHERE \"bar\".\"id\" IN (1, 2)"

As you can see, the alias for Bar can now change based on the presence of a where clause. The inconsistency means the ORDER BY clause also has to change based on the presence of the where clause. Here’s how raw SQL tends to fail:

irb(main):003:0> Foo.joins(:bar).where(bar: {id: [1, 2]}).order('bars.id DESC')
  Foo Load (1.1ms)  SELECT "foos".* FROM "foos" INNER JOIN "bars" bar ON bar."id" = "foos"."bar_id" WHERE "bar"."id" IN (?, ?) /* loading for inspect */ ORDER BY bars.id DESC LIMIT ?  [[nil, 1], [nil, 2], ["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: bars.id)

irb(main):004:0> Foo.joins(:bar).order('bar.id DESC')
  Foo Load (1.0ms)  SELECT "foos".* FROM "foos" INNER JOIN "bars" ON "bars"."id" = "foos"."bar_id" /* loading for inspect */ ORDER BY bar.id DESC LIMIT ?  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: bar.id)

Note: I am not sure why the /* loading for inspect */ comment is in the middle of the query.

Even the old tricks of feeding some ARel to the order method or merging with another relation fail:

irb(main):005:0> Foo.joins(:bar).where(bar: {id: [1, 2]}).order(Bar.arel_table[:id].desc)
  Foo Load (1.1ms)  SELECT "foos".* FROM "foos" INNER JOIN "bars" bar ON bar."id" = "foos"."bar_id" WHERE "bar"."id" IN (?, ?) /* loading for inspect */ ORDER BY "bars"."id" DESC LIMIT ?  [[nil, 1], [nil, 2], ["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: bars.id)

irb(main):006:0> Foo.joins(:bar).where(bar: {id: [1, 2]}).merge(Bar.order(id: :desc))
  Foo Load (1.1ms)  SELECT "foos".* FROM "foos" INNER JOIN "bars" bar ON bar."id" = "foos"."bar_id" WHERE "bar"."id" IN (?, ?) /* loading for inspect */ ORDER BY "bars"."id" DESC LIMIT ?  [[nil, 1], [nil, 2], ["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: bars.id)

So… any chance we could get official support for ORDER BY using columns from joined tables? These queries have been a huge pain point when upgrading the app to every major version since Rails 3.1.

I apologize in advance if I missed some obvious way to get this to work in Rails 6.1.

Edit: I guess the obvious workaround is:

Foo.joins(:bar).where.not(bar: {id: nil}).order('bar.id DESC')

The where clause will not filter anything but will force a consistent alias… still it doesn’t seem ideal to me.

2

Try this:

Foo.joins(:bar).where(bars: {id: [1, 2]}).to_sql
=> "SELECT \"foos\".* FROM \"foos\" INNER JOIN \"bars\" ON \"bars\".\"id\" = \"foos\".\"bar_id\" WHERE \"bars\".\"id\" IN (1, 2)"

Using bars instead of bar in the where clause avoids the aliasing.

3

This seems to be a way to defeat the aliasing mechanism, but then why is it there in the first place? Now we have to be careful to never refer to the association by its name in the query otherwise it’ll get aliased…

It’s problematic also because scopes can break each other when composed depending on whether or not they use the alias.

4

The aliasing will default to using the table name. But is mostly involved when using join. Calling .join(:bar) will join the bar relation which will be the bars table. If you had another relation that also referenced the bars table it would join that bars table again as an alias (bars_1 I believe, but i forget). The aliases may change with Rails, but is generally stable and why you can guess it if you are building the query.

where and order don’t work on relations, rather tables so when you pass bar to #where you are simply telling it to use the bar table.

If you want to build a where or order condition based on SQL you could look at ActiveRecord Filter and ActiveRecord Sort. They allow you to build queries based on the relations. For example:

Foo.filter(bar: { id: [1,2] }).sort(bar: :id).to_sql 
# => SELECT "foos".* FROM "foos"
# => LEFT OUTER JOIN "bars" ON "bars"."id" = "foos"."bar_id"
# => WHERE "bars"."id" IN (1, 2)
# => ORDER BY "bars"."id"

or go crazy, say if Bar::has_many :widgets

Foo.filter(bar: {id: [1, 2], widget: { weight: {gt: 10} } }).
    sort(bar: {widget: {length: {asc: :null_last}}).to_sql
# => SELECT "foos".* FROM "foos"
# => LEFT OUTER JOIN "bars" ON "bars"."id" = "foos"."bar_id"
# => LEFT OUTER JOIN "widgets" ON "widgets"."bar_id" = "bars"."id"
# => WHERE
# =>   "bars"."id" IN (1, 2)
# =>   AND "widgets"."weight" > 10
# => ORDER BY "widgets"."length" ASC NULL LAST

As an FYI, I built this gems and have been using them for 5+ years, but hopefully they are usefully for you in this situation.

1 Like
5

Thanks @malomalo I might take a look at your gems.

The reason I think support for something like this should be in Rails core is because the queries in question in my case have been built and rebuilt for Squeel, Baby Squeel and then directly ARel and each of those got broken in turn due to changes to ActiveRecord along the years. If support for this was in core, this situation likely wouldn’t happen (and it’s not any new concept: ordering by joined columns has been in SQL for decades, same with joining a table twice in 1 query, etc). In the absence of official support, we are forced to rely on third party gems for solutions, which (understandably) don’t get supported forever by their creators, or roll our own, which will also tend to get broken in future updates.