has_many:through

I just want to make sure I'm doing this properly. I have one table that
acts as a primary key:

Table : teams
team_id
name

All of my other tables have the following criteria:
team_id
stats..
more stats..
even more stats..

The foreign key is team_id. So, I'm trying to understand how to
associate the other tables through the teams table..

I've normalized my tables but I'm just a bit sketchy on how to proceed
from here. Should I use simple keys or should I use something like
has_many or belongs_to in my associations?

When I display a view to another person, I will be displaying the
following information:

Name
stats..
more stats..
even more stats..
stats created by mathematical methods applied to the stats above..

Any tips would be appreciated..

Meanwhile, I will continue reading the rails enterprise book on DB
models..

I understand the foreign id has to have the same association id in the
naming.. for instance,..

class PassingOffense
  belongs_to :team
end

class ScoringOffense
  belongs_to :team
end

class Teams
  has_many :passing_offenses
  has_many :scoring_offenses
end

Correct? Or, should I be doing something different here? Keep in mind
all tables have team_id but only Teams has the team's 'name'.

I just want to make sure I'm doing this properly. I have one table that
acts as a primary key:

No. Tables do not act as primary keys. Fields act as primary keys
for tables.

Table : teams
team_id
name

ActiveRecord expects the primary key for the table to be called simply
"id", not "whatever_id", although you can override this if you want.
In fact, the migrations will automatically create an id field if you
don't specify it.

All of my other tables have the following criteria:
team_id
stats..
more stats..
even more stats..

The foreign key is team_id. So, I'm trying to understand how to
associate the other tables through the teams table..

Probably through the Team model. You know...
@team = Team.find(:some => :conditions)
@team.stats
@team.more_stats

I've normalized my tables but I'm just a bit sketchy on how to proceed
from here. Should I use simple keys or should I use something like
has_many or belongs_to in my associations?

That's not an "or" question. has_many and belongs_to are the way you
tell Rails about your table associations -- it's not smart enough to
infer them from the schema, although I seem to recall the
foreign_key_associations plugin can do that. I've never used that
plugin, though -- I generally would rather specify associations
explicitly.

When I display a view to another person, I will be displaying the
following information:

Name
stats..
more stats..
even more stats..
stats created by mathematical methods applied to the stats above..

Any tips would be appreciated..

See above.

Meanwhile, I will continue reading the rails enterprise book on DB
models..

I suspect it may assume a level of SQL and RDBMS knowledge that you
probably lack at present...

Best,

I understand the foreign id has to have the same association id in the
naming..

By default, yes. If the foreign key field has a different name, you
can specify that.

for instance,..

class PassingOffense
belongs_to :team
end

class ScoringOffense
belongs_to :team
end

class Teams
has_many :passing_offenses
has_many :scoring_offenses
end

Correct?

Almost correct -- class Teams should be class Team (remember, model
class names are always singular in Rails). This will expect a team_id
column in the passing_offenses and scoring_offenses tables, and will
create Team#passing_offenses, Team#scoring_offenses, and other methods
(see Association docs for the full list).

Or, should I be doing something different here? Keep in mind
all tables have team_id but only Teams has the team's 'name'.

No, other than the pluralization of Teams, this looks pretty good.

Best,

You might well find it worth while looking through some of the guides
at guides.rubyonrails.org particularly the one on Active Record
Associations. This will provide some very useful tips on how to go
about organising and accessing the data.

Colin

That was really a great guide. I understand the associations a lot
better now. However, I didn't see any detailed examples on querying in
the associations guide. Based on what I read and looking over my
database tables, I will definitely be using a belongs_to and has_many
association.

So, for instance..

class Team
  has_many :rushing_offenses
end

class RushingOffense
  belongs_to :team
end

Table teams:
id
name

Table rushing_offense:
team_id
statone
stattwo
statthree
compiled_on

The name column in teams has to be unique, meaning there will always
only be 120 teams assigned to 120 ids.

So, if I'm creating both a new record in both tables, I'm using
something similar to this?

