轨道3：Relation.count和Relation.all.count的区别 [英] Rails 3: Difference between Relation.count and Relation.all.count

问题描述

莫因，

我偶然在ActiveRecord的一个矛盾。
我试图让价值全部用于组合在一个大表中的两列。 第一个想法：

  SELECT DISTINCT COL1，COL2 FROM表
 

想象一下，一个Rails应用程序，组织饭菜作为一个模型，每餐的has_many：面条 每个面有属性（因此数据库表中的列）颜色和形状。 我的目标是要获得颜色和形状因一点食物。<所有present组合的数量/ P>

由于AR不提供我用了一个独特的方法

  my_meal.noodles.select（截然不同的颜色，形状）
 

和获得（在轨控制台标准输出）的8面​​对象（分别为它们的字符串重新presentations）六线输出。但是：

 ＆GT;＆GT; my_meal.noodles.select（截然不同的颜色，形状）。算
=＆GT; 1606
 

在事实上 my_meal 包含1606面。 如果我转换关系到一个数组中，并得到它的大小，或者使用 .all.count 的结果是正确的。

所以，我的问题是，为什么AR输出8对象，但算上所有的数据库行？

有一个类似的问题似乎这里提及，但没有给出答复。

感谢和问候， 蒂姆·

解决方案

好了，感谢 tadman 推我正确的方向。

我挖更深的有些（尤其是在日志文件中），但是我发现是有点不可思议。

的问题是由选定的列数造成的。如果一个仅选择一列，计数结果

  my_meal.noodles.select（明显的颜色）。算
 

ActiveRecord的创建下列SQL语句：

  SELECT COUNT（不同的颜色）AS count_id从面WHERE（面.meal_id = 295）
 

在情况下，一个选择两个或多个列，适用计数它

  my_meal.noodles.select（截然不同的颜色，形状）。算
 

ActiveRecord的忘掉了SELECT子句，并创建：

  SELECT COUNT（*）作为count_id从面WHERE（面.meal_id = 295）
 

这可能是对的，因为（SQL的） COUNT 只允许一个或更少的列作为参数。添加组在计数和什么是好的：

  my_meal.noodles.select（截然不同的颜色，形状）。组（色，形）。算

SELECT COUNT（*）AS count_all，颜色，形状color_shape从面WHERE（面.meal_id = 295）GROUP BY颜色，形状
 

除了这个 AS color_shape 是准确的什么我的预期。但是...只是它返回的：

 ＆GT;＆GT; my_meal.noodles.select（截然不同的颜色，形状）。组（色，形）。算
=＆GT; {星=＆GT; 309，圈=＆GT; 111，意大利面条=＆GT; 189平方米=＆GT; 194，三角形=＆GT; 179，领结=＆GT; 301，外壳=＆GT; 93，信=＆GT; 230}

＆GT;＆GT; my_meal.noodles.select（截然不同的颜色，形状）。组（色，形）。count.class
=＆GT;的ActiveSupport :: OrderedHash
 

这奇怪的返回值是（除了秩序依赖于DB）与相同的结果，并返回值

  my_meal.noodles.group（形）。算
 

结论：
正如指出这里仍有（关系之间的差距可能他们是数学或AREL关系）和ActiveRecord的::关系。
我可以看到pressing结果在模型的模式的优势，尽可能多地（至少在一个Rails应用程序上下文）。
然而真正的关系是不是的综合作用的结果的若干操作的，但串联这些操作的结果的。 一般的ActiveRecord ::关系的chainability是一个伟大的事情，但也有一些设计决定，我不能跟随。
如果你不能依赖于每一个动作返回一个新的关系，一起工作的保证，它失去了大部分的inuitional吸引力。

对于我的问题的解决方案，我将用上述组解决方案和一些肮脏的解决方法计数操作的：

  my_meal.noodles.select（截然不同的颜色，形状）。组（色，形）。all.count
 

此COM $ P $拉出来的数据库和创建昂贵的对象仅仅指望他们之前psses结果到可接受的最低限度。或者人们可以使用手写的SQL查询，但为什么有Rails和不使用它，是吧？ ; - ）

感谢您的帮助，
蒂姆·

Moin,

i stumbled upon a inconsistency in ActiveRecord.
I tried to get all used combinations of values in two columns of a large table. First idea:

SELECT DISTINCT col1, col2 FROM table

Imagine a rails app that organizes meals as a model and each Meal has_many :noodles Each Noodle has the attributes (and hence the DB table columns) color and shape. My goal is to get the number of all present combinations of color and shape for a single meal.

Since AR does not provide a "distinct" method i used

my_meal.noodles.select("distinct color, shape")

and got (in the rails console stdout) a six line output of 8 Noodle objects (respectively their String representations). But:

>> my_meal.noodles.select("distinct color, shape").count
=> 1606

In fact my_meal contains 1606 noodles. If i convert the the Relation to an array and get the size of it or use .all.count the result is correct.

So my question is, why does AR output 8 Objects but count all the DB lines?

A similar problem seems to be mentioned here but no answer is given.

Thanks and best regards, Tim

解决方案

Okay, thanks to tadman for pushing me in the right direction.

I digged somewhat deeper (especially in the log files) and what i found is a little bit weird.

The problem was caused by the number of selected columns. If one selects only one column and counts the result

my_meal.noodles.select("distinct color").count

ActiveRecord creates the following SQL statement:

SELECT COUNT(distinct color) AS count_id FROM "NOODLES" WHERE ("NOODLES".meal_id = 295)

In case one selects two or more columns and applies count to it

my_meal.noodles.select("distinct color, shape").count

ActiveRecord forgets about that select clause and creates:

SELECT COUNT(*) AS count_id FROM "NOODLES" WHERE ("NOODLES".meal_id = 295)

This may be right, since (SQL's) COUNT allows only one or less columns as parameters. Add a group before the count and anything is fine:

my_meal.noodles.select("distinct color, shape").group("color, shape").count

SELECT COUNT(*) AS count_all, color, shape AS color_shape FROM "NOODLES" WHERE ("NOODLES".meal_id = 295) GROUP BY color, shape

Apart from this AS color_shape it is exact what i expected. BUT... only it returns this:

>> my_meal.noodles.select("distinct color, shape").group("color, shape").count
=> {star=>309, circle=>111, spaghetti=>189, square=>194, triangle=>179, bowtie=>301, shell=>93, letter=>230}

>> my_meal.noodles.select("distinct color, shape").group("color, shape").count.class
=> ActiveSupport::OrderedHash

This weird return value is (apart from order which depends on the DB) identical with the result and return value of

my_meal.noodles.group("shape").count

Conclusion:
As pointed out here there is still a gap between relations (may they be mathematical or arel relations) and ActiveRecord::Relations.
I can see the advantages of pressing the result in the patterns of a model as often as possible (at least in the context of a Rails app).
However real relations are not the result of the combination of several operations but the result of the concatenation of those operations. In general the chainability of ActiveRecord::Relations is a great thing but there are some design decisions i cannot follow.
If you can't depend on the assurance that each action returns a new relation to work with, it looses much of its inuitional appeal.

As for the solution of my problem, i will use the above-mentioned group solution and some kind of dirty workaround for the count operation:

my_meal.noodles.select("distinct color, shape").group("color, shape").all.count

This compresses the results to an acceptable minimum before pulling them out of the database and creating expensive objects just to count them. Alternatively one could use a handwritten SQL query, but why have a Rails and do not use it, huh? ;-)

Thanks for your help,
Tim

这篇关于轨道3：Relation.count和Relation.all.count的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！