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