Rails - Jan 2006 - Looking for DB/Model Design Suggestions

Hi,

I''m working on an app - my first Rails - and am looking for some 
suggestions on the best way (or good way) to set up my models / DB. In 
general there are teams and matches; a team will participate in multiple 
matches, and in each match there will be 6 teams - 3 vs. 3.

I''m going to want to be able, for a particular team, pull up all
matches
for that team, to display a scoring summary, etc. I was thinking of two 
tables Team and Match. The team table would have their number and name, 
and other info, and the Match table would have the match number, 6 
fields for the teams involved, a field for the score for the winners and 
one for the losers, and a general comment field.

A tricky bit is I want to be able to have a Match entry page, where the 
user would enter the teams numbers, the final scores and the comments, 
and upon saving, having the appropriate updates so I can do a lookup by 
team to see their matches played.

Any hints? If more info is needed, I''ll be happy to provide.

Thanks!

-- 
Posted via http://www.ruby-forum.com/.

class Team < ActiveRecord::Base
  has_many :competitions
  has_many :matches, :through => :competitions

  def winning_matches
    winnings = Competition.find_by_winner(true)
    winnings.collect { |won| won.match }
  end
end

class Competition < ActiveRecord::Base
  belongs_to :team
  belongs_to :match
end

class Match < ActiveRecord::Base
  has_many :competitions
  has_many :teams, :through => :competitions
end

class InitialSchema < ActiveRecord::Migration
  def self.up
    create_table "team", :force => true do |t|
      t.column "name",          :string, :limit => 100
      t.column "number",        :integer
      # other info...
      t.column "created_at",    :datetime
      t.column "updated_at",    :datetime
    end

    create_table "match", :force => true do |t|
      t.column "number",        :integer
      t.column "winning_score", :integer
      t.column "losing_score",  :integer
      t.column "comment",       :text
      t.column "created_at",    :datetime
      t.column "updated_at",    :datetime
    end

    create_table "competition", :force => true do |t|
      t.column "team_id",       :integer
      t.column "match_id",      :integer
      t.column "winner",        :boolean
      t.column "created_at",    :datetime
      t.column "updated_at",    :datetime
    end
end

----------

Save the migration class as 001_initial_schema.db in /db/migrate/ and run
rake migrate.
Then create 3 separate model files as above. You don''t really have to
use
the :through
but it makes it easier. (You''ll need edge Rails to use :through.)

I''m not entirely sure I understand your domain issues, but creating a
3rd
table, really
just a join table made into a separate "competition" model seems
clean.

team = Team.find_by_name(''Hooligans'')
team.matches  #  Gets an array of Match
team.winning_matches # Returns array of won Matches

Caveat emptor, given that I''ve just typed this in without irb, etc.
-Bill
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/57a98a96/attachment-0001.html

Oops. That''s what happens when you just type code straight.
#winning_matches should have a first line like:
winnings = Competition.find_all_by_team_id_and_winner(self.id, true)

On 1/23/06, Bill Katz <billkatz@gmail.com> wrote:
>
> class Team < ActiveRecord::Base
>   has_many :competitions
>   has_many :matches, :through => :competitions
>
>   def winning_matches
>     winnings = Competition.find_by_winner(true)
>     winnings.collect { |won| won.match }
>   end
> end
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/a3c5c059/attachment.html

Or, really,
winnings = competitions.find_by_winner(true)
Time for sleep :) ...

On 1/23/06, Bill Katz <billkatz@gmail.com> wrote:
>
> Oops. That''s what happens when you just type code straight.
> #winning_matches should have a first line like:
> winnings = Competition.find_all_by_team_id_and_winner(self.id, true)
>
> On 1/23/06, Bill Katz <billkatz@gmail.com> wrote:
> >
> > class Team < ActiveRecord::Base
> >   has_many :competitions
> >   has_many :matches, :through => :competitions
> >
> >   def winning_matches
> >     winnings = Competition.find_by_winner(true)
> >     winnings.collect { |won| won.match }
> >   end
> > end
> >
> >
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/2a738504/attachment.html

Thanks for that info.

I have to now figure out the rest :) (yeah, am a newbie).

More background - there will be matches, composed of two "alliances"
of
three teams (Red and Blue, for discussion sake.) Each team (anywhere 
from 25 to 50 teams) will participate in a series of matches, approx. 8 
- 10.

I was looking at it by trying to limit it to the 6 teams in a match, but 
was getting confused.