@rushing_offense = @team.rushing_offenses.create(:name => 'Florida',
:statone => '143', :stattwo => '173', :statthree => '194',
:compiled_on => Time.now)

The id would automatically be created in the Teams table and because the
foreign key is team_id, that would automatically match when the record
is created? This is the part I'm leery about.

Now the other issue I have is when I do further updating. For instance,
I compiled weekly data. This means that in the rushing_offense table
there will always be 120 records for each week (matching the same number
of teams). I create new records and then view the data based on the
current week, doing something similar by referencing a named_scope:

  named_scope :compiled_this_week, lambda { { :conditions =>
['compiled_on > ? and compiled_on < ?', Time.now.beginning_of_week,
Time.now.end_of_week] } }

So, the question is when I go to create the new week's data, isn't an
error going to be thrown in the teams table because it sees that a
unique team is trying to be created? Or, it will it simply not create a
new team/id in the teams table but go ahead and create the data in the
rushing_offense table?

Again, these are specific questions that I have. I need to ensure that
I'm able to create weekly data and find weekly data. In my past model
it was easy because I just had everything duplicated in every table and
queried by a given table. However, it was an incorrect way of doing
things and the tables were not normalized. Now they are.

Please advise...

That was really a great guide. I understand the associations a lot
better now. However, I didn't see any detailed examples on querying in
the associations guide. Based on what I read and looking over my
database tables, I will definitely be using a belongs_to and has_many
association.

Querying is where the magic of rails comes into play. You can use
this_team = Team.find(id) to pick up a particular team or this_team =
Team.find_by_name( 'some team name'). Then this_team.rushing_offenses
will give you an array of all the rushing offenses for this team.

So, for instance..

class Team
has_many :rushing_offenses
end

class RushingOffense
belongs_to :team
end

Table teams:
id
name

Table rushing_offense:

The table name should be plural - rushing_offenses

team_id
statone
stattwo
statthree
compiled_on

The name column in teams has to be unique, meaning there will always
only be 120 teams assigned to 120 ids.

So, if I'm creating both a new record in both tables, I'm using
something similar to this?

@rushing_offense = @team.rushing_offenses.create(:name => 'Florida',
:statone => '143', :stattwo => '173', :statthree => '194',
:compiled_on => Time.now)

I have not done it in this way as I have generally created things
using the standard methods using form submission. I would suggest you
work through some of the other guides also if you haven't done so
already. Then I suggest you play about a bit to see how it all works.
When things don't seem to be doing what you expect the ruby debugger
can be very useful (I think there is a guide on using ruby-debug).

Also have a look at the guide on testing and start doing tests right
from the the beginning.

Did you build your models, views etc using the scaffold generator so
all the test skeletons have been provided for you?

Colin

I am afraid I am getting out of my depth with what you are describing
below as it is not the sort of application I have experience of. I
think you may need help from others.

Your Team model class should definitely be singular though.

I still think you might be better to work through some of the
tutorials working through what one might describe as a typical
application in order that you pick up the concepts, then adapt these
to your requirements. I suspect you may have a lot of difficulty
leaping in at the deep end with a non-conventional application.

Colin

Well I hate to say I took the easier way but with this scenario I did.
I went back to the older way of doing things with regards to only the
statistics tables.

The way I see it is they are being parsed from another table and so
therefore, they are just table lists for the most part. They are
individual from one another and supply distinct views of a particular
category. Therefore, there really is no association with them other
than them just being viewed as is...

The only table I will be using to work with them is my ratings table and
for that I'm using calculations only. So, I can pull the particular
piece of information I need from each of the tables I have currently and
create calculations which are then built into the ratings view.

For other parts of my site, I will be using normalization and working on
understanding these associations better. Again, like you said... I was
trying to do too much. I can't hurt any of my views doing it the way I
am currently. If I were populating the data inwardly and not relying on
an external source/parser - maybe then I would have to normalize and
change things.

Thanks for all your help though.

