Sql query to fetch

Rails
1

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.

2

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)