Sort by specific ids in ActiveRecord

Question

I have inherited another programmer's Rails3 project, and I'm fairly new to rails overall. He's got a query that appears to sort by specific id's. Can somebody explain how this resolves in actual SQL? I think this code is killing the db and subsequently rails. I've tried to output it in the logger but can't seem to get the actual SQL to output even with config set to :debug. Searching heavily here (on SO) didn't turn up a clear explanation of how this query looks. The code looks like:

options = {
              select: "SUM(1) AS num_demos, product_id ",
              group:  "product_id",                
              order:  "num_demos ASC",
            }
  product_ids = Demo.where("state = 'waitlisted'").find(:all, options).collect{|d| d.product_id}
  sort_product_ids = product_ids.collect{|product_id| "id = #{product_id}"}   
  Product.where(visible: true, id: product_ids).order(sort_product_ids.join(', '))

As far as I can see, the final line will create a query against the product table with an ORDER BY "id = 1, id = 3, ..." etc, which doesn't make a lot of sense to me. All clues appreciated.

use `to_sql` at the end of the query and inspect it to see the SQL generated — tihom, Sep 24 '13 at 20:57
Thanks, got same output from rails console. It looks like this: SELECT "products".* FROM "products" WHERE "products"."visible" = 't' AND "products"."id" IN (166, 105,..etc) ORDER BY id = 166, id = 105, id = 110, id = 99, id = 109, id = 118, id = 108, id = 160,... etc. I've not seen this type of ORDER BY before, and it's pretty cool. — Will Kessler, Sep 25 '13 at 04:11

score 3 · Answer 1 · answered Sep 24 '13 at 21:02

3

To select and sort by a given array of ids you can use this

Product.where(visible: true, id: product_ids)
.order( "field(id,#{product_ids.join(',')})" )

answered Sep 24 '13 at 21:02

tihom

7,923
1
25
29

Do you mean, it will select only the records with the given ids and return them in the order of the ids as provided by product_ids.join(',') ? – Will Kessler Sep 24 '13 at 21:27
it will select the records with the given ids and `visible = true`. Then it will arrange them by the given order. If a `product` is not present in the final result it will just ignore its `id` in the order. – tihom Sep 24 '13 at 21:45
This is exactly what I was looking for. It ordered my Model by a given set of ids. – tolgap Feb 07 '14 at 09:14
2

@tihom This helped but I'm getting: `(PG::SyntaxError: ERROR: syntax error at or near "[" LINE 1: ...,2,3,4,9532,9533,88,89)) ORDER BY FIELD(stage_id, [1, 2, 3, ...` – sscirrus Sep 24 '14 at 16:40
@sscirrus the `order by field` functionality is not available in PG see http://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql – tihom Sep 25 '14 at 02:50
Thanks! you have save time for me :) – Luan D Jan 08 '15 at 08:43

Momer · Accepted Answer · 2013-09-24T21:28:51.460

3

A quick breakdown of what's going on, as it'll help you understand what to do for your replacement query.

options = {
              select: "SUM(1) AS num_demos, product_id ",
              group:  "product_id",                
              order:  "num_demos ASC",
            }
product_ids = Demo.where("state = 'waitlisted'").find(:all, options).collect{|d| d.product_id}

This line will generate

SELECT SUM(1) as num_demos, product_id FROM "demos" WHERE (state = 'waitlisted') GROUP BY product_id

And returns an array of Demo objects, sorted by the count(*) of rows in the group, where only the product_id attribute has been loaded, and is available to you.

Next,

sort_product_ids = product_ids.collect{|product_id| "id = #{product_id}"}

results in a collection of product_ids mapped to the format "id = x". IE: If the previous result returned 10 results, with product_ids ranging from 1..10, sort_product_ids is now equivalent to ["id = 1", "id = 2", "id = 3", "id = 4", "id = 5", "id = 6", "id = 7", "id = 8", "id = 9", "id = 10"]

Finally,

Product.where(visible: true, id: product_ids).order(sort_product_ids.join(', '))

Selects all Products where the column visible is true, and their id is in the array of product_ids (which, as we found out earlier, is actually an array of Demo objects, not integers - this might be causing the query to fail). Then, it asks SQL to sort that result list by the sort_product_ids (sent in as a string "id = 1, id = 2, ... id = 10" instead of an array ["id = 1", "id = 2", ... "id = 10"]).

More info available at: http://guides.rubyonrails.org/active_record_querying.html http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html

edited Sep 24 '13 at 21:28

answered Sep 24 '13 at 21:20

Momer

3,158
22
23

thank you for this useful analysis. In my tests in irb, the array sort_product_ids would have entries like "id = 1", "id = 2", etc. When you call join(', ') on that you should get a single string with all those entries thus: "id = 1, id = 2", etc. So does ActiveRecord actually create SQL that says ... ORDER BY ('id = 1, id = 2, ...') ? – Will Kessler Sep 24 '13 at 21:24
Thanks a lot, @Morner. Would like to vote this answer up but I don't have enough reputation... – Will Kessler Sep 25 '13 at 00:08
No problem, did your question get resolved? Select am answer if so – Momer Sep 25 '13 at 02:41
Almost. I finally got rails console working (had to set env vars for DATABASE_URL and RAILS_ENV correctly, for anybody reading this that is struggling to do that), and then ran the above code in the console. It pretty much does what you said, but interestingly, the records are returned in the reverse order from the list created by the join on sort_product_ids. Why would that be? – Will Kessler Sep 25 '13 at 04:07
Not sure, but you can reverse that by appending one of `"ASC"` or `"DESC"` to the `sort_product_ids.join(', ')` string; whichever results in the opposite sorting. – Momer Sep 25 '13 at 04:15
you cna reverse it by using `sort_product_ids.reverse.join(', ')` in `order` – tihom Sep 25 '13 at 19:17

score 1 · Answer 3 · answered Apr 17 '21 at 16:42

If you are using PostgreSQL, consider to use WITH ORDINALITY, it is the fastest way compared to others. See this thread.

To apply this method to Ruby on Rails, for example:

class SpecifiedByIds
  def specified_by_ids(ids)
    joins(
      <<~SQL
        LEFT JOIN unnest(array[#{ids.join(',')}])
        WITH ORDINALITY AS t(id,odr) ON t.id = #{table_name}.id
      SQL
    ).order('t.odr')
  end
end

class MyModel < ActiveRecord::Base
  extend SpecifiedByIds
end

this works with rails 6 and PostgreSQL database – Rajasekhar Bammidi Aug 02 '23 at 16:40 — Rajasekhar Bammidi, Aug 02 '23 at 16:40

Sort by specific ids in ActiveRecord

3 Answers3

Linked