Count of child model in Model.find

Rails
#1

Hi,

Im trying to get all the users information along with the total number
of orders they have placed. I have used the following below but keep
getting messages like you should include “users.email” in group clause
etc etc

User.find(:all, :select => “users.*, count(orders.id) as orders_count”,
:joins => “left outer join orders on orders.user_id = users.id”, :group
=> “users.id”)

Can you actually do this with activerecord?

JB

#2

Hello JB,

I have tested your query and it works correctly. Perhaps provide the
error trace and the list might be able to help you further.

Cheers,
Nicholas

On Aug 7, 9:19 am, John B. [email protected]

#3

On Aug 7, 2009, at 9:19 AM, John B. wrote:

users.id", :group
=> “users.id”)

Can you actually do this with activerecord?

JB

Sounds like you might want a counter_cache option on the
belongs_to :user in your Order model

http://www.railsbrain.com/api/rails-2.3.2/doc/index.html?a=M001887&name=belongs_to

-Rob

Rob B. http://agileconsultingllc.com
[email protected]

#4

Nicholas H. wrote:

Hello JB,

I have tested your query and it works correctly. Perhaps provide the
error trace and the list might be able to help you further.

Cheers,
Nicholas

On Aug 7, 9:19�am, John B. [email protected]

Counter cache field is not really an option to be honest,should be able
to get this in a query i would have thought.

Im on rails 2.3 and postgres version 8.3

error trace below:

e[4;35;1mUser Load (0.0ms)e[0m e[0mRuntimeError: ERROR C42803
Mcolumn “users.email” must appear in the GROUP BY clause or be used in
an aggregate function F.\src\backend\parser\parse_agg.c L330
Rcheck_ungrouped_columns_walker: SELECT users.*, count(orders.id) as
orders_count FROM “users” left outer join orders on orders.user_id =
users.id GROUP BY users.ide[0m

JB

#5

Ah, to clarify, I tested it on MySQL. Maybe try testing the query
against Postgres directly and then see what rails is generating to
compare. Try passing the query directly through rails. It appears to
be an error thrown by Postgres.

Cheers,
Nicholas

On Fri, Aug 7, 2009 at 11:28 AM, John

#6

John B. wrote:

Nicholas H. wrote:

Hello JB,

I have tested your query and it works correctly. Perhaps provide the
error trace and the list might be able to help you further.

NOT TESTED…

Order.count(:joins => “left outer join orders on orders.user_id =
users.id”,
:group => “users.id”)

look at active record aggregate query support such as count()

hth

ilan

#7

On Aug 7, 4:28 pm, John B. [email protected]
wrote:

Nicholas H. wrote:

Rcheck_ungrouped_columns_walker: SELECT users.*, count(orders.id) as
orders_count FROM “users” left outer join orders on orders.user_id =
users.id GROUP BY users.id [0m

This is not Rails’ fault. This is postgres being stricter with you
than more lenient databases; see
http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html
in particular

“In the second query, we could not have written SELECT * FROM test1
GROUP BY x, because there is no single value for the column y that
could be associated with each group.”

which is basically what you are doing
Fred

#8

Frederick C. wrote:

On Aug 7, 4:28�pm, John B. [email protected]
wrote:

Nicholas H. wrote:

Rcheck_ungrouped_columns_walker: SELECT users.*, count(orders.id) as
orders_count FROM “users” left outer join orders on orders.user_id =
users.id GROUP BY users.id [0m

This is not Rails’ fault. This is postgres being stricter with you
than more lenient databases; see
http://www.postgresql.org/docs/8.3/static/queries-table-expressions.html
in particular

“In the second query, we could not have written SELECT * FROM test1
GROUP BY x, because there is no single value for the column y that
could be associated with each group.”

which is basically what you are doing
Fred

Ok thanks, that explains it.

JB