I did create a viewer/category/links association using a tutorial I
found online that allowed me to create a resources links table. It did
help me understand association better.

[...]
RushingOffense model houses a scrape method that calls a file called

scraper.rb and parses out the data from the site into an array of
arrays. It then creates the new data, populating the data using
something similar:

    RushingOffense\.create\(:rank =&gt; offensive\_rushing\.rows\[i\]\[0\],
      :name =&gt; offensive\_rushing\.rows\[i\]\[1\],
      :games =&gt; offensive\_rushing\.rows\[i\]\[2\],
      :carries =&gt; offensive\_rushing\.rows\[i\]\[3\],
      :net =&gt; offensive\_rushing\.rows\[i\]\[4\],
      :avg =&gt; offensive\_rushing\.rows\[i\]\[5\],
      :tds =&gt; offensive\_rushing\.rows\[i\]\[6\],
      :ydspg =&gt; offensive\_rushing\.rows\[i\]\[7\],
      :wins =&gt; offensive\_rushing\.rows\[i\]\[8\],
      :losses =&gt; offensive\_rushing\.rows\[i\]\[9\],
      :ties =&gt; offensive\_rushing\.rows\[i\]\[10\],
      :compiled\_on =&gt; Time\.now\)

---begin style-Nazi digression---

Just as an aside: this is rather unidiomatic Ruby. Why didn't you
assign offensive_rushing.rows[i] to a local variable and save some
typing (and possibly some calculation time)?

Worse, the [i] makes me think that you're using a for loop of some
sort. Remember, Ruby is not PHP, and it has different looping
constructs. You want rows.each.

Or you could do something like this:

FIELDS = [:rank, :name, :games, ...]
offensive_rushing.rows.each do |row|
  values = {:compiled_on => Time.now}
  FIELDS.each_with_index do |field, i|
    values[field] = row[i]
  end
  RushingOffense.create values
end

...which is shorter and arguably easier to read.

-----end style-Nazi digression-----

Also note that you're doing a separate DB query for each record
created, which is a terrible idea (queries don't belong inside
loops). Investigate ar-extensions or some other bulk insertion
plugin, or if all else fails, generate a bulk insert statement
directly in SQL.

Now, this has all changed because by normalizing my tables, I moved the
name into the Teams table. I also removed rank (because it wasn't fully
dependent on the primary key). I also removed avg and ydspg because I
could gain these values by doing calculations later on and so they also
were not dependent on the primary key). So, by normalizing my tables it
has created issues for me in terms of how I scrape and populate the
tables.

I'm just not sure how to tie the two tables in when creating a record.

Simple:

team_name = offensive_rushing.rows[i][1] # (or whatever the proper
array subscript is)
team = Team.find_by_name(team_name)

# and then either:
team.rushing_offenses << RushingOffense.create(:whatever =>
'data', :you => 'want')
# or:
RushingOffense.create(:team => team, :other => 'data')

Rails does most of the work for you.

[...]

So, how do I create one single row of data using the example above if
the Teams table is pre-populated with 120 unique IDs and Teams and the
Rushing Offenses table needs to know which team_id it belongs to?

That's how.

When parsing the data, all information is stored in the arrays so the
name of the team is shown. I would imagine that the name of the team
would have to be matched to the team name in the teams column and that
the id would then be passed back to the team_id, or I could be
completely mistaken.

You are completely correct. That's what the Team.find_by_name line
above does.

As I have a very unique parsing mechanism, and I've read a lot of
articles now, some of this just doesn't make sense to me.

Your parsing mechanism is irrelevant. You just need to know what to
do with the data once parsed.

Best,

Bad idea. Proper use of associated tables is so fundamental to
efficient application design -- and so well supported in Rails -- that
it is worth holding up further development until you understand it. I
try not to be dogmatic about many things, but this is one of them.

See my other recent post in this thread for sample ideas. Really,
*learn to use the framework and the database*, don't just kludge your
way around them.

Best,

Hi Marnen,

