CODEWITHSUNDEEP
ruby-on-railssorting
Rubioli
Rubioli

Reputation: 680

Ruby on Rails - Order records by created_at and sort_by by boolean

I'm trying to loop over my posts and order('created_at DESC') and sort by pinned (which is a boolean).

How is it possible to order records by created_at and sort with pinned so pinned posts come on top.

I've tried with the following, but the sorting gets massed up and is no longer ordered by created_at or if I try to add the sort_by at the end, the pagination would stop work.

posts = Post.active.order('created_at DESC').page(params[:page]).per(25)
@posts = posts.sort_by { |post| [post.pinned ? 0 : 1, post] }

I have also tried the following with no luck:

@posts = Post.active.order('created_at DESC').page(params[:page]).per(25)
// In my view
- @posts.sort_by { |post| [post.pinned ? 0 : 1, post] }.each do |post|

This will produce an error for the pagination

posts = Post.active.order('created_at DESC').sort_by { |post| [post.pinned ? 0 : 1, post] }.page(params[:page]).per(25)

Any tips are appreciated!

Upvotes: 0

Views: 1509

Answers (2)

Joel Blum
Joel Blum

Reputation: 7878

Max's answer is the way to go, the only thing missing in his comment was wrapping it in Arel.sql

posts = Post.active.order(Arel.sql("CASE WHEN pinned THEN 0 ELSE 1 END"), "created_at DESC"))

Upvotes: 3

max
max

Reputation: 101811

As an alternative you can construct the query with Arel::Nodes::Case instead of a string:

class Post < ApplicationRecord
  def self.pinned_on_top
    kase = Arel::Nodes::Case.new(arel_table[:pinned])
    order(
      kase.when(1).then(0).else(1)
    )
  end
end

# Given the following
Post.create(pinned: true)   # id: 1
Post.create(pinned: false)  # id: 2
Post.create(pinned: true)   # id: 3

Post.pinned_on_top.order(created_at: :desc).ids # 3, 1, 2

The key advantage is portability.

Upvotes: 2

Related Questions