I guess in my create method, I''m going to have to pull the 6 teams in 
the match and create the appropriate items. I was thinking of using a 
third table to link, but wasn''t sure how to make the link. I mocked up
a
page to help me visualize what I need to do. It has an entry for a match 
number, 3 field for the "red alliance", 3 for the "blue
alliance", the
"red" score, the "blue" score, and a comment field.

{Not to mention, I''m not sure how to set up edge Rails - I''m
using
Locomotive on OS X. I tried following the items on the wiki page: 
<http://wiki.rubyonrails.com/rails/pages/EdgeRails> - I did the check 
out to the vendor directory, but when I try to do the rake freeze_edge 
command, I get and error:

rake aborted!
Don''t know how to build task ''freeze_edge''
}

Thanks again!

jt

-- 
Posted via http://www.ruby-forum.com/.

If you are worried about restricting the entries to 6, you could add some
validation code via #validate_on_create, etc.
If you need to keep track of which team was "blue" or "red",
that would go
into the competition (join) table.
I skipped the freeze_edge command and use edge rails as a subversion
external in the /vendor directory.
The logic isn''t much tougher if you want to skip ":through"
and edge rails.
-Bill

On 1/23/06, John Tsombakos <johnts@charter.net>
wrote:
>
> Thanks for that info.
>
> I have to now figure out the rest :) (yeah, am a newbie).
>
> More background - there will be matches, composed of two
"alliances" of
> three teams (Red and Blue, for discussion sake.) Each team (anywhere
> from 25 to 50 teams) will participate in a series of matches, approx. 8
> - 10.
>
> I was looking at it by trying to limit it to the 6 teams in a match, but
> was getting confused.
>
> I guess in my create method, I''m going to have to pull the 6 teams
in
> the match and create the appropriate items. I was thinking of using a
> third table to link, but wasn''t sure how to make the link. I
mocked up a
> page to help me visualize what I need to do. It has an entry for a match
> number, 3 field for the "red alliance", 3 for the "blue
alliance", the
> "red" score, the "blue" score, and a comment field.
>
> {Not to mention, I''m not sure how to set up edge Rails -
I''m using
> Locomotive on OS X. I tried following the items on the wiki page:
> <http://wiki.rubyonrails.com/rails/pages/EdgeRails> - I did the check
> out to the vendor directory, but when I try to do the rake freeze_edge
> command, I get and error:
>
> rake aborted!
> Don''t know how to build task ''freeze_edge''
> }
>
> Thanks again!
>
> jt
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060124/d52798ad/attachment.html

Bill Katz wrote:
> If you are worried about restricting the entries to 6, you could add 
> some
> validation code via #validate_on_create, etc.
> If you need to keep track of which team was "blue" or
"red", that would
> go
> into the competition (join) table.
> I skipped the freeze_edge command and use edge rails as a subversion
> external in the /vendor directory.
> The logic isn''t much tougher if you want to skip
":through" and edge
> rails.
> -Bill
Thanks...again.

Since I can''t seem to get edge rails to work (I pulled the subversion
external into the /vendor directory, but it doesn''t look like
Locomotive
is using it), how would I not use the :through?

How, for instance, would I get, given a team, the matches they were in,
along with the teams for each match?

And.. how would I add a new item, given the details of a match? (sorry
for the newbie questions...)

thanks muchly.

jt

-- 
Posted via http://www.ruby-forum.com/.

what does this do ?

:through => :competitions

i am looking to do somethign similar and couldnt follow all your code.

thanks
adam

On 1/24/06, John Tsombakos <johnts@charter.net>
wrote:
> Bill Katz wrote:
> > If you are worried about restricting the entries to 6, you could add
> > some
> > validation code via #validate_on_create, etc.
> > If you need to keep track of which team was "blue" or
"red", that would
> > go
> > into the competition (join) table.
> > I skipped the freeze_edge command and use edge rails as a subversion
> > external in the /vendor directory.
> > The logic isn''t much tougher if you want to skip
":through" and edge
> > rails.
> > -Bill
>
> Thanks...again.
>
> Since I can''t seem to get edge rails to work (I pulled the
subversion
> external into the /vendor directory, but it doesn''t look like
Locomotive
> is using it), how would I not use the :through?
>
> How, for instance, would I get, given a team, the matches they were in,
> along with the teams for each match?
>
> And.. how would I add a new item, given the details of a match? (sorry
> for the newbie questions...)
>
> thanks muchly.
>
> jt
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

