Notes posted by eeby

Avoiding duplicate results when you do a join

When you use the :joins option you can get multiple instances of the same item. For example, say you want every User who owns one or more Lists. With the code below, if a user owns 5 lists, the User will show up five times in the results:

users = User.find(:all, 
  :conditions => ['users.id = lists.user_id'], 
  :joins => [:lists], 
  :order => 'users.username')

You can cause each found user to appear only once by using the :select option with “DISTINCT”:

users = User.find(:all, 
  :conditions => ['users.id = lists.user_id'], 
  :joins => [:lists], 
  :select => 'DISTINCT users.*'
  :order => 'users.username')

Example: find by associated table

Say you have tables “authors” and “books” and they have a one-to-many association.

You want authors who have written books with “cooking” in the title…

cookbook_authors = Author.find(:all, 
  :conditions => ['books.title LIKE ?', '%cooking%'], 
  :joins => [:books], 
  :order => 'authors.last_name' )

For many-to-many associations, it’s a similar pattern. Say you have tables “people” and “organizations” with a many-to-many association through the join table “organization_memberships”.

Ski Club members whose first name starts with “a”…

ski_club_members = Person.find(:all, 
  :conditions => ['first_name LIKE ? AND organizations.name = ?', 
    'a%', 'Ski Club'], 
  :joins => [:organizations], 
  :order => 'people.last_name' )