CODEWITHSUNDEEP
mysqlruby-on-railsrubyrails-activerecord
Prakash Laxkar
Prakash Laxkar

Reputation: 854

How to make order apply only to non-null records yet get all records?

I want to order the result based on the condition.

For example

@redeemables = @business.redeemables.order(expiry_date: :desc)

I want all redeemables with expiry_date => nil first than redeemables order by desc

In other means, redeemables which have exipry_date < Date.current should come in bottom in the order.

How I can do this? Thanks

Upvotes: 4

Views: 90

Answers (4)

Hosang Jeon
Hosang Jeon

Reputation: 1423

The easiest way to get what you want is: 

@redeemables = @business.redeemables.order("-expiry_date asc")
  • This will give you exactly reverse result of (expiry_date: asc)
  • It means descending ordered by expiry_date with null first

Upvotes: 2

Laurence
Laurence

Reputation: 36

Try something like this:

@redeemables = @business.redeemables.sort_by {|r| "#{r.expiry_date.blank?} #{r.desc}" }

Whats its basically doing is sorting by a concatenated string of whether the expiry date is blank (true/false) and the description. (e.g. false item_description).

If you want to change the sort order of the nil expiry_dates, you can do the following:

@redeemables = @business.redeemables.sort_by {|r| "#{r.expiry_date.present?} #{r.desc}" }

Upvotes: 0

wiesion
wiesion

Reputation: 2455

If you're using PostgreSQL there's the nice NULLS FIRST (vs. NULLS LAST) statement, but there's the standard SQL way of doing this (CASE works basically always), because relation + relation will trigger eager loading, which you probably don't want to happen.

@redeemables = @business.redeemables.order('(CASE WHEN expiry_date IS NULL THEN 1 ELSE 0 END) desc, expiry_date desc')

Upvotes: 2

Abhi
Abhi

Reputation: 4261

You may do like this:

@redeemables_without_expiry = @business.redeemables.where("expiry_date IS NULL")
@redeemables_with_expiry = @business.redeemables.where("expiry_date IS NOT NULL").order(expiry_date: :desc)

@redeemables = @redeemables_without_expiry + @redeemables_with_expiry

Upvotes: 0

Related Questions