Rails 3: Difference between Relation.count and Relation.all.count

Question

Moin

I came across inconsistency in ActiveRecord.
I tried to get all combinations of values ​​used 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 every has_many :noodles meal has_many :noodles Each noodles has attributes (and therefore the columns of the DB table) color and shape . My goal is to get the number of all existing color and shape combinations in one meal.

Since AR does not provide an “excellent” method, I used

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

and received (in the rails console stdout) six-line output of 8 Noodle objects (respectively their String representation). But:

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

In fact, my_meal contains 1606 noodles. If I convert Relation to an array and get its size or use .all.count , the result will be correct.

So my question is: why does AR output 8 objects, but counts all DB rows?

A similar problem is mentioned here , but no answer is provided.

Thanks and best regards, Tim

Answer 1

OK, thanks tadman for taking me in the right direction.

I went a little deeper (especially in the log files), and what I found is a bit strange.

The problem was caused by the number of selected columns. If you select only one column and count 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) 

If one selects two or more columns and applies count to it

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

ActiveRecord forgets this select clause and creates:

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

This may be correct, because (SQL) count allows only one or fewer columns as parameters. Add group before count and everything will be 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 

Other than that, AS color_shape is 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 is a strange return value (in addition to the order, which depends on the database), identical to the result and return value

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

Output:
As indicated here, there is still a gap between relations (maybe they are mathematical or relations) and ActiveRecord :: Relations.
I see the benefits of clicking on the result in the model templates as often as possible (at least in the context of the Rails application).
However, real relationships are not the result of a combination of several operations, but are the result of the concatenation of these operations. In general, the ActiveRecord :: Relations chain is a great thing, but there are some design decisions that I cannot follow. If you cannot depend on the belief that each action returns a new attitude for the job, it loses most of its appeal.

As for solving my problem, I will use the above 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 for counting them. Alternatively, you can use a handwritten SQL query, but why not use Rails ?; -)

Thanks for your help,
Tim