CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecord
djfdev
djfdev

Reputation: 6037

Ordering records from `has_many through, -> { distinct }` by an associated value

I'm using a SELECT DISTINCT and having issues trying to order my results by an associated value. Take this example (in Rails):

I have a Cook model with a distinct customers association:

has_many :customers, -> { distinct }, through: :orders, source: :user

It's distinct because I only want to count customers once, even if they have multiple orders.

The generated SQL for this is:

> cook.customers.to_sql
=> "SELECT DISTINCT \"users\".* FROM \"users\"
 INNER JOIN \"orders\" ON \"users\".\"id\" = \"orders\".\"user_id\"
 INNER JOIN \"meals\" ON \"orders\".\"meal_id\" = \"meals\".\"id\"
 WHERE \"meals\".\"cook_id\" = 1"

This works fine! (the orders table is associated through meals but feel free to ignore)

I want to order this list of customers by the most recent created_at value of the orders of those customers. But when I do this, I get an error:

cook.customers.order('orders.created_at DESC').to_sql
=> "SELECT DISTINCT \"users\".* FROM \"users\"
    INNER JOIN \"orders\" ON \"users\".\"id\" = \"orders\".\"user_id\"
    INNER JOIN \"meals\" ON \"orders\".\"meal_id\" = \"meals\".\"id        \"WHERE \"meals\".\"cook_id\" = 1  ORDER BY orders.created_at DESC"
  > cook.customers.order('orders.created_at DESC')
  ActiveRecord::StatementInvalid: PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
  LINE 1: ...eals"."id" WHERE "meals"."cook_id" = $1  ORDER BY orders.cre...

Even if I explicitly join the tables (don't think I should need to because of has_many through), this still doesn't work.

cook.customers.joins(:orders).order('orders.created_at DESC').to_sql
=> "SELECT DISTINCT \"users\".* FROM \"users\" INNER JOIN \"orders\" \"orders_users\" ON \"orders_users\".\"user_id\" = \"users\".\"id\" INNER JOIN \"orders\" ON \"users\".\"id\" = \"orders\".\"user_id\" INNER JOIN \"meals\" ON \"orders\".\"meal_id\" = \"meals\".\"id\" WHERE \"meals\".\"cook_id\" = 1  ORDER BY orders.created_at DESC"

Any clues? I'm looking for a solution that will return an ActiveRecord relation (so no using sort_by), because I plan on chaining additional queries to this.

Thank you :)

Upvotes: 1

Views: 683

Answers (2)

Juan José Ramírez
Juan José Ramírez

Reputation: 641

For me this worked. My model Apartment.rb has default scope ordering.

This doesn't work

has many :apartments
has_many :buildings, -> {distinct}, through: :apartments

This does

has many :apartments
has_many :buildings, through: :apartments

def buildings
  super.uniq
end

Upvotes: 1

Larry Lv
Larry Lv

Reputation: 759

You don't have to manually specify the join conditions, you could do it by using includes and ActiveRecord will take care of the join conditions.

cook.customers.includes(:orders).order('orders.created_at DESC').references(:orders).to_sql

Upvotes: 0

Related Questions