mysql query, where members = 2, undefined field?

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?

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

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.

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.

nice!

can i also include some custom sql to choose particular teams with find_by_sql or something, with this ?