ActiveRecord order by an array

Ruby on Rails
1

Is there a way of ordering by an array?

something like?

arr = [10,14,1,3]
where(id: arr).order(arr)

I know the above doesn’t work, it’s how I would like it to work.

At the moment without the order part, it is finding all the id’s in the array but listing them in ascending order.

Thanks

2

I have just found that I can do this, but it seems a little slow, any other suggestions?

arr = [10,14,1,3]
where(id: arr).order("field(id, #{arr.join(',')})")

Here is a link to what field() does

3

Is there a significance to the array? what does that collection represent?

4

@pedromoreira Yes it’s a list of catalogue id’s that the customer is allowed to see, however the first one is the most important, it’s the one that gets set as the default, but because the where clause is ordering them in ASC in this case it would pick up 1 as the first.

5

How about adding an is_default field to the Catalogue model? Then you can order the result using a query something like order(is_default: :desc, name: asc) so that the default always appears first.

6

Hi Andy,

I can’t really do that, as the order of the catalogues is important :frowning: .

Thanks,

7

Is that information encoded in the database, e.g. a display_order field?

8

Yes it’s in a database as catalog_list

9

How is it stored? A plain string? A postgres array?

10

It’s a string in MySQL DB

11

You could try do the sorting in Ruby rather than SQL. I’ve no idea if this would be faster than the MySQL approach using fields().

For example:

ordered_catalogue_ids = user.catalog_list.split(",").map(&:to_i)
ordered_catalogues = catalogues.where(id: catalogue_ids).sort_by do |catalog_id|
  ordered_catalogue_ids.index(catalog_id)
end
12

Thanks Andy I will give that a go :slight_smile: .

where is ordering coming from?

13

Sorry, I’ve corrected my post.

14

I had this problem recently, and solved it using this suggestion: ruby on rails - ActiveRecord.find(array_of_ids), preserving order - Stack Overflow

15

Just came across a blog post by Justin Weiss that solves just this.

16

@lenart thanks :slight_smile: It looks like using field is the right way to go then.