CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
Will Kessler
Will Kessler

Reputation: 565

Sort by specific ids in ActiveRecord

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.

Upvotes: 4

Views: 4716

Answers (3)

Weihang Jian
Weihang Jian

Reputation: 8755

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

Upvotes: 1

Momer
Momer

Reputation: 3247

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

Upvotes: 3

tihom
tihom

Reputation: 8003

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(',')})" )

Upvotes: 3

Related Questions