0

I'm having an issue in using group by and order by in one statement in Rails 3

In my controller:

def index
  @units = Unit.list_order.grouped
end

and my model:

scope :list_order, order("units.interior_code, units.created_at ASC")
scope :grouped, group("units.unit_parent")

The query is pulling back the data grouped (Unique records are displayed based on unit_parent) and is ordered by interior_code but it's not ordering the grouped records by date, i.e. there may be 4 grouped records and the query does not respect the date ordering to pull only the last record from the group

I've seen this post on SO which is a similar issue

Any help would be appreciated

Community
  • 1
  • 1
Mark Kenny
  • 1,598
  • 2
  • 17
  • 30

1 Answers1

0

If units.created_at is being ordered as a string, rather than a date, the order won't appear to be correct. Are you sure that the values for created_at are being treated as a date?

dev_etter
  • 1,156
  • 13
  • 32
  • created_at is a date/time format in the database, I wasn't aware that it needs to be treated any differently when using order_by. I got this to work (I think, need to test properly) Unit.find_by_sql('SELECT u1.* FROM units u1 LEFT JOIN units u2 ON (u1.unit_parent = u2.unit_parent AND u1.created_at < u2.created_at) WHERE u2.created_at IS NULL ORDER BY interior_code') but it's very ugly!!! – Mark Kenny Feb 16 '12 at 16:57