Active Record results to be ordered in specific way-CodePudding

I have Job table with column job_type, job_type can be either top, hot or normal. Now when I list all jobs I want top jobs to be shown first, followed by hot jobs and at last normal jobs. I would also like to sort the jobs in descending order(created_at: :desc). I have following scopes

class Job < ApplicationRecord
  scope :hot_jobs, -> { where(job_type: 'hot') }
  scope :top_jobs, -> { where(job_type: 'top') }
  scope :normal_jobs, -> { where(job_type: 'normal') }
end

What I tried.

In controller I tried something like this

@jobs = Job.hot_jobs.order(created_at: :desc).or(Job.top_jobs.order(created_at: :desc)).or(Job.normal_jobs.order(created_at: :desc))

but the above code is not working. Above code result is same as Job.all.order(created_at: :desc)

So how can I list jobs in following order Top Jobs followed by Hot Jobs followed by Normal Jobs. I can do some hack if I use array but I don't want to convert ActiveRecord#Relations to Array because that will create problem with will_paginate and I can't use includes to prevent N 1 query

I am using Rails 6.0.4.1

Update

I changed job_type column to integer and not I am able to do

@jobs = Job.active.order(job_type: :asc, created_at: :desc).limit(48)

this solved by problem.

CodePudding user response：

Just use a case statement in your order.

The trick in Rails 6 is to wrap it in Arel.sql, or you get a "non-attribute argument" error

@jobs = Job.all.order(
        Arel.sql(
          "case when job_type = 'top' then 1 
           when job_type = 'hot' then 2
           else 3 end asc"), created_at: :desc)

if you have a default order scope on job, just change order to reorder