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 ?