Non-association and pulling data from many models

rubyonrails-talk
1

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?

2

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.

3

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.

4

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??

5

Alpha Blue 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..