Bill Katz wrote:
> class Match < ActiveRecord::Base
>   has_many :competitions
>   has_many :teams, :through => :competitions
> end
Looks like I''m going to have to find a new name for this model... 
"Match" is a reserved word in MySQL - I was getting an error when I 
tried to run the rake migrate command.

Again, still looking for hints on how I would get,  given a team, the 
matches they were in,
along with the teams for each match. And how to get info into the proper 
tables... err and how to not have to use the :through edge rails feature 
;)

Thanks again
jt

-- 
Posted via http://www.ruby-forum.com/.

John Tsombakos wrote:
> Again, still looking for hints on how I would get,  given a team, the 
> matches they were in,
> along with the teams for each match. And how to get info into the proper 
> tables... err and how to not have to use the :through edge rails feature 
> ;)
I''m trying to figure this out.. but am not having much luck.

First thing I found out, I had to name the tables in the migration file: 
"teams", "matches", "competitions". Ok fine, the
database is set up.

I was able to put Edge Rails into the Vendor directory and it does seem 
to work. So I set up 3 new models: Team, Match, and Competition, with 
the above details.

Now, using the console, so I can try to figure out how to do this, I 
tried:

t = Team.new(:number=>99, :name=>"blah")

which created a new team item. I then tried adding a match:

t.matches << Match.new(:number=>2, :winning_score=>100, :comment
=>
"blahblah")

Then I saved it: t.save.

There was a new record in the Teams table, but that''s it - nothing in 
Matches nor Competitions.

Do I have to create each of these items and hook up the foreign id''s 
(team_id, match_id)? That seems like more work than should be necessary. 
I may as well just have one big table and duplicate all the info I 
need...

What am I missing? I really want to use Rails for this little project! 
I''m just getting more and more confused. All of the examples are very 
simple - one simple to many examples.

Help!!

Thanks,

jt

-- 
Posted via http://www.ruby-forum.com/.

John,
Note that you used Match.new not Match.create, so you really haven''t
saved
that Match record.
If you try to get an "id" from a new record, you''ll see that
it''s nil until
you actually issue a save.

Still, the join record "competition" is not automatically created when
using
:through.
You are using :through when you are treating that join table as a separate
model and you''ll be creating those entries explicitly, e.g.
you''ll need to
record whether a team
is a winner or not.

So you have to ways to do this:
1) Old way using HABTM
Team:
has_many :competitions
has_and_belongs_to_many :matches, :join_table => :competitions
(similar for Match)
Then you can do
t.matches << a_match, and it will create the entry in
''competitions''

2) Treat the join table as a separate model and use :through
Team:
has_many :competitions
has_many :matches, :through => :competitions
Then you create competitions which automatically establish the relationship
between a team and match.
You''ll be setting the other fields in competition at the same time,
since
that''s the main reason you treat
that join table as a separate model ---> It''s got data separate from
just
the team/match association.
So here, you do
Competition.create(:team_id => t.id, :match_id => m.id, :winner =>
true)
As soon as you create a competition, the relationship between team and match
is established, which
is really what happens under the habtm.

Anyone have a different viewpoint on :through use?
-Bill



On 1/25/06, John Tsombakos <johnts@charter.net>
wrote:
>
> John Tsombakos wrote:
>
> > Again, still looking for hints on how I would get,  given a team, the
> > matches they were in,
> > along with the teams for each match. And how to get info into the
proper
> > tables... err and how to not have to use the :through edge rails
feature
> > ;)
>
> I''m trying to figure this out.. but am not having much luck.
>
> First thing I found out, I had to name the tables in the migration file:
> "teams", "matches", "competitions". Ok fine,
the database is set up.
>
> I was able to put Edge Rails into the Vendor directory and it does seem
> to work. So I set up 3 new models: Team, Match, and Competition, with
> the above details.
>
> Now, using the console, so I can try to figure out how to do this, I
> tried:
>
> t = Team.new(:number=>99, :name=>"blah")
>
> which created a new team item. I then tried adding a match:
>
> t.matches << Match.new(:number=>2, :winning_score=>100,
:comment =>
> "blahblah")
>
> Then I saved it: t.save.
>
> There was a new record in the Teams table, but that''s it - nothing
in
> Matches nor Competitions.
>
> Do I have to create each of these items and hook up the foreign
id''s
> (team_id, match_id)? That seems like more work than should be necessary.
> I may as well just have one big table and duplicate all the info I
> need...
>
> What am I missing? I really want to use Rails for this little project!
> I''m just getting more and more confused. All of the examples are
very
> simple - one simple to many examples.
>
> Help!!
>
> Thanks,
>
> jt
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060125/488ddb4d/attachment.html