Well I have multiple copies of my project (one on 1.9.1 with mysql,
another on 1.8.6 with postgres, and another on 1.8.6 with mysql). I
also keep a full backup of each with subversion and hard-copied.

So, I played around with the parser object first, using your
suggestions. I came up with:

  FIELDS = [:rank, :name, :games, :carries, :net, :avg, :tds, :ydspg,
:wins, :losses, :ties]
  def scrape(url,type,name,deep)
    # scraping/parsing our data
    offensive_rushing = Scraper.new(url,type,name,deep)
    offensive_rushing.scrape_data
    offensive_rushing.clean_celldata
    # checking to see if the data exists in our table for the current
week
    @rushing_offenses = RushingOffense.compiled_this_week.find(:all)
    if @rushing_offenses == [] # means we have an empty array so no data
exists
      puts "Updating Offensive Rushing Statistics for the following
teams:"
      offensive_rushing.rows.each do |row|
        values = {:compiled_on => Time.now}
        FIELDS.each_with_index do |field, i|
          values[field] = row[i]
        end
        # List our teams
        puts row[1]
        RushingOffense.create values
      end
    end
    if @rushing_offenses != [] # means the current week's data is not
empty so don't update data
      puts "Current Week's Data Is Already Populated!"
    end
  end

And the parser object works fine with your suggestions. Yes, you were
correct about the for loop. This bit of code you provided helped me
understand how arrays work in a much deeper detail. I've been using
ruby's .each with normal arrays but was having difficulty with array of
arrays. Thanks for shedding the light.

I'll try your suggestions with my backed up project and mull over things
a bit. It is very difficult for me to slow down though mate. I'm
patient but that's not necessarily the same thing. I have a lot of
ideas and I like to play with those ideas. Rails to me is like a giant
candy store and I'm starving and got locked inside.

Marnen,

I managed to get my scraper/parser working as intended with utilizing
both the teams table and the stats table it is updating. I did the
following:

  FIELDS = [:rank, :team_id, :games, :carries, :net, :avg, :tds, :ydspg,
:wins, :losses, :ties]
  def scrape(url,type,name,deep)
    offensive_rushing = Scraper.new(url,type,name,deep)
    offensive_rushing.scrape_data
    offensive_rushing.clean_celldata
    @rushing_offenses = RushingOffense.compiled_this_week.find(:all)
    if @rushing_offenses == [] # means we have an empty array so no data
exists
      puts "Updating Offensive Rushing Statistics for the following
teams:"
      offensive_rushing.rows.each do |row|
        team = Team.find_by_name(row[1])
        values = {:compiled_on => Time.now}
        FIELDS.each_with_index do |field, i|
          if row[i] == row[1]
            values[field] = team.id
          else
            values[field] = row[i]
          end
        end
        # List each team we update
        puts team.name + " ID = " + team.id.to_s
        RushingOffense.create values
      end
    else
      # data is already populated for the week so don't update
      puts "Current Week's Data Is Already Populated!"
    end
  end

Basically, since the parser houses the name of the team in row[1] I
simply substituted :name for :team_id in my constant. I then supplied
the team variable per your helpful tidbits and then checked in the each
method to see if row[i] was the same as row[1] and if so to substitute
the values for team.id.

It lines up correctly and appears to function fine. Do you see any
possible errors with my syntax? I just want to make sure I've done this
part right.

So far my teams table is populated with the correct teams and
rushing_offense is populated correctly as well. I'm going to work on
the find..

I also have a question on Constants. In this case, it appears the
FIELDS constant should probably be named to something associated with
the rushing_offense model as it is just going to be used by this model.
But, if I had a constant that needed to be usable by all models would I
put that in environment.rb?

Thanks again mate. Wish me luck on the find queries.

Hrm, okay my tables are setup correctly now and populated so I'm trying
to do a find, but I must be doing it wrong..

class Team < ActiveRecord::Base
  has_many :rushing_offenses
end

