# Sql query to fetch

**URL:** <https://www.ruby-forum.com/t/sql-query-to-fetch/129710>\
**Category:** Rails\
**Created:** [February 20, 2008, 9:57pm UTC](https://www.ruby-forum.com/t/sql-query-to-fetch/129710 "2008-02-20T21:57:44Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\
**Post date:** [February 20, 2008, 9:57pm UTC](https://www.ruby-forum.com/t/sql-query-to-fetch/129710/1 "2008-02-20T21:57:44Z")

</div>

Hello,  
I have has\_and\_belongs\_to\_many relationship between ‘people’ and  
‘relations’  
What i want to do is to select all people from the people\_relations  
table who has multiple relation\_ids.

for example: What will be the query if the search is to be made to find  
all people who have relation\_ids[270,271,272] (which means people who  
have relation\_ids as 270 and 271 and 272)

Thank you.

---

<div class="post-metadata">

**Author:** ![ankit2584](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/51bf81/32.png) [@ankit2584](https://www.ruby-forum.com/u/ankit2584)\
**Post date:** [February 21, 2008, 7:17pm UTC](https://www.ruby-forum.com/t/sql-query-to-fetch/129710/2 "2008-02-21T19:17:37Z")

</div>

I’d define a view in the database for my people (in fact, I generally  
have a view defined for every model - this lets the database do the data  
marshalling efficiently for counts and joins - index and show work off  
views; new, edit, create, update are all off base models).

If the view is defined something like this:

CREATE OR REPLACE VIEW `peopleview` AS SELECT  
`p`.`id`,  
`p`.`name`,  
(SELECT COUNT(0) AS `COUNT(*)` FROM `people_relations` `r` WHERE  
(`p`.`id`=`r`.`person_id`)) AS `rel_count`  
FROM `people` `p`;

and you create a model named peopleview, a controller and the index and  
show views, then in the controller it is simple things like:

@peopleviews = Peopleview.find(:all) for the generic stuff  
@peopleviews = Peopleview.find(:all, :conditions =\> [“rel\_count\>1”]) for  
those multiply-related folks…

I have one example from my app…  
Team has id, team\_name, and team\_lead (a user id)  
User has id, user\_name  
Membership has team\_id and user\_id

Having a database view based on team lets my rails view ‘index’ show:  
from teams: id (the team id, of dubious worth)  
from teams: the team\_name  
from teams: team\_lead (also an id - not very useful)  
from users: team\_lead\_name (user\_name from user)  
from memberships: the membership count for the team.

The database does the joins and the counts. Since these ‘manufactured’  
fields are in the view, I can also use them to select and sort by. My  
teamviews\_controller simply has

cond = get\_filter (looks at session for filter spec)  
order = get\_order (looks at session and parms for order spec)  
@teamviews = Teamview.find(:all, :conditions =\> cond, :order =\> order)