Geek Answers Handbook

Sort by specific identifiers in ActiveRecord

I have inherited another Rails3 programmer project, and I'm pretty new to rails in general. He received a request that is sorted by specific identifiers. Can someone explain how this happens in real SQL? I think this code kills db and subsequently rails. I tried to output it in the logger, but it seems that it cannot get the actual SQL for output even with the configuration setting: debug. The search hard here (on SO) did not reveal a clear explanation of what this query looks like. The code looks like this:

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 tell, the last line will create a query to the product table with ORDER BY "id = 1, id = 3, ...", etc., which does not make much sense to me. All tips are appreciated.

+4
source share
2 answers

A quick breakdown of what happens as it helps you understand what to do for your replacement request.

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 it returns an array of objects Demosorted by count(*)rows in the group where only the attribute was loaded product_idand is available to you.

Further

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

results in the product_ids set being displayed in the format "id = x". IE: If the previous result returned 10 results, with product_ids in the range of 1.10, is sort_product_idsnow equivalent["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(', '))

Products, visible true, id product_ids (, , Demo, - ). SQL sort_product_ids ( "id = 1, id = 2, ... id = 10" ["id = 1", "id = 2", ... "id = 10"]).

: http://guides.rubyonrails.org/active_record_querying.html http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html

+1

,

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

More articles:


All Articles