class RushingOffense < ActiveRecord::Base
  belongs_to :team
end

I've tried the following in the rails console:

@team = Team.find(:all)
@rushing_offenses = @team.rushing_offenses.find(:all)

and get an undefined method rushing_offenses error

Again, I'm probably doing this wrong. Let me break it apart the way I
believe it's reading from a code standpoint:

@team = Team.find(:all)
--> Is finding all the data in the teams table

@rushing_offenses = @team.rushing_offenses.find(:all)
--> assign an instance variable @rushing_offenses
--> utilize the associations with: @team found all data in teams
--> utilize the associations with: rushing_offenses (because it belongs
to team)
--> rushing_offenses.find(:all) would find all data in the
rushing_offenses table

Yes?.. No?..

I read up on the associations and understand the association thinking
and when to use associations. I just don't understand the code syntax
between them and how to use the code syntax for normal querying...

Älphä Blüë wrote:

Hrm, okay my tables are setup correctly now and populated so I'm trying
to do a find, but I must be doing it wrong..

I think you are.

class Team < ActiveRecord::Base
  has_many :rushing_offenses
end

class RushingOffense < ActiveRecord::Base
  belongs_to :team
end

This is fine.

I've tried the following in the rails console:

@team = Team.find(:all)
@rushing_offenses = @team.rushing_offenses.find(:all)

and get an undefined method rushing_offenses error

Of course you do. ActiveRecord::Base.find(:all) returns an array of
model objects, whereas methods like rushing_offenses are defined on
*each* model instance, not on the array as a whole. You need to call
rushing_offenses on the particular Team instance that you're interested
in. (And you don't need the .find(:all) on rushing_offenses.)
[...]

I read up on the associations and understand the association thinking
and when to use associations. I just don't understand the code syntax
between them and how to use the code syntax for normal querying...

I think you understand the syntax. You just seem to be uncertain on
what methods are defined on the individual models and what's defined on
the array as a whole.

Best,

Marnen Laibow-Koser wrote:

I think you understand the syntax. You just seem to be uncertain on
what methods are defined on the individual models and what's defined on
the array as a whole.

That's why I figured I would just see which methods were new since I
kept getting an undefined method error but as per my example above, the
array came up empty. So, according to that bit of code, my associations
are not giving me any new methods with which to work with. Again, maybe
I'm mistaken here.

What would the simplest check be to determine whether or not both models
are associated with one another?

Älphä Blüë wrote:
[...]

What would the simplest check be to determine whether or not both models
are associated with one another?

Use reflect_on_association (no, I won't describe it here -- read the
docs). That's how I usually do it in my RSpec files

If you're using Shoulda, I believe it has ready-made .should_belong_to
assertions.

Best,

remove the .find(:all), you've already found @team so
@team.rushing_offenses is all you need.

-eric

Eric wrote:

remove the .find(:all), you've already found @team so
@team.rushing_offenses is all you need.

-eric

On Jun 27, 8:24�am, "�lph� Bl��" <rails-mailing-l...@andreas-s.net>

Thanks Eric, that's what I had thought when I read everything Marnen
posted afterwards, which is why I tried to find out if maybe I was using
the wrong method.

But, for instance:

@team = Team.find :all
@team.rushing_offenses

@team.rushing_offenses

NoMethodError: undefined method 'rushing_offenses' for
#<Array:0x5ae469c>

Eric wrote:

> On Jun 27, 8:24 am, " lph Bl " <rails-mailing-l...@andreas-s.net>

Thanks Eric, that's what I had thought when I read everything Marnen
posted afterwards, which is why I tried to find out if maybe I was using
the wrong method.

But, for instance:

@team = Team.find :all
@team.rushing_offenses

>> @team.rushing_offenses

NoMethodError: undefined method 'rushing_offenses' for
#<Array:0x5ae469c>

Here @team isn't a team. It's an array containing all of your teams so
you can't call rushing_offenses (which is an instance method of Team)
on it - you've got to pull out one particular team and call it on
that.

Fred