Rails Active Query Order by Multiple Values in Specific Order

Rails Active query order by multiple values in specific order?

A portable solution would be to use a CASE statement as an inlined map in your ORDER BY:

query.order(%q(
  case role
  when 'CAD' then 1
  when 'CM'  then 2
  when 'CA'  then 3
  end
))

Keep in mind that you can ORDER BY any expression you want and a CASE certainly is an expression in SQL.

Newer versions of Rails will want you to use Arel.sql rather than a raw string:

query.order(
  Arel.sql(
    %q(
      case role
      when 'CAD' then 1
      when 'CM'  then 2
      when 'CA'  then 3
      end
    )
  )
)

And if the list is dynamic, you can build a CASE expression:

array = %w[CAD CM CA]
q     = connection.method(:quote) # Or ApplicationRecord.connection.method(:quote)
cases = array.each_with_index.map { |e, i| "when #{q[e]} then #{i}" }
query.order(Arel.sql("case role #{cases.join(' ')} end"))

All the string manipulation is a bit ugly but it is perfectly safe and you'd usually hide it in a scope.

Rails activerecord where in get results in specific order

There're several ways.
Easiest database-independent way is to sort in ruby after fetch:

order = ids.each_with_index.to_h
results.sort_by{|item| order[item.id] }

note that that requires fetching whole result into memory and returns an array, which may have some implications depending on your app

In Mysql you can use order by field(id, ...):

Model.where(...).order("field(id, #{ids.join(',')})")`

In postgres 9.5+ - order by array_position(array[...], id):

Model.where(...).order("array_position(array[#{ids.join(',')}], id)")`

RAILS Active record search with multiple ordering -- out of order

<% @report_results.order('user_id').order('created_at DESC').each do |report| %>

Not sure why this works, I thought the last order command would overwrite the previous, but it seems like it retains it.

Items now show up grouped by user_id, in descending chronological order.

Ruby on Rails: how do I sort with two columns using ActiveRecord?

Assuming you're using MySQL,

Model.all(:order => 'DATE(updated_at), price')

Note the distinction from the other answers. The updated_at column will be a full timestamp, so if you want to sort based on the day it was updated, you need to use a function to get just the date part from the timestamp. In MySQL, that is DATE().

Rails how to sort records from database in a specific order?

For a SQL solution, check mu's answer. Using Ruby:

articles = Article.where(id: ids).index_by(&:id).values_at(*ids) +
  Article.where.not(id: ids)

Of course, a SQL solution is nicer if you need a Relation and not an Array.

How to sort activerecord query by specific prority

Order can be any SQL code. You can use a CASE statement to map your values to values that naturally sort in the correct order.

Assignment.order("
    CASE
      WHEN priority = 'best' THEN '1'
      WHEN priority = 'good' THEN '2'
      WHEN priority = 'bad' THEN '3'
    END")

Even better, you could move this logic to the model so that it's easier to call from controllers:

class Assignment < ActiveRecord::Base
  ...
  def self.priority_order
    order("
        CASE
          WHEN priority = 'best' THEN '1'
          WHEN priority = 'good' THEN '2'
          WHEN priority = 'bad' THEN '3'
        END")
  end
end

Then you can just call Assignment.priority_order to get your sorted records.

If this column is sortable in the view, add a parameter to the method for direction:

def self.priority_order(direction = "ASC")
  # Prevent injection by making sure the direction is either ASC or DESC
  direction = "ASC" unless direction.upcase.match(/\ADESC\Z/)
  order("
      CASE
        WHEN priority = 'best' THEN '1'
        WHEN priority = 'good' THEN '2'
        WHEN priority = 'bad' THEN '3'
      END #{direction}")
end

Then, you would call Assignment.priority_order(params[:direction]) to pass in the sorting from the controller.

How can I order multiple ActiveRecords in rails by time?

For your specific samples, where conditions are multiplied (and), you can use ActiveRecord scopes - here

They are made for the purpose of naming query conditions that you nicely combine in the code. Add scopes to your model, i.e. classes which inherit from ActiveRecord::Base

class Activity < ActiveRecord::Base
  scope :ordered, -> { order(created_at: :desc) }
  scope :commenting, -> { where(key: 'commenting') }
  scope :limit, -> { limit(20) }
..

And then to combine scopes (and condition) you just chanin

Activity.ordered.commenting.limit...

Meanwhile, there is a default_scope for something you include by default (e.g. order by)

For or condition (smt or smt) it's more complicated - you seems to use Arel.table (which is inside ActiveRecord) - see here for example

Order and sort_by difference in Ruby on Rails ActiveRecord

sort_by is executed in Ruby, so if you have a nil value, things will break in a way similar to this:

[3, nil, 1].sort
#=> ArgumentError: comparison of Fixnum with nil failed

order is executed by your RDBMS, which generally will do fine with NULL values. You can even specify where you want to put the NULL VALUES, by adding NULL FIRST (usually the default) or NULL LAST to your ORDER BY clause?

How To Order ActiveRecord Relation Based on Column Of Last Record Of Associated Object

I have changed the column 'case_type' instead of 'type' because we can not use column type in our table.

To sort the data by case type of last record of client cases please try this.

For ASC

Client.includes(:cases).sort_by{|m| m.cases.last.case_type}

For DESC

 Client.includes(:cases).sort_by{|m| m.cases.last.case_type}.reverse

Rails where condition and order

So it looks like given a user ID, you want a list of companies sorted by the created_at date of the bookmarks that join Users and Companies.

Am I correct in assuming that Users and Companies share a many-to-many relationship through Bookmarks?

If that's the case, the following "join" could work for you!

Company.joins(:bookmarks).where('bookmarks.user_id = ?', 7).order('bookmarks.created_at')

Of course, in your code, this could be generalized to grab companies for any user!

user_id = 42
companies = Company.joins(:bookmarks).where('bookmarks.user_id = ?', user_id).order('bookmarks.created_at')

ActiveRecord "joins" reference

---

Related Topics