# ORDER BY joined associations in Rails 6.1

**URL:** <https://discuss.rubyonrails.org/t/order-by-joined-associations-in-rails-6-1/77080>\
**Category:** rubyonrails-core\
**Created:** [February 1, 2021, 11:40am UTC](https://discuss.rubyonrails.org/t/order-by-joined-associations-in-rails-6-1/77080 "2021-02-01T11:40:44Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dantemss](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/dantemss/32/2942_2.png) [@Dantemss](https://discuss.rubyonrails.org/u/Dantemss)\
**Post date:** [February 1, 2021, 11:40am UTC](https://discuss.rubyonrails.org/t/order-by-joined-associations-in-rails-6-1/77080/1 "2021-02-01T11:40:44Z")

</div>

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:

```ruby
class Foo < ApplicationRecord
  belongs_to :bar
end

class Bar < ApplicationRecord
end

```

Some example queries:

```ruby
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\""

```

```ruby
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:

```ruby
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)

```

```ruby
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:

```ruby
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)

```

```ruby
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:

```ruby
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.

---

<div class="post-metadata">

**Author:** ![mvz](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/mvz/32/1375_2.png) [@mvz](https://discuss.rubyonrails.org/u/mvz)\
**Post date:** [February 2, 2021, 7:20am UTC](https://discuss.rubyonrails.org/t/order-by-joined-associations-in-rails-6-1/77080/2 "2021-02-02T07:20:16Z")

</div>

> [@Dantemss](#):
>
> `Foo.joins(:bar).where(bar: {id: [1, 2]}).to_sql`

Try this:

```ruby
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.

---

<div class="post-metadata">

**Author:** ![Dantemss](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/dantemss/32/2942_2.png) [@Dantemss](https://discuss.rubyonrails.org/u/Dantemss)\
**Post date:** [February 2, 2021, 4:01pm UTC](https://discuss.rubyonrails.org/t/order-by-joined-associations-in-rails-6-1/77080/3 "2021-02-02T16:01:06Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![malomalo](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/malomalo/32/1353_2.png) [@malomalo](https://discuss.rubyonrails.org/u/malomalo)\
**Post date:** [February 7, 2021, 3:12am UTC](https://discuss.rubyonrails.org/t/order-by-joined-associations-in-rails-6-1/77080/4 "2021-02-07T03:12:58Z")

</div>

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](https://github.com/malomalo/activerecord-filter) and [ActiveRecord Sort](https://github.com/malomalo/activerecord-sort). They allow you to build queries based on the relations. For example:

```ruby
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`

```ruby
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.

---

<div class="post-metadata">

**Author:** ![Dantemss](https://sea2.discourse-cdn.com/rubyonrails/user_avatar/discuss.rubyonrails.org/dantemss/32/2942_2.png) [@Dantemss](https://discuss.rubyonrails.org/u/Dantemss)\
**Post date:** [February 7, 2021, 4:51am UTC](https://discuss.rubyonrails.org/t/order-by-joined-associations-in-rails-6-1/77080/5 "2021-02-07T04:51:48Z")

</div>

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.