Mysql query, where members = 2, undefined field?

Rails
1

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?

2

On 3 Feb 2008, at 18:50, John G. wrote:

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

3

John G. wrote:

now here this should basically group the members together and calculate
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.

4

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.

5

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 B.

Feel free to add me to MSN and/or GTalk as this email.

6

nice!

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