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 -- Posted via http://www.ruby-forum.com/.
On Aug 7, 2009, at 9:19 AM, John Butler wrote:> 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 > --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 Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org
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 Butler <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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 > -- > Posted viahttp://www.ruby-forum.com/.
Nicholas Henry 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 Butler <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>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 JB -- Posted via http://www.ruby-forum.com/.
John Butler wrote:> Nicholas Henry 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 -- Posted via http://www.ruby-forum.com/.
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 Butler<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Nicholas Henry 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 Butler <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > > 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 > > > JB > > -- > Posted via http://www.ruby-forum.com/. > > > >
On Aug 7, 4:28 pm, John Butler <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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 [0mThis 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> > JB > > -- > Posted viahttp://www.ruby-forum.com/.
Frederick Cheung wrote:> On Aug 7, 4:28�pm, John Butler <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> > wrote: >> Nicholas Henry 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 > FredOk thanks, that explains it. JB -- Posted via http://www.ruby-forum.com/.