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
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]
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]
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
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
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
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
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