# sql query to fetch

**URL:** <https://discuss.rubyonrails.org/t/sql-query-to-fetch/25213>\
**Category:** rubyonrails-talk\
**Created:** [February 20, 2008, 8:57pm UTC](https://discuss.rubyonrails.org/t/sql-query-to-fetch/25213 "2008-02-20T20:57:44Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [February 20, 2008, 8:57pm UTC](https://discuss.rubyonrails.org/t/sql-query-to-fetch/25213/1 "2008-02-20T20: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:** ![11175](https://avatars.discourse-cdn.com/v4/letter/1/8edcca/32.png) [@11175](https://discuss.rubyonrails.org/u/11175)\
**Post date:** [February 21, 2008, 6:17pm UTC](https://discuss.rubyonrails.org/t/sql-query-to-fetch/25213/2 "2008-02-21T18: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 &nbsp;&nbsp;`p`.`id`, &nbsp;&nbsp;`p`.`name`, &nbsp;&nbsp;(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)