count of child model in Model.find

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

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

Nicholas Henry wrote:

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:

[4;35;1mUser Load (0.0ms) [0m [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.id [0m

This is not Rails' fault. This is postgres being stricter with you than more lenient databases; see PostgreSQL: Documentation: 8.3: Table Expressions 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

Frederick Cheung wrote: