SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to join four MySQL database tables together?

    I am having a lot of trouble trying to figure out how to join four tables together (you can see the design below). Can someone give me some tips/pointers/suggestions as to how to do this?

    I need to display a list of teams then after the team name I need to display a the leader names then beneath that the members. Example:

    Team 1
    Location: NY

    Leaders
    John Doe
    Mary Jane

    Members
    Jack Black
    Joe Schmoe

    people
    --id
    --name
    --email

    teams_leaders
    --team_id
    --member_id

    teams_members
    --team_id
    --member_id

    teams
    --id
    --name
    --location

    Any help would be greatly appreciated. I've been reading my RoR books and am lost!
    John Saunders

  2. #2
    SitePoint Guru silver trophy Luke Redpath's Avatar
    Join Date
    Mar 2003
    Location
    London
    Posts
    794
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about this model:

    Code:
    people:
    - id
    - name
    - email
    
    teams:
    - id
    - name
    - location
    
    memberships:
    - id
    - person_id
    - team_id
    - leader (boolean)
    
    class Person
      has_many :memberships
      has_many :teams, :through => :memberships
    
      def member_of_team?(team)
        self.teams.include? team
      end
    end
    
    class Team
      has_many :memberships
      has_many :people, :through => :memberships
    
      def leaders
        self.memberships.find(:all, :conditions => ['leader = ?', true]).collect(&:person)
      end
    
      def normal_members
        self.memberships.find(:all, :conditions => ['leader = ?', false]).collect(&:person)
      end
    
      def create_leader(person)
        if person.member_of_team?(self)
          membership_for(person).update_attribute(:leader, true)
        else
          self.memberships.create(:person => person, :leader => true)
        end
      end
    
      def membership_for(person)
        self.memberships.find(:first, :conditions => ['person_id = ?', person.id])
      end
    end
    
    class Membership
      belongs_to :team
      belongs_to :person
    end
    
    # some view
    <% @teams.each do |team| %>
      <h1>Team: <%= team.name %>
    
      <h2>Leaders</h2>
      <ul id="team_leaders">
      <%= team.leaders.each do |leader| %>
      <li><%= leader.name %></li>
      <% end %>
      </ul>
    
      <h2>Members</h2>
      <ul id="team_members">
      <%= team.normal_members.each do |member| %>
      <li><%= member.name %></li>
      <% end %>
      </ul>
    <% end %>

  3. #3
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thats how I do it too


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •