I want to sort by two columns, one is a DateTime (updated_at), and the other is a Decimal (Price)
I would like to be able to sort first by updated_at, then, if multiple items occur on the same day, sort by Price.
Add a comment
|
8 Answers
In Rails 4 you can do something similar to:
Model.order(foo: :asc, bar: :desc)
foo and bar are columns in the db.
answered Oct 18, 2013 at 8:45
Christian Fazzini
19.7k22 gold badges118 silver badges222 bronze badges
Comments
Assuming you're using MySQL,
Model.all(:order => 'DATE(updated_at), price')
Note the distinction from the other answers. The updated_at column will be a full timestamp, so if you want to sort based on the day it was updated, you need to use a function to get just the date part from the timestamp. In MySQL, that is DATE().
answered Aug 27, 2010 at 20:22
Daniel Vandersluis
94.7k23 gold badges174 silver badges158 bronze badges
2 Comments
Jo Liss
To stop joins from randomly breaking with "ambiguous column" messages, you should use the following more robust version:
:order => ["DATE(#{table_name}.updated_at)", :price] (Note that :price is a symbol.)
grant
I had to escape my order column with
order like so: Model.all(:order => "day asc, `order` asc")Thing.find(:all, :order => "updated_at desc, price asc")
will do the trick.
Update:
Thing.all.order("updated_at DESC, price ASC")
is the Rails 3 way to go. (Thanks @cpursley)
4 Comments
Abdo
Thank you @Erik - I know this answer is old so here's my warning for those seeing it now: This method has been deprecated starting Rails 3.2. Use Thing.all instead =)
cpursley
Correct, this worked well for me:
Thing.all.order("updated_at DESC, price ASC")
Nick
Your update was helpful for me when I needed to lowercase the sorted values:
Thing.order("LOWER(name), number").
therealrodk
The update here enabled me to order on an association after ordering on a direct attribute. Thanks!
Active Record Query Interface lets you specify as many attributes as you want to order your query:
models = Model.order(:date, :hour, price: :desc)
or if you want to get more specific (thanks @zw963 ):
models = Model.order(price: :desc, date: :desc, price: :asc)
Bonus: After the first query, you can chain other queries:
models = models.where('date >= :date', date: Time.current.to_date)
3 Comments
Tass
I know this is an old post, but I'd personally change
model to models so that a person would know it's pluralized. Just a suggestion.
zw963
I think should edit a more specially example: e.g. models = Model.order({price: :desc}, {date: :desc}, {price: asc})
nayiaw
if anyone pasted this and saw undefined method error, there's a small typo here. It should be
:asc instead of asc: Model.order({price: :desc}, {date: :desc}, {price: :asc}) Actually there are many ways to do it using Active Record. One that has not been mentioned above would be (in various formats, all valid):
Model.order(foo: :asc).order(:bar => :desc).order(:etc)
Maybe it's more verbose, but personally I find it easier to manage. SQL gets produced in one step only:
SELECT "models".* FROM "models" ORDER BY "models"."etc" ASC, "models"."bar" DESC, "models"."foo" ASC
Thusly, for the original question:
Model.order(:updated_at).order(:price)
You need not declare data type, ActiveRecord does this smoothly, and so does your DB Engine
3 Comments
bkunzi01
This works great when using two columns from two different tables like: Member.includes(:voter).order("town_club asc").order("voters.last_name asc")
Qaz
Does the SQL you posted correspond to
Model.order(foo: :asc).order(:bar => :desc).order(:etc)? The order of the order clauses in the SQL is the opposite of what I get when I run .to_sql on that.
Ruby Racer
@Qaz this is a long time ago. I think I probably need to correct this one, I'd never noticed. I'll double check later. Thanks.
you can do only separating with commas like this
.where(your query).order("name desc, status asc")
Comments
Model.all(:order => 'updated_at, price')
Comments
None of these worked for me! After exactly 2 days of looking top and bottom over the internet, I found a solution!!
lets say you have many columns in the products table including: special_price and msrp. These are the two columns we are trying to sort with.
Okay, First in your Model add this line:
named_scope :sorted_by_special_price_asc_msrp_asc, { :order => 'special_price asc,msrp asc' }
Second, in the Product Controller, add where you need to perform the search:
@search = Product.sorted_by_special_price_asc_msrp_asc.search(search_params)
answered Aug 11, 2016 at 18:54
Max Alexander Hanna
3,9031 gold badge32 silver badges41 bronze badges
Comments
