Rails - Aug 2009 - I'm not sure what happened with my app - ActiveRecord Query

Everything was working perfectly.  I ran a few tests this morning in
development and found a few errors and corrected them.

Another thing I did was clear out "old data" in 4 tables.  3 of these
tables are now having the same issue using the following query:

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
tsos_offense = Team.find(teamone,teamtwo, :joins => [:tsos_offenses],
:conditions => {:tsos_offenses => {:compiled_on => compiled_on}},
:order
=> "teams.id")

The SQL statement in development.log shows the following:

SELECT `teams`.* FROM `teams`
INNER JOIN `tsos_offenses` ON tsos_offenses.team_id = teams.id
WHERE (`teams`.`id` IN (10,1) AND (`tsos_offenses`.`compiled_on` BETWEEN
''2009-08-03'' AND ''2009-08-09''))
ORDER BY teams.id

It was working perfectly before today.

If I do the following:

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
tsos_offense = Team.find(10,1, :select => "tsos_offenses.totoff AS
totoff, tsos_offenses.rushoff AS rushoff, tsos_offenses.passoff AS
passoff, tsos_offenses.scoroff AS scoroff, tsos_offenses.rzonoff AS
rzonoff, tsos_offenses.fumlost AS fumlost, tsos_offenses.passhint AS
passhint, tsos_offenses.tolost AS tolost, tsos_offenses.sacksall AS
sacksall, tsos_offenses.tackflossall AS tackflossall,
tsos_offenses.passeff AS passeff, tsos_offenses.firdwns AS firdwns,
tsos_offenses.thrdwncon AS thrdwncon, tsos_offenses.fthdwncon AS
fthdwncon, tsos_offenses.totals AS totals", :joins => [:tsos_offenses],
:conditions => {:tsos_offenses => {:compiled_on => compiled_on}},
:order
=> "teams.id")

It works perfectly again.  All I''m doing is placing a select into the
query.  Why would it change behavior like this in one day?  I haven''t
even touched this model and the only thing I did was clear out the old
table data and replace it with new table data.  The compiled_on dates
match up perfectly.

The only reasoning I can think about is that there isn''t data
"beyond"
this week but why would that matter?  The compiled_on is checking for
data between 2009-08-03 and 2009-08-09.

The compiled_on column in tsos_offenses table shows 2009-08-03.  I''m a
bit confused as to why I have to use select and specify every column in
the table instead of a simple joins statement..

Any ideas what the problem might be?
-- 
Posted via http://www.ruby-forum.com/.

Test One:

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
tsos_offense = Team.find(10,1, :joins => [:tsos_offenses], :conditions 
=> {:tsos_offenses => {:compiled_on => compiled_on}}, :order => 
"teams.id")

Pull Data:

tsos_offense[0].tsos_offenses[0]

=> #<TsosOffense id: 1081, team_id: 1, totoff: 45.74, rushoff: 201.6, 
passoff: 0.63, scoroff: 83.79, rzonoff: 174.72, fumlost: 60.48, 
passhint: 201.6, tolost: 186.48, sacksall: 199.92, tackflossall: 193.2, 
passeff: 102.06, firdwns: 24.95, thrdwncon: 133.56, fthdwncon: 118.44, 
totals: 123.369, compiled_on: "2009-08-03", created_at:
"2009-08-03
19:02:11", updated_at: "2009-08-03 19:02:12">

First Team found

tsos_offense[1].tsos_offenses[1]

=> nil

This code was working yesterday.

Now it appears that:

tsos_offense[1].tsos_offenses[0]

=> #<TsosOffense id: 1090, team_id: 10, totoff: 118.42, rushoff: 139.86, 
passoff: 49.9, scoroff: 165.53, rzonoff: 189.84, fumlost: 118.44, 
passhint: 201.6, tolost: 196.56, sacksall: 181.44, tackflossall: 179.76, 
passeff: 196.56, firdwns: 119.7, thrdwncon: 143.64, fthdwncon: 138.6, 
totals: 152.846, compiled_on: "2009-08-03", created_at:
"2009-08-03
19:02:11", updated_at: "2009-08-03 19:02:12">

pulls the second team.

