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?

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.

Write some unit tests now to ensure it doesn't break again!

Best,
jeremy

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