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

moin,

i stumbled upon inconsistency in activerecord.
tried used combinations of values in 2 columns of large table. first idea:

select distinct col1, col2 table 

imagine rails app organizes meals model , each meal has_many :noodles each noodle has attributes (and hence db table columns) color , shape. goal number of present combinations of color , shape single meal.

since ar not provide "distinct" method used

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

and got (in rails console stdout) 6 line output of 8 noodle objects (respectively string representations). but:

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

in fact my_meal contains 1606 noodles. if convert the relation array , size of or use .all.count result correct.

so question is, why ar output 8 objects count db lines?

a similar problem seems mentioned here no answer given.

thanks , best regards, tim

okay, tadman pushing me in right direction.

i digged deeper (especially in log files) , found little bit weird.

the problem caused number of selected columns. if 1 selects 1 column , counts result

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

activerecord creates following sql statement:

select count(distinct color) count_id "noodles" ("noodles".meal_id = 295) 

in case 1 selects 2 or more columns , applies count it

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

activerecord forgets select clause , creates:

select count(*) count_id "noodles" ("noodles".meal_id = 295) 

this may right, since (sql's) count allows 1 or less columns parameters. add group before count , fine:

my_meal.noodles.select("distinct color, shape").group("color, shape").count  select count(*) count_all, color, shape color_shape "noodles" ("noodles".meal_id = 295) group color, shape 

apart as color_shape exact expected. but... 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 (apart order depends on db) identical result , return value of

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

conclusion:
pointed out here there still gap between relations (may mathematical or arel relations) , activerecord::relations.
can see advantages of pressing result in patterns of model possible (at least in context of rails app).
real relations not result of combination of several operations result of concatenation of operations. in general chainability of activerecord::relations great thing there design decisions cannot follow.
if can't depend on assurance each action returns new relation work with, looses of inuitional appeal.

as solution of problem, use above-mentioned group solution , kind of dirty workaround count operation:

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

this compresses results acceptable minimum before pulling them out of database , creating expensive objects count them. alternatively 1 use handwritten sql query, why have rails , not use it, huh? ;-)

thanks help,
tim