Topic: Joining a table twice

I followed the tutorial here (http://jrhicks.net/96) to do a many to many relationship between two tables and it worked great.  Now I'm attempting to take it one step further and have two many to many relationships with the same two tables.

I have a Game session table with a has and belongs to many relationship with a users table. That's working fine thanks to the tutorial, but I don't just want to know who played in that board game session, I want to record who won. And some games allow more than one person to win, so I can't just have a gsessions.winner field. I want to join up with the users table for a second time to record the winner ids. I'm already using gsessions_users as a lookup table for the first half of this, so I don't know what to do now. I created a table "winners" with columns gsession_id and user_id, but now I'm at a loss. A user has to be a player if they're going to be a winner, so I could add a column winner (boolean) to my lookup table instead of adding a second look up table.

If I were writing these queries by hand I think I would use a table alias to access the user table twice, but I'm not sure how to do that in RoR.  I've read about has_many :through, but I don't know if that's what I need.  Any idea?

Re: Joining a table twice

Let's say the join table between the Users and the Games table is Paticipations (you can rename). This has a user_id, game_id, and a winner boolean column. You can try doing something like this (completely untested and I have no idea if it will work).

class Game < ActiveRecord::Base
  has_many :participations
  has_many :players, :through => :participations, :source => :user
  has_many :winners, :through => :participations, :source => :user, :conditions => 'particiaptions.winner = TRUE'
end

I wish I could test this out, but I'm not at a machine with Rails at the moment. Hopefully it works...

Railscasts - Free Ruby on Rails Screencasts

Re: Joining a table twice

Thanks for getting back to me so quickly.

I'm trying this out, but I'm not sure how to use this association in the controller.  Here's some background code:

class Gsession < ActiveRecord::Base
  belongs_to :game
  belongs_to :gamenight
  has_many :players, :through => :participations, :source => :user
  has_many :winners, :through => :participations, :source => :user, :conditions => 'participations.winner = true'
end

class GsessionsController < ApplicationController
  ...
  def edit
    @gsession = Gsession.find(params[:id])
    @games = Game.find_all
    @users = User.find_all
  end

  def update
    @gsession = Gsession.find(params[:id])
    @gsession.players = User.find(@params[:user_ids])
    if @gsession.update_attributes(params[:gsession])
      flash[:notice] = 'Gsession was successfully updated.'
      redirect_to :action => 'show', :id => @gsession
    else
      render :action => 'edit'
    end
  end
...
end


<!--[form:gsession]-->
<p><label>Players:</label><br/>
  <% for user in @users %>
    <label for="<%= user.id %>">
    <input type="checkbox" id="<%= user.id %>" name="user_ids[]" value="<%= user.id %>">
    <%=h user.firstname %> <%=h user.lastname %></label> <br />
  <% end %>

I'm just trying to get the first half of this working for now.  When I edit a gsession I see a list of users, with checkboxes.  When I submit I get this error:  undefined method `players='

I'm sure it's coming from the second line of my update action, but I don't know what I should be using there.  I just tried to make it look like the code from the many to many tutorial.  What's the proper way to access that relationship that we set up?  Do I need to create a model for participations?  If so, what would it look like?

Thanks,

Danny

Last edited by personman (2006-08-27 12:33:16)

Re: Joining a table twice

personman wrote:

What's the proper way to access that relationship that we set up?  Do I need to create a model for participations?  If so, what would it look like?

Yeah, the problem is there are two ways to set up a many-to-many relationship in rails. You can use the has_and_belongs_to_many call which doesn't require a separate joining model and uses some naming magic for the joining table. With this method you cannot easily add other attributes to the join table.

The other way is to use has_many :through which offers a bit more flexibility because the joining table is its own model (yeah, you need to create a Participation model/class) and you can very easily add more attributes to the join (such as the winner boolean). The downside is you lose some helper methods which allow you to easily create the relationship through the checkboxes (there is no players= helper method for example). Creating a form can be a bit difficult for adding players with this join style. It is often done through AJAX. I'll try to write up some code tomorrow if I have time.

Railscasts - Free Ruby on Rails Screencasts

Re: Joining a table twice

Thank you.  If I can get over this hurdle I know they'll be a lot of possiblities opened up to me.  I'm doing some reading and it looks like I would even want to make the controller for Participations, too.  Then I would use the normal Create action in that controller (?).  That idea is coming from here:
http://ddj.com/blog/lightlangblog/archi … tor_h.html

If you get a chance to steer me in the right direction I'll be very grateful.  If not then I'll keep hammering away until I make something work.  Thanks.

Re: Joining a table twice

Sounds like you're on the right path. A participations controller is the way I would do it - this fits into a RESTful design. When you add a player to a game session, you are creating a participation.

The user interface can get a little tricky though, and I think it is what a lot of people struggle with regarding RESTful designs. For now, what I would do is leave the "players" part off of the game session creation form. After the game session has been created, I would loop through the participations (players) and add a link to add a player (create a participation).

<h3>Players</h3>
<ul>
<% for participation in @gsession.participations %>
  <li>
    <%=h participation.user.name %>
    <% if participation.winner? %>
      <strong>Winner!</strong>
    <% end %>
  </li>
<% end %>
</ul>
<%= link_to 'add player', :controller => 'participations', :action => 'new', :gsession_id => @gsession %>

You can then create a new action in the participations controller for adding a participation.

Once you get that done, you can Ajaxify this creation process so the user doesn't have to go to a separate page every time they want to add a player/mark it as a winner.

Railscasts - Free Ruby on Rails Screencasts

Re: Joining a table twice

I have it working now.  Thanks so much for your help.  Creating a model and controller for participations also makes it possible to add a score for that player.  Right now I have to go back and forth between different pages to create participations, set winners, etc., but a little ajax can fix that.  Thanks for helping me get past this obstacle.