按ActiveRecord中的特定ID排序 [英] Sort by specific ids in ActiveRecord

问题描述

我继承了另一个程序员的Rails3项目，并且从总体上来说我还是个新手。他的查询似乎按特定ID排序。有人可以解释这在实际SQL中如何解决吗？我认为这段代码正在杀死数据库，并随后杀死了Rails。我试图在记录器中输出它，但是即使将config设置为：debug，也似乎无法输出实际的SQL。在此处大量搜索（在SO上）并没有清楚说明该查询的外观。代码如下：

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

据我所知，最后一行将使用ORDER BY id = 1，id = 3，...对产品表创建查询等等，这对我来说没有多大意义。

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.

推荐答案

快速分析正在发生的事情，因为它将帮助您了解替换后的处理方法查询。

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}

此行将生成

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

并返回 Demo 对象的数组，并按 count（*）排序组中的行，其中只有 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.

下一步

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

生成映射到<$格式的product_id集合c $ c> id = x 。 IE：如果先前的结果返回10个结果，且product_ids为1..10，则 sort_product_ids 现在等于 [ id = 1， id = 2， id = 3， id = 4， id = 5， id = 6， id = 7， id = 8， id = 9， id = 10]

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"]

最后，

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

选择所有产品，其中可见列为 true ，其 id 在 product_ids 数组中（如我们先前所发现的，实际上是 Demo 对象的数组，而不是整数-这可能导致查询失败）。然后，它要求SQL按照 sort_product_ids （以字符串 id = 1，id = 2，发送给字符串）的形式对结果列表进行排序。 id = 10 而不是数组 [ id = 1， id = 2，... id = 10] ）。

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"]).

更多信息，请访问：
http://guides.rubyonrails.org/active_record_querying.html
http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html

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

这篇关于按ActiveRecord中的特定ID排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！