Bill Katz wrote:
> John,
> Note that you used Match.new not Match.create, so you really
haven''t
> saved
> that Match record.
> If you try to get an "id" from a new record, you''ll see
that it''s nil
> until
> you actually issue a save.
Ah, thanks!

And thanks for the additional info. My next question ( ;) ) is - given a 
team number, how would I get a list of all the matches they played, 
along with the teams that were in that match?

And/Or - given a match number, how do I get the list of teams and their 
scores?

thanks again.


-- 
Posted via http://www.ruby-forum.com/.

You need to work through the Agile Web Development book :) If you don''t
have
it, go buy it.


And thanks for the additional info. My next question ( ;) ) is - given
a
> team number, how would I get a list of all the matches they played,
> along with the teams that were in that match?

team_matches = Team.find_by_number(num).matches
team_matches.each do |match|
  puts "Match #{match.number) included the following teams:"
  match.teams.each { |team| puts team.name }
end


And/Or - given a match number, how do I get the list of teams and
their
> scores?

match = Match.find_by_number(num)
puts "Match #{match.number}:"
match.competitions.each do |bout|
  result = bout.winner ? "won with score of #{match.winning_score}" :
"lost
with score of #{match.losing_score}"
  puts "Team (#{team.name}) #{result}"
end

-Bill
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060125/9170a52f/attachment.html

On 1/25/06, Bill Katz <billkatz@gmail.com> wrote:
> You need to work through the Agile Web Development book :) If you
don''t have
> it, go buy it.
I do have it and am reading it :)  Problem is, the book shows how to
do nice, simple relationships.. this is a bit more complex and the
book doesn''t go into it - with examples.
> team_matches = Team.find_by_number(num).matches
> team_matches.each do |match|
>   puts "Match #{match.number) included the following teams:"
>   match.teams.each { |team| puts team.name }
>  end
>
> match = Match.find_by_number(num)
> puts "Match #{match.number}:"
> match.competitions.each do |bout|
>   result = bout.winner ? "won with score of
#{match.winning_score}" : "lost
> with score of #{ match.losing_score}"
>   puts "Team (#{team.name}) #{result}"
> end
>  -Bill
Thanks! Exactly the examples I need. It''s starting to fall into place
:)

I even found a mistake ;)  the next to last line should be:

puts "Team (#{bout.team.name}) #{result}"

Thanks again!!

John Tsombakos wrote:
> Thanks again!!

I''m working on getting this set up , and while I''m
understanding the
structure, I''m having a hard time getting my head around how to design 
the create/update pages - especially update.

Since using :through, I have to manage all of the foreign keys, 
especially in the connecting table - competitions in this case.  If I 
have a page to update a match, where the user can change the teams that 
are associated with that match, I''d have to keep track of what was 
changed and update all sorts of foreign keys manually. I keep hearing 
(and reading in the book) "Rails means less code", I''m
thinking I''m
overlooking something.

The more I get into this, the more complicated the updated and even 
creating is looking. I may end up having one or two simple tables and 
forego all the nice relations.

If someone has a more than simple :has_many using :through (or :habtm) 
example, where you''re updating multiple thing, I''d love to see
it. I''ve
looked at the "tagging" type examples, with a series of checkboxes,
but
that''s not quite like what I need.

Thanks all.

jt

-- 
Posted via http://www.ruby-forum.com/.

> Since using :through, I have to manage all of the foreign keys,
> especially in the connecting table - competitions in this case.  If I
> have a page to update a match, where the user can change the teams that
> are associated with that match, I''d have to keep track of what was
> changed and update all sorts of foreign keys manually.

 You''re making this sound more difficult than it is. Why not make a
form
with two
textboxes for your winning & losing score, and six select drop-downs
arranged
appropriately, three under the winning & three under the losing score.
Each select has a list of all the team names
with the associated value being the team_id. Initialize each select to the
currently set
team for that "competition." One select tag - one competition record.

After the form is submitted, see if the teams have changed. If so, simply
update the
competition record corresponding to the changed select. Replace the old
team_id with
the new team_id. Save. The match_id remains constant.

Either that, or go back to the 2 table system with hardwired fields for the
six teams
in each match. You''ll then have to write some logic to aggregate
searches on
those
six foreign keys.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060127/3e01129c/attachment.html