Sort articles depending on the users role that created it

Question

I'm trying to create a search result using ransack.

I have the results already done.

The users can create articles and each user has a role.

1. Regular
2. Privileged
3. Merchant

These roles are in a table called UserRole

I want to display the search results prioritizing the Merchants then the Privileged and finally the Regular.

Articles Controller:

def index
  @users = User.all
  @roles = UserRole.all
  @q = Article.ransack(params[:q])

  # check if no search is made, it will show a blank page with search options only
  if params[:q].blank?
    @q = Article.none.ransack # so you have a ransack search
  else
    @q = Article.ransack params[:q]
  end

  # display results from search
  @articles = @q.result.paginate(page: params[:page], :per_page => 10).order('created_at DESC')
end

index.html.erb

<% @articles.each do |article| %>
  ...
<% end %>

users schema

create_table "users", force: :cascade do |t|
  t.string   "email",                  default: "",    null: false
  t.string   "encrypted_password",     default: "",    null: false
  t.string   "reset_password_token"
  t.datetime "reset_password_sent_at"
  t.datetime "remember_created_at"
  t.integer  "sign_in_count",          default: 0,     null: false
  t.datetime "current_sign_in_at"
  t.datetime "last_sign_in_at"
  t.inet     "current_sign_in_ip"
  t.inet     "last_sign_in_ip"
  t.string   "confirmation_token"
  t.datetime "confirmed_at"
  t.datetime "confirmation_sent_at"
  t.string   "unconfirmed_email"
  t.datetime "created_at",                             null: false
  t.datetime "updated_at",                             null: false
  t.string   "first_name"
  t.string   "last_name"
  t.string   "phone"
  t.boolean  "admin",                  default: false
  t.integer  "user_role_id",           default: 1
end

user_role schema

create_table "user_roles", force: :cascade do |t|
  t.string   "role_name"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

Question

How do I display the search results prioritizing the Merchants then the Privileged and finally the Regular?

edit

article schema

create_table "articles", force: :cascade do |t|
  t.string   "name"
  t.float    "price"
  t.string   "description"
  t.datetime "created_at",                 null: false
  t.datetime "updated_at",                 null: false
  t.integer  "user_id"
  t.integer  "category_id"
end

almost solved

changed

-    @articles = @q.result.paginate(page: params[:page], :per_page => 10).order('created_at desc')

to

+    @articles = @q.result.paginate(page: params[:page], :per_page => 10).joins(:user).order('users.user_role_id desc')

Now it is displaying the users who have role 3 Merchant first and when they create a new article the new one goes to the top as well but if a 1 Regular user creates an article it goes below the 2 Privileged and 3 Merchant users who have created an article.

But now when a new regular user registers, it will put the new articles below the old ones...

SOLVED

@articles = @q.result.paginate(page: params[:page], :per_page => 10).joins(:user).order('users.user_role_id desc', 'created_at desc')

('users.user_role_id desc', 'created_at desc')

Answer 1

One way of doing it can be using an order by with field function.

@q.result.paginate(page: params[:page], :per_page => 10).joins(:user).order('FIELD(user_role_id,1,2,3) desc')

where 3 is Merchant role id , 2 is Privileged and 1 is Regular. The problem with this approach is that you are hard coding the Ids. This approach can be extended in several ways this is just a starting point.

PS: I am not familiar with ransack and not sure if the code above would work fine with it. The whole point is to illustrate the use of Field()