How do I order database records in rails by most recent?

Question

I want to order all the items in a model Item so it displays the most recent ones first. According to the Rails guide, the following code should work:

 Item.order("created_at DESC")

However, when I type that (or varieties) in the terminal, the most recent item always shows up last, and that's how they show up on my page. How do I efficiently retrieve them with he most recent first? (I'm going to display only some of them at a time on each page.)

Note that my default scope for items is oldest first.

Update: This is the SQL I get:

SELECT "comments".* FROM "comments" ORDER BY comments.created_at ASC, created_at DESC

So I guess I shouldn't use default scopes...

Solution 1

The query you posted is correct

Item.order("created_at DESC")

The only reason why it would not work is if there is anything else conflicting with it. In general, the conflict is represented by a default_scope.

If you have a default scope that overrides your order, you should first unscope the query

Item.unscoped { Item.order("created_at DESC") } 

If you are using default scopes, I strongly encourage you to avoid them. They are very hard to debug and unscope.

There are very few cases where default scopes make sense. You can simply pass the (default) scope at select time in the controller or create a custom method for it.

Solution 2

I realise this is a really old question, but none of the answers contain the solution without writing raw SQL, which is available since Rails 3+:

Item.order(created_at: :desc)

or using the reverse_order method:

Item.order(:created_at).reverse_order

See more at http://guides.rubyonrails.org/active_record_querying.html#ordering and http://guides.rubyonrails.org/active_record_querying.html#reverse-order.

Solution 3

I modified CDub's answer with reverse so it now works:

 Item.order(:created_at).reverse

I'm still not sure why the Rails guide's way doesn't work. Also the above method doesn't work well with pagination.

Solution 4

Item.unscoped.order('created_at DESC') should work.Using reverse might decrease the performance when the number of records increases

View more solutions

Comments

[am-rails]

But I want to do it differently than the default scope in the Item model.

[am-rails]

Though maybe I could change the default scope in the Model Items belong to, though that didn't initially work..

[Nitin Jain]

can you try Item.all.order("created_at DESC")

[Simone Carletti]

This is very inefficient. First you load all the records sorting them by created_at ASC, then you reverse the array in Ruby. Let the database engine perform the ordering.

[am-rails]

Thanks, I didn't realize the default_scope was so hard to over-ride. I guess I'll replace it with named scopes.