Non-association and pulling data from many models

Rails
1

Here is my current model with the issue:

class Virtual < ActiveRecord::Base
belongs_to :team

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

def self.pullstats(model, teamone)
model.find(:all, :conditions => [‘team_id = ?’, teamone], :order =>
:team_id)
end

def self.start(teamone, teamtwo)
nameone = Team.find(teamone)
nametwo = Team.find(teamtwo)
rushone = compiled_this_week.pullstats(RushingOffense, teamone)
rushtwo = compiled_this_week.pullstats(RushingOffense, teamtwo)
passone = compiled_this_week.pullstats(PassingOffense, teamone)
passtwo = compiled_this_week.pullstats(PassingOffense, teamtwo)
return nameone, nametwo, rushone, rushtwo, passone, passtwo
end

end

My current test uses:

a,b,c,d,e,f = Virtual.start(10,12)

10 and 12 are IDs for different teams.
a = team model for team 10
b = team model for team 12
c = rush model for team 10
d = rush model for team 12
e = rush model for team 10
f = pass model for team 12

The test will pass and data can be pulled and I can view the data using
var.fieldname etc. to show the data comparisons between both teams.
However, I know this has got to be a very long way of doing things.

Normally I would try to use a :joins statement and plop each table side
by side to one another and then just pull the respective fields. Only,
I don’t think I can do that with my current model.

Virtual Model has (no data) and the controller is just being used for
grabbing data. I have a total of 37 tables and I’m just not certain how
to pull the data without doing something as clunky as what I’m doing.

From what I understand, I can only do a :joins from a model that
belongs_to another model… i.e. RushingOffense belongs_to :team so I can
do RushingOffense.find(:all, :joins => [:team]) but I can’t do
Team.find(:all, :joins => [:rushing_offense]). If I try the latter I’ll
get an error about not finding an association.

I setup my data so that all my data tables belong_to team and team has
many data tables. I just want to do a side by side comparison of two
teams. What is the best way to accomplish this?

Should I move away from the virtual controller and go back to the teams
controller and create a specific view and reference everything from
there? Or, should I continue with the clunky way I’m doing it now…?

2

I suppose I could use:

def self.pullstats(model ,team, field)
model.find(:all, :select => field, :conditions => [‘team_id = ?’,
team], :order => :team_id)
end

And from the controller declare my instance variables as:

@teamone_rush = Virtual.pullstats(RushingOffense, 10, “ydspg”)
@teamtwo_rush = Virtual.pullstats(RushingOffense, 12, “ydspg”)
etc.

But as you can see this would amount to (2 teams x 37 models of data
with single column retrievals) or:

74 instance variables in the controller…

My problem is I don’t know how to associate the Virtuals controller to
the rest of the models I want to pull the data from so that I can :join
their respective tables to one large query…

I would rather do:

