CODEWITHSUNDEEP
sqlruby-on-railssortingactiverecord
Brandon
Brandon

Reputation: 3130

Rails Post Date Sorting Troubles

I'm having issues sorting posts by date using ActiveRecord... it's not doing what I expect.

1.9.3p194 :019 > Post.all(:select => [:id, :updated_at], :order => "updated_at DESC")
  Post Load (0.7ms)  SELECT id, updated_at FROM "posts" ORDER BY published_at DESC, updated_at DESC
+----+---------------------------+
| id | updated_at                |
+----+---------------------------+
| 22 | 2012-08-16 18:59:28 -0600 |
| 9  | 2012-08-16 18:58:16 -0600 |
| 11 | 2012-08-15 20:18:53 -0600 |
| 1  | 2012-08-15 20:18:52 -0600 |
| 12 | 2012-08-15 20:18:53 -0600 |
| 6  | 2012-08-15 20:18:52 -0600 |
| 13 | 2012-08-15 20:18:53 -0600 |
| 2  | 2012-08-15 20:18:52 -0600 |
| 15 | 2012-08-15 20:18:53 -0600 |
| 5  | 2012-08-16 21:49:14 -0600 |
| 17 | 2012-08-15 20:18:53 -0600 |
| 4  | 2012-08-15 20:18:52 -0600 |
| 20 | 2012-08-15 20:18:53 -0600 |
| 7  | 2012-08-15 20:18:52 -0600 |
| 21 | 2012-08-15 20:18:53 -0600 |
| 14 | 2012-08-15 20:18:53 -0600 |
| 10 | 2012-08-15 20:18:53 -0600 |
| 8  | 2012-08-15 20:18:53 -0600 |
| 19 | 2012-08-15 20:18:53 -0600 |
| 18 | 2012-08-15 20:18:53 -0600 |
| 16 | 2012-08-15 20:18:53 -0600 |
| 3  | 2012-08-15 20:18:52 -0600 |
+----+---------------------------+

Note that post #5 has an updated at date of August 16, but it is not going to the top! Note that Rails seems to correctly sort it....

1.9.3p194 :020 > Post.all(:select => [:id, :updated_at], :order => "updated_at DESC").sort_by &:updated_at
  Post Load (0.6ms)  SELECT id, updated_at FROM "posts" ORDER BY published_at DESC, updated_at DESC
+----+---------------------------+
| id | updated_at                |
+----+---------------------------+
| 1  | 2012-08-15 20:18:52 -0600 |
| 2  | 2012-08-15 20:18:52 -0600 |
| 3  | 2012-08-15 20:18:52 -0600 |
| 4  | 2012-08-15 20:18:52 -0600 |
| 6  | 2012-08-15 20:18:52 -0600 |
| 7  | 2012-08-15 20:18:52 -0600 |
| 8  | 2012-08-15 20:18:53 -0600 |
| 10 | 2012-08-15 20:18:53 -0600 |
| 11 | 2012-08-15 20:18:53 -0600 |
| 12 | 2012-08-15 20:18:53 -0600 |
| 13 | 2012-08-15 20:18:53 -0600 |
| 14 | 2012-08-15 20:18:53 -0600 |
| 15 | 2012-08-15 20:18:53 -0600 |
| 16 | 2012-08-15 20:18:53 -0600 |
| 17 | 2012-08-15 20:18:53 -0600 |
| 18 | 2012-08-15 20:18:53 -0600 |
| 19 | 2012-08-15 20:18:53 -0600 |
| 20 | 2012-08-15 20:18:53 -0600 |
| 21 | 2012-08-15 20:18:53 -0600 |
| 9  | 2012-08-16 18:58:16 -0600 |
| 22 | 2012-08-16 18:59:28 -0600 |
| 5  | 2012-08-16 21:49:14 -0600 |
+----+---------------------------+

I'm sure it is something simple... Any help you could provide would be appreciated.

I've tried these to no avail:

Post.all(:order => "updated_at DESC")
Post.all(:order => "updated_at DESC").limit(1)

Upvotes: 1

Views: 214

Answers (2)

ronalchn
ronalchn

Reputation: 12335

Well, the SQL is definitely not correct.

SELECT id, updated_at FROM "posts" ORDER BY published_at DESC, updated_at DESC

should be

SELECT id, updated_at FROM "posts" ORDER BY updated_at DESC

Edit:

Yes, the default scope means that it will order by both published_at and updated_at. If you want to keep the default scope in the model, but not use the scope for this query, you can use:

Post.unscoped.all(:select => [:id, :updated_at], :order => "updated_at DESC")

Upvotes: 1

Brandon
Brandon

Reputation: 3130

Think I have it figured out.... I have a default scope on "created_at" which I think is screwing me up.

Upvotes: 1

Related Questions