Why would tsos_offense[1].tsos_offenses[1] work on one compile and on 
another day tsos_offense[1].tsos_offenses[0] works?

I even checked my views.  For two weeks I''ve been using [1][1] pairings
for the second team and now [1][0] is the correct pairing?

Am I better off going with a select?  I don''t like how the behavior is 
working with this.
-- 
Posted via http://www.ruby-forum.com/.

I moved the three tables to selects and the response time difference is 
negligible about 50ms for the entire routine.  It also keeps the entire 
routine formalized and uniform.

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

On Mon, Aug 3, 2009 at 5:35 PM, Alpha
Blue<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
> I moved the three tables to selects and the response time difference is
> negligible about 50ms for the entire routine.  It also keeps the entire
> routine formalized and uniform.
Write some unit tests now to ensure it doesn''t break again!

Best,
jeremy

2009/8/3 Alpha Blue
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>:
>
> Test One:
>
> 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
> tsos_offense = Team.find(10,1, :joins => [:tsos_offenses], :conditions
> => {:tsos_offenses => {:compiled_on => compiled_on}}, :order =>
> "teams.id")
>
> Pull Data:
>
> tsos_offense[0].tsos_offenses[0]
>
> => #<TsosOffense id: 1081, team_id: 1, totoff: 45.74, rushoff: 201.6,
> passoff: 0.63, scoroff: 83.79, rzonoff: 174.72, fumlost: 60.48,
> passhint: 201.6, tolost: 186.48, sacksall: 199.92, tackflossall: 193.2,
> passeff: 102.06, firdwns: 24.95, thrdwncon: 133.56, fthdwncon: 118.44,
> totals: 123.369, compiled_on: "2009-08-03", created_at:
"2009-08-03
> 19:02:11", updated_at: "2009-08-03 19:02:12">
>
> First Team found
>
> tsos_offense[1].tsos_offenses[1]
>
> => nil
>
> This code was working yesterday.
>
> Now it appears that:
>
> tsos_offense[1].tsos_offenses[0]
>
> => #<TsosOffense id: 1090, team_id: 10, totoff: 118.42, rushoff:
139.86,
> passoff: 49.9, scoroff: 165.53, rzonoff: 189.84, fumlost: 118.44,
> passhint: 201.6, tolost: 196.56, sacksall: 181.44, tackflossall: 179.76,
> passeff: 196.56, firdwns: 119.7, thrdwncon: 143.64, fthdwncon: 138.6,
> totals: 152.846, compiled_on: "2009-08-03", created_at:
"2009-08-03
> 19:02:11", updated_at: "2009-08-03 19:02:12">
>
> pulls the second team.
>
> Why would tsos_offense[1].tsos_offenses[1] work on one compile and on
> another day tsos_offense[1].tsos_offenses[0] works?
>
> I even checked my views.  For two weeks I''ve been using [1][1]
pairings
> for the second team and now [1][0] is the correct pairing?
Is there an explicit sort to ensure the order of the results is
consistent?  If not then I believe the order may change when
apparently unrelated records are changed.  Thus the record that was
[1][1] could become [1][0], though I say this without fully
understanding what you are doing so I may be talking rubbish.

Colin
>
> Am I better off going with a select?  I don''t like how the
behavior is
> working with this.
> --
> Posted via http://www.ruby-forum.com/.
>
> >
>

Colin Law wrote:
> Is there an explicit sort to ensure the order of the results is
> consistent?  If not then I believe the order may change when
> apparently unrelated records are changed.  Thus the record that was
> [1][1] could become [1][0], though I say this without fully
> understanding what you are doing so I may be talking rubbish.
> 
> Colin
Hi Colin, the sort order was ordered by team.id while the joins are 
occurring with an association by foreign key of team_id.  I believe that 
you are correct.  When I cleared out all of my table data for those 
three tables and recreated new table data the behavior simply changed. 
It was unexpected.

The real issue with table joins without select statements is as I 
described with how you pull the data:

x{0].y[0].column

versus when using selects

x[0].column

Further, if you don''t apply selects with AS alias values and your
column
has the same name as a model, it will return an empty array on that 
column value.  So, my solution was to use select with AS alias on all 
tables that involved joins.

It''s just more direct and solved my issue.

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