2

I'm attempting to lookup a list of stores using .where but I'm also trying to keep them sorted as the same array of ids.

i.e

  ids = ["4", "15", "10", "20", "1"]
  stores = Store.published.where(id: ids)

It looks like stores is being returned in ascending order of the id so like [{id: 20}, {id: 15} {id: 10}, {id: 4}, {id: 1}]. I'd like to keep the returned stores ordered in the same way that ids is ordered. Also note the ids in each store are ints whereas the ids in the ids array are strings.

Doolan
  • 1,293
  • 1
  • 13
  • 28
  • 2
    Does this answer your question? [Sorting ActiveRecord Records by Array](https://stackoverflow.com/questions/48901150/sorting-activerecord-records-by-array) – Emu Aug 26 '21 at 02:21
  • @Emu unfortunately using the solution in the tagged post throws a deprecation warning so I'd like to avoid that. – Doolan Aug 26 '21 at 17:32

3 Answers3

4

Because the numbers are the IDs of the records you can simply use:

ids = ["4", "15", "10", "20", "1"]
stores = Store.find(ids)

This works because find accepts a list of IDs too and when a list is provided then find will return the records in the same order as in the list.

And in Ruby on Rails 7.0 you will be able to use where in combination with in_order_of which has the benefit of not raising an error if a record is not found and that you can order by other columns and their values too:

ids = ["4", "15", "10", "20", "1"]
stores = Store.where(id: ids).in_order_of(:id, ids)
spickermann
  • 100,941
  • 9
  • 101
  • 131
  • If any of the ids doesn't exist then find will throw an error – Aniket Tiwari Aug 26 '21 at 13:32
  • Yeah the problem I had with .find() was it would just throw an error like @AniketShivamTiwari mentioned. The new way in Rails 7.0 looks pretty nice tho. – Doolan Aug 26 '21 at 17:09
  • I found a solution that could possibly work but I can't seem to get it working for my data set https://stackoverflow.com/questions/37990997/how-to-sort-one-array-based-on-another-array-using-ruby/37991249#37991249. I'm not too worried about how I'm querying the ids; more so concerned about how to sort them. – Doolan Aug 26 '21 at 17:41
0

When doing a where(id: ids) query the database is simply running a query limiting the results to those ID's. The database won't return the results based on the ordering of the ids you give it, but you can explicitly order database results by id (or any other attribute) with where(id: ids).order('id desc') to return in decreasing order by id. Another option you can use ActiveRecord#find with multiple ids and it will return the results in the order you request them:

Store.published.find(ids)

However where will silently ignore any id's that are not found, whereas find will fail if any id passed in is missing.

melcher
  • 1,543
  • 9
  • 15
0

Figured it out. I wanted to stick with using .where() so it wouldn't throw an error if the id didn't exist. This solution worked for me...

sorted_stores = stores.sort_by { |store| ids.index(store[:id]) }
Doolan
  • 1,293
  • 1
  • 13
  • 28