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...?
--
Posted via http://www.ruby-forum.com/.
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? -- Posted via http://www.ruby-forum.com/.
I figured it out - will post my response in a moment in case someone reads and tries to respond. -- Posted via http://www.ruby-forum.com/.
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. -- Posted via http://www.ruby-forum.com/.
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.
--
Posted via http://www.ruby-forum.com/.
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!
--
Posted via http://www.ruby-forum.com/.
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??
--
Posted via http://www.ruby-forum.com/.
Solved it on my own. :) test[0].tsrs[0] .. instead of .. test[0].tsrs_ratings.tsrs[0] -- Posted via http://www.ruby-forum.com/.
Alpha Blue wrote:> Solved it on my own. :) > > 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.. -- Posted via http://www.ruby-forum.com/.