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.

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.

On Mon, Aug 3, 2009 at 5:35 PM, Alpha
Blue[email protected] 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 B. [email protected]:

Pull Data:
First Team found

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

Colin L. 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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs