CODEWITHSUNDEEP
ruby-on-rails
Mano
Mano

Reputation: 987

Rails sort particular value at the top

build_histories (name, status, updated_at)

foo1, Queued, 2017-01-08 13:46:59
foo2, Failed, 2017-01-02 13:46:59
foo3, Success,2017-01-03 13:46:59
foo4, Queued, 2017-01-04 13:46:59
foo5, Failed, 2017-01-05 13:46:59
foo6, Success,2017-01-09 13:46:59

I tried this and it returns 

BuildHistory.joins(app_build: :app).where("build_histories.bundle like ?","%#{params}%").order("CASE WHEN status = 'Queued' THEN 1 ELSE 2 END,  status", "updated_at DESC")

foo1, Queued, 2017-01-08 13:46:59
foo4, Queued, 2017-01-04 13:46:59 
foo2, Failed, 2017-01-02 13:46:59
foo5, Failed, 2017-01-05 13:46:59
foo3, Success,2017-01-03 13:46:59
foo6, Success,2017-01-09 13:46:59

But I want to sort it as "Queued" always at the top and after that "Failed/Success" should be there by updated_at desc order

Example

foo1, Queued, 2017-01-08 13:46:59
foo4, Queued, 2017-01-04 13:46:59
foo6, Success,2017-01-09 13:46:59
foo5, Failed, 2017-01-05 13:46:59
foo3, Success,2017-01-03 13:46:59 
foo2, Failed, 2017-01-02 13:46:59

For reference: I tried based on this

Tried with the following

BuildHistory.joins(app_build: :app).where("build_histories.bundle like ?","%#{params}%").select("*, CASE WHEN build_histories.status = 'Queued' THEN 1 ELSE 2 END as tmp_order").order("tmp_order, build_histories.updated_at desc")

I am able to get the result but in status column I am getting status = 1 or 2 instead of "Queued/Success/Failure"

Kindly Help

Upvotes: 1

Views: 541

Answers (2)

Salil
Salil

Reputation: 47482

If you are using MySql you can try following order by field

.order("FIELD(status, 'Queued'), updated_at DESC")

For postgres 

.order("status = 'Queued' DESC, updated_at DESC")

Upvotes: 5

Karan Shah
Karan Shah

Reputation: 1324

Try

BuildHistory.order("CASE WHEN status = 'Queued' THEN 1 ELSE 2 END", "updated_at DESC")

Note: I've not checked the code myself, but I think it should work.

Upvotes: 1

Related Questions