Rails :order by date in Postgres returning incorrect order

Question

I have a model called Story that I'm trying to order by the created_at date. Since I've hosted my app on Heroku, which uses Postgresql, I have the following in my controller:

@stories = Story.find( :all, :order => "DATE(created_at) DESC" , :limit => 11)

I would expect this to give the first 11 of my stories, ordered by the creation date, with the newest story first.

Unfortunately, this doesn't work. Most of the stories return ordered correctly, but the first two are flipped. That is, the latest story appears second in the list.

Why would this be? I have a sneaky suspicion that my results aren't ordered at all or are being ordered on the wrong column (maybe id?) and that until now it just happened to be ordered like I expected when displayed on my index page. How can I get it to order as I expect it to?

In case anyone cares, the index view is simply displaying the stories, in order. That is (HAML):

- @stories.each do |story|
  = render :partial => "event", :locals => { :event => story }

EDIT

I am suspicious that the created_at is a datetime column and the DATE(...) function disregards the time portion. So it returns the elements created on the same date in a random order. Since the first two stories were created on the same day, but several hours apart, which would explain why they seem to be 'reversed'. If this is the case, what would be the correct syntax to order by both date and time?

Also note that by default in PostgreSQL "null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise." SQLite sorts null values as if they are smaller by default. That could be your problem if you're moving from one to the other and you wind up here (like me). postgresql.org/docs/8.3/static/queries-order.html#QUERIES-ORDER — Travis Hohl
– Travis Hohl, Commented May 30, 2018 at 1:04

gylaz · Accepted Answer · 2018-06-20 16:14:15Z

23

I believe you want:

@stories = Story.find(:all, :order => "created_at DESC" , :limit => 11)

Update for Rails 3+:

@stories = Story.order(created_at: :desc).limit(11)

edited Jun 20, 2018 at 16:14

answered Oct 6, 2012 at 23:47

gylaz

13.7k9 gold badges55 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Max Dunn · Accepted Answer · 2012-10-07 04:16:36Z

5

If you are using Rails 3, I would also encourage you to use the cool new query syntax which would be:

@stories = Story.order('created_at DESC').limit(11)

See Active Record Query Interface for more information.

answered Oct 7, 2012 at 4:16

Max Dunn

1,2449 silver badges13 bronze badges

1 Comment

Roddy of the Frozen Peas Over a year ago

I tried using .limit(11) and Rails gave me an error message about unrecognized syntax.

Collectives™ on Stack Overflow

Rails :order by date in Postgres returning incorrect order

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related