ok, now i know this isn''t a purely rails problem but if you could help me out that''d be great. coming from a SQL Server background switching over to mysql hasn''t been that much fuss but i''m getting problems with computed fields. in my head this should work, select team_name, COUNT(team_id) as member_count from members where member_count = 2 group by team_name now here this should basically group the members together and calculate the members within each team, then only show the teams who have 2 members. however when i run this it tells me, undefined field ''member_count'' ...can i not do a where clause on a computed field?, if so how can i get around this? appreciate any pointers you can give me? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2008-Feb-03 19:40 UTC
Re: mysql query, where members = 2, undefined field?
On 3 Feb 2008, at 18:50, John Griffiths wrote:> > ok, now i know this isn''t a purely rails problem but if you could help > me out that''d be great. > > coming from a SQL Server background switching over to mysql hasn''t > been > that much fuss but i''m getting problems with computed fields. > > in my head this should work, > > select team_name, COUNT(team_id) as member_count > from members > where member_count = 2 > group by team_name> now here this should basically group the members together and > calculate > the members within each team, then only show the teams who have 2 > members. >Conditions on aggregate values need to be in a having clause ie select ... from ... group by ... having member_count = 2 (see http://dev.mysql.com/doc/refman/5.0/en/select.html) Fred> however when i run this it tells me, > > undefined field ''member_count'' > > ...can i not do a where clause on a computed field?, if so how can i > get > around this? > > appreciate any pointers you can give me? > -- > Posted via http://www.ruby-forum.com/. > > --~--~---------~--~----~------------~-------~--~----~ > You received this message because you are subscribed to the Google > Groups "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org > For more options, visit this group at http://groups.google.c--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Mark Reginald James
2008-Feb-03 19:42 UTC
Re: mysql query, where members = 2, undefined field?
John Griffiths wrote:> coming from a SQL Server background switching over to mysql hasn''t been > that much fuss but i''m getting problems with computed fields. > > in my head this should work, > > select team_name, COUNT(team_id) as member_count > from members > where member_count = 2 > group by team_name > > now here this should basically group the members together and calculate > the members within each team, then only show the teams who have 2 > members. > > however when i run this it tells me, > > undefined field ''member_count'' > > ...can i not do a where clause on a computed field?, if so how can i get > around this? > > appreciate any pointers you can give me?MySQL doesn''t support use of group aggregates in where clauses. You instead have to use a "having" clause after the group clause. ActiveRecord doesn''t currently support a :having option to find. I''ve been using a monkey patch that enables it, but you can always use find_by_sql. -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Thanks Mark, yep I''m using find_by_sql for a couple of my more intense search routines; shame i can''t use ActiveRecord instead. I''ll look at ''having'' and see if that works, thanks for this btw. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
If you have counter_cache set on your has_many :members: has_many :members, :counter_cache => true And then have a member_count field in your teams table you should be able to go Team.find_all_by_member_count(2) to get all of them. -- Ryan Bigg http://www.frozenplague.net Feel free to add me to MSN and/or GTalk as this email. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
nice! can i also include some custom sql to choose particular teams with find_by_sql or something, with this ? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---