Rails where using array and sorted by the same as the array

Question

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.

Use a name in and order by, or name in and sort in Ruby. — Dave Newton
– Dave Newton, Commented Dec 15, 2014 at 14:02
Try User.where(name: ["foo", "bar", "baz"]) and then sort items like in default array - stackoverflow.com/questions/4283295/…, stackoverflow.com/questions/11961685/… — Sergey Sokolov
– Sergey Sokolov, Commented Dec 15, 2014 at 14:10

fivedigit · Accepted Answer · 2014-12-15 14:37:09Z

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)

answered Dec 15, 2014 at 14:37

fivedigit

18.7k6 gold badges59 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

monkbroc · Accepted Answer · 2014-12-15 14:52:42Z

There are 2 ways of doing this.

Sort in Ruby as fivedigit mentionned.
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)

Collectives™ on Stack Overflow

Rails where using array and sorted by the same as the array

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related