# Mysql query, where members = 2, undefined field?

**URL:** <https://www.ruby-forum.com/t/mysql-query-where-members-2-undefined-field/127979>\
**Category:** Rails\
**Created:** [February 3, 2008, 7:50pm UTC](https://www.ruby-forum.com/t/mysql-query-where-members-2-undefined-field/127979 "2008-02-03T19:50:26Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![softf5ajivanet](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/9f8e36/32.png) [@softf5ajivanet](https://www.ruby-forum.com/u/softf5ajivanet)\
**Post date:** [February 3, 2008, 7:50pm UTC](https://www.ruby-forum.com/t/mysql-query-where-members-2-undefined-field/127979/1 "2008-02-03T19:50:26Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![softf5ajivanet](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/9f8e36/32.png) [@softf5ajivanet](https://www.ruby-forum.com/u/softf5ajivanet)\
**Post date:** [February 3, 2008, 8:41pm UTC](https://www.ruby-forum.com/t/mysql-query-where-members-2-undefined-field/127979/2 "2008-02-03T20:41:22Z")

</div>

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](http://dev.mysql.com/doc/refman/5.0/en/select.html))

Fred

---

<div class="post-metadata">

**Author:** ![softf5ajivanet](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/9f8e36/32.png) [@softf5ajivanet](https://www.ruby-forum.com/u/softf5ajivanet)\
**Post date:** [February 3, 2008, 8:43pm UTC](https://www.ruby-forum.com/t/mysql-query-where-members-2-undefined-field/127979/3 "2008-02-03T20:43:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![softf5ajivanet](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/9f8e36/32.png) [@softf5ajivanet](https://www.ruby-forum.com/u/softf5ajivanet)\
**Post date:** [February 4, 2008, 12:59pm UTC](https://www.ruby-forum.com/t/mysql-query-where-members-2-undefined-field/127979/4 "2008-02-04T12:59:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![softf5ajivanet](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/9f8e36/32.png) [@softf5ajivanet](https://www.ruby-forum.com/u/softf5ajivanet)\
**Post date:** [February 4, 2008, 2:11pm UTC](https://www.ruby-forum.com/t/mysql-query-where-members-2-undefined-field/127979/5 "2008-02-04T14:11:36Z")

</div>

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.

> **[frozenplague.net](http://www.frozenplague.net)**
>
> This domain may be for sale!

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

---

<div class="post-metadata">

**Author:** ![softf5ajivanet](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/9f8e36/32.png) [@softf5ajivanet](https://www.ruby-forum.com/u/softf5ajivanet)\
**Post date:** [February 4, 2008, 3:42pm UTC](https://www.ruby-forum.com/t/mysql-query-where-members-2-undefined-field/127979/6 "2008-02-04T15:42:54Z")

</div>

nice!

can i also include some custom sql to choose particular teams with  
find\_by\_sql or something, with this ?