0

it's a pretty complex question to explain using words, so I gonna use code:

Let's imagine the following scenario:

User.create name: "bar"
User.create name: "foo"
User.create name: "baz"

I want to perform the following query:

User.where(name: ["foo", "bar", "baz"])

User has no default_scope

I want to retrieve these 3 records in the following order:

<User name:foo>, <User name:bar>, <User name:baz>

But, as you may know, rails will return the records in the same order as they were created (Actually I do believe it is the database behavior, rails is just a proxy). Like the next snippet:

<User name:bar>, <User name:foo>, <User name:baz>

Anyway, Does anyone know if there is a clean way to solve it?

I could use an each and perform the query but it'll end up in an N+1 query and I want to avoid it.

Jonathan Duarte
  • 753
  • 5
  • 9
  • Use a `name in` and `order by`, or `name in` and sort in Ruby. – Dave Newton Dec 15 '14 at 14:02
  • Try User.where(name: ["foo", "bar", "baz"]) and then sort items like in default array - http://stackoverflow.com/questions/4283295/how-to-sort-an-array-in-ruby-to-a-particular-order, http://stackoverflow.com/questions/11961685/sort-an-array-according-to-the-elements-of-another-array – Sergey Sokolov Dec 15 '14 at 14:10

2 Answers2

0

You could fetch all the records from the database at once, and then sort them by the sequence of names in the array:

names = %w[ foo bar baz ]
users = User.where(name: names)
sorted_users = users.sort_by { |user| names.index(user.name) }

But if you need to get the records in insertion order, you could also get away with just ordering them by the created_at timestamp, if present in your table:

 users = User.order(created_at: :asc)
fivedigit
  • 18,464
  • 6
  • 54
  • 58
0

There are 2 ways of doing this.

  1. Sort in Ruby as fivedigit mentionned.

  2. Use a custom ORDER BY SQL condition. Do this if you need to keep the query as an ActiveRecord::Relation, for example if you need to further filter it.

To do this, map each value in order to an integer.

Simple case:

User.where(:name => ["foo", "bar", "baz"]).order("CASE name WHEN 'foo' THEN 1 WHEN 'bar' THEN 2 WHEN 'baz' THEN 3 ELSE 4 END")

Automatic mapping of query values to integers:

field = "name"
values = ["foo", "bar", "baz"]
order_when = []
values.each_with_index { |value, index| order_when << "WHEN #{User.sanitize(value)} THEN #{index}" }
order_query = "CASE #{field} #{order_when.join(" ")} ELSE #{values.length} END"
User.where(field => values).order(order_query)
monkbroc
  • 808
  • 9
  • 13