# I'm not sure what happened with my app - ActiveRecord Query

**URL:** <https://www.ruby-forum.com/t/im-not-sure-what-happened-with-my-app-activerecord-query/173139>\
**Category:** Rails\
**Created:** [August 3, 2009, 10:17pm UTC](https://www.ruby-forum.com/t/im-not-sure-what-happened-with-my-app-activerecord-query/173139 "2009-08-03T22:17:13Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\
**Post date:** [August 3, 2009, 10:17pm UTC](https://www.ruby-forum.com/t/im-not-sure-what-happened-with-my-app-activerecord-query/173139/1 "2009-08-03T22:17:13Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\
**Post date:** [August 3, 2009, 10:30pm UTC](https://www.ruby-forum.com/t/im-not-sure-what-happened-with-my-app-activerecord-query/173139/2 "2009-08-03T22:30:20Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\
**Post date:** [August 4, 2009, 2:35am UTC](https://www.ruby-forum.com/t/im-not-sure-what-happened-with-my-app-activerecord-query/173139/3 "2009-08-04T02:35:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\
**Post date:** [August 4, 2009, 2:40am UTC](https://www.ruby-forum.com/t/im-not-sure-what-happened-with-my-app-activerecord-query/173139/4 "2009-08-04T02:40:46Z")

</div>

On Mon, Aug 3, 2009 at 5:35 PM, Alpha  
Blue[removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\
**Post date:** [August 4, 2009, 11:23am UTC](https://www.ruby-forum.com/t/im-not-sure-what-happened-with-my-app-activerecord-query/173139/5 "2009-08-04T11:23:29Z")

</div>

2009/8/3 Alpha B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> 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

---

<div class="post-metadata">

**Author:** ![elricstorm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/da6949/32.png) [@elricstorm](https://www.ruby-forum.com/u/elricstorm)\
**Post date:** [August 4, 2009, 12:36pm UTC](https://www.ruby-forum.com/t/im-not-sure-what-happened-with-my-app-activerecord-query/173139/6 "2009-08-04T12:36:57Z")

</div>

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.