Order a query based on comparison of associated records

Question

class Man
  has_many :sons

  # id
end

class Son
  belongs_to :man

  # id, man_id, age
end

I was to retrieve men from the DB and I want them ordered based on the age of their oldest son. Here's an example.

first_man = Man.create
first_man.sons.create(age: 10)
first_man.sons.create(age: 5)

second_man = Man.create
second_man.sons.create(age: 20)
second_man.sons.create(age: 5)

third_man = Man.create
third_man.sons.create(age: 19)
third_man.sons.create(age: 8)

Man.order('[some order]').to_a
=> [second_man, third_man, first_man]

How do I get ActiveRecord to do this?

Edit

I get invalid SQL when I try to do Man.joins(:sons).order("sons.age DESC").uniq.

ActiveRecord::StatementInvalid:
       PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
       LINE 1: ...sons"."man_id" = "men"."id"  ORDER BY sons...
                                                                    ^
       : SELECT  DISTINCT "men".* FROM "men" INNER JOIN "sons" ON "sons"."man_id" = "men"."id"  ORDER BY sons.age DESC LIMIT 15

score 0 · Accepted Answer · answered Mar 23 '14 at 02:48

0

Not tried it but i guess this should work

 Man.includes(:sons).order("sons.age").to_a

answered Mar 23 '14 at 02:48

Paritosh Piplewar

7,982
5
26
41

user3309314 · Answer 2 · 2014-03-23T03:41:31.010

0

Try this

Man.joins(:sons).order('sons.age DESC').uniq

Updated

Maybe this will help but is's ugly

Son.order('age DESC').map(&:man).uniq

edited Mar 23 '14 at 03:41

answered Mar 23 '14 at 02:49

user3309314

2,453
2
17
30

@Rafa: what are you referring to? – Qantas 94 Heavy Mar 23 '14 at 03:16
Sorry, I just realised about the time, he was first. Hence I deleted my answer. – Rafa Paez Mar 23 '14 at 03:19

Order a query based on comparison of associated records

2 Answers2