def self.pullstats(team)
Team.find(team, :joins => [:rushing_offense, :passing_offense,
:total_offense, :scoring_offense, etc. etc., :conditions => [‘team_id =
?’, team], :order => :team_id)
end

This would do side by side joins where the team_id of each table would
line up with the team.id of the Teams table.

How can I do the above? Anyone?

3

I figured it out - will post my response in a moment in case someone
reads and tries to respond.

4

Okay, here was my issue in detail.

Because I have 37 controllers/models, I needed to make it easier for me
to manage them. So I performed a lot of inheritance. But, I forgot to
add true associations.

Example Controller:

class UniversalTemplatesController < ApplicationController

contains all functions for all 37 controllers

end

class RushingOffensesController < UniversalTemplatesController

contains only an authenticate routine - nothing else

end

etc. etc. for the next 36 controllers

Example Model:

class InheritanceTemplate < ActiveRecord::Base
self.abstract_class = true

contains all methods that other 37 models need

end

class RushingOffense < InheritanceTemplate

I forgot to put my associations in here

Instead I put them in the InheritanceTemplate model

thinking they would inherit - untrue

Correction is below

belongs_to :team
end

etc. etc. for the other 36 models

class Team < ActiveRecord::Base

I did have has_many :inheritance_templates

This was wrong

Correction below

has_many :rushing_offenses
has_many :passing_offenses
etc. etc.
end

Now I can do:

Team.all :join => [:rushing_offense, :passing_offense]
etc.

I can’t believe I made this simple mistake. I guess it happens when you
have so many models/controllers and are working on a large project.

5

Well large queries are one thing but this is probably too large:

def self.start(teamone, teamtwo)
Team.find(teamone,teamtwo, :joins => [ :tsrs_ratings,
:rushing_offenses, :passing_offenses, :scoring_offenses,
:total_offenses, :red_zone_offenses, :fumbles_losts,
:passes_had_intercepteds, :turnovers_losts, :sacks_alloweds,
:tackles_for_loss_alloweds, :passing_efficiencies,
:first_downs_offenses, :third_down_conversion_percentages,
:fourth_down_conversion_percentages, :total_defenses,
:rushing_defenses, :pass_defenses, :scoring_defenses,
:red_zone_defenses, :fumbles_recovereds,
:passes_intercepteds, :turnovers_gaineds, :sacks,
:tackles_for_losses, :pass_efficiency_defenses, :first_downs_defenses,
:third_down_percentage_defenses,
:fourth_down_percentage_defenses, :kickoff_returns,
:punt_returns, :net_puntings, :kickoff_return_yardage_defenses,
:punt_return_yardage_defenses, :turnover_margins ])
end

I only need to select specific fields in each of those tables but how
would I go about using :select => with this? In order of which tables
are grabbed/referenced?

:joins => 1,2,3,4,5 etc…
:select => “1”, “2”, “3”, “4”, “5” etc.

?

And, my data has weekly compiled but my teams table does not. My Teams
table is static and untouched and contains exactly 120 teams. My other
data tables could contain up to 16 weeks worth of data.

I usually use a scope or condition to pull the data based on:

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

But, it’s either going to be ambiguous in this case or it won’t work
because the created_at won’t apply to teams. Any tips/pointers for how
to get past this particular piece?

I’m only posting rebuttals to mark my progress but I see no one
answering or commenting on my threads so either I’m asking too much, or
giving too much information, the latter which I always thought was
better when asking questions.

6

Okay breaking everything posted in this topic down to one question only:

Look at the following:

def self.start(teamone, teamtwo)
week_start_date
=Time.now.beginning_of_week.to_date.strftime(’%Y-%m-%d’)
week_end_date = Time.now.end_of_week.to_date.strftime(’%Y-%m-%d’)
compiled_on = week_start_date…week_end_date
Team.find( teamone, teamtwo,
:joins => [ :tsrs_ratings, :rushing_offenses ],
:conditions => { :tsrs_ratings => {:compiled_on => compiled_on},
:rushing_offenses => {:compiled_on => compiled_on} },
:select => “name, tsrs_ratings.tsrs, rushing_offenses.ydspg”,
:order => “tsrs_ratings.team_id” )
end

Everything about this works fine except for the select option. I want
to select name from the teams table, tsrs from the tsrs_ratings table,
and ydspg from the rushing_offenses table.

How do I combine a multiple select from different tables?

If I have the answer to this question I think I’m going to be just
dandy.

Thanks!

7

Okay, this doesn’t make any sense. This should work:

def self.start(teamone, teamtwo)

week_start_date
=Time.now.beginning_of_week.to_date.strftime(’%Y-%m-%d’)
week_end_date = Time.now.end_of_week.to_date.strftime(’%Y-%m-%d’)
compiled_on = week_start_date…week_end_date
Team.find( teamone, teamtwo,
:select => “teams.name, tsrs_ratings.tsrs, rushing_offenses.ydspg”,
:joins => [ :tsrs_ratings, :rushing_offenses ],
:conditions => { :tsrs_ratings => {:compiled_on => compiled_on},
:rushing_offenses => {:compiled_on => compiled_on} },
:order => “teams.id” )

end

SQL returns (from development.log):

SELECT teams.name, tsrs_ratings.tsrs, rushing_offenses.ydspg
FROM teams
INNER JOIN tsrs_ratings ON tsrs_ratings.team_id = teams.id
INNER JOIN rushing_offenses ON rushing_offenses.team_id = teams.id
WHERE (teams.id IN (10,12) AND (tsrs_ratings.compiled_on BETWEEN
‘2009-07-20’ AND ‘2009-07-26’ AND rushing_offenses.compiled_on
BETWEEN ‘2009-07-20’ AND ‘2009-07-26’)) ORDER BY teams.id

If I take the same sql statement returned from the development log and
place into mysql directly I get:

name tsrs ydspg
Florida 112.005 231.14
TCU 99.0148 220.23

Yet if I try to reference it in rails:

test = Virtual.start(10,12)

test[0]
=> #<Team name: “Florida”>

test[0].tsrs_ratings
=> []

test[0].tsrs_ratings[0]
=> nil

What gives??

8

Solved it on my own. :slight_smile:

test[0].tsrs[0]

… instead of … test[0].tsrs_ratings.tsrs[0]

9

Alpha B. wrote:

Solved it on my own. :slight_smile:

test[0].tsrs[0]

… instead of … test[0].tsrs_ratings.tsrs[0]

In case someone views this with a similar issue at hand, the final fix
was listed somewhat incorrectly.

It was supposed to read test[0].tsrs and not test[0].tsrs[0]

You can also do:

:select => “teams.name, tsrs_ratings.tsrs AS newname,
rushing_offenses.ydspg AS anothernewname”

and then access it via, test[0].newname and test[0].anothernewname and
since I had so many stats this worked out best.

By using such a large query and only performing one, it makes it a lot
easier on my database.

Fyi…