# Noob - Associated Tables Find Query

**URL:** <https://www.ruby-forum.com/t/noob-associated-tables-find-query/52151>\
**Category:** Rails\
**Created:** [January 11, 2006, 2:44pm UTC](https://www.ruby-forum.com/t/noob-associated-tables-find-query/52151 "2006-01-11T14:44:55Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Peter\_Baldwin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@Peter\_Baldwin](https://www.ruby-forum.com/u/Peter_Baldwin)\
**Post date:** [January 11, 2006, 2:44pm UTC](https://www.ruby-forum.com/t/noob-associated-tables-find-query/52151/1 "2006-01-11T14:44:55Z")

</div>

I’m trying to write a football prediction game.

I have a Matches table and a Predictions table.  
Each Match :has\_many Predictions, and each Prediction :belongs\_to a  
Match

Each Match has (amongst other things) a Match.match\_date field.  
Each Prediction has (amongst other things) a User\_id field

I’m trying to get a list of all Predictions a particular User has made  
which “belong to” Matches for an arbitrary time period (e.g. “Time.now  
\<= now()”)  
e.g. All “User 1”'s predictions relating to matches from last week.

Given that the tables are associated, is it possible to do a single  
.find query stipulating both the Prediction.User\_id and the  
Match.match\_date, which returns a list of a user’s predictions for the  
period?

Thanks in advance

Pete

---

<div class="post-metadata">

**Author:** ![Peter\_Baldwin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@Peter\_Baldwin](https://www.ruby-forum.com/u/Peter_Baldwin)\
**Post date:** [January 12, 2006, 5:35pm UTC](https://www.ruby-forum.com/t/noob-associated-tables-find-query/52151/2 "2006-01-12T17:35:13Z")

</div>

Anyone able to help with this? Assistance would be much appreciated.  
Pete

> I’m trying to write a football prediction game.
> 
> I have a Matches table and a Predictions table.  
> Each Match :has\_many Predictions, and each Prediction :belongs\_to a  
> Match
> 
> Each Match has (amongst other things) a Match.match\_date field.  
> Each Prediction has (amongst other things) a User\_id field
> 
> I’m trying to get a list of all Predictions a particular User has made  
> which “belong to” Matches for an arbitrary time period (e.g. “Time.now  
> \<= now()”)  
> e.g. All “User 1”'s predictions relating to matches from last week.
> 
> Given that the tables are associated, is it possible to do a single  
> .find query stipulating both the Prediction.User\_id and the  
> Match.match\_date, which returns a list of a user’s predictions for the  
> period?

---

<div class="post-metadata">

**Author:** ![Peter\_Baldwin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@Peter\_Baldwin](https://www.ruby-forum.com/u/Peter_Baldwin)\
**Post date:** [January 12, 2006, 5:52pm UTC](https://www.ruby-forum.com/t/noob-associated-tables-find-query/52151/3 "2006-01-12T17:52:25Z")

</div>

Hi, Peter,

try something like (untested):

Prediction.find(:all, :include =\> :match, :conditions =\>  
“predictions.user\_id = #{my\_user\_id} AND matches.match\_date \>=  
‘#{my\_formatted\_time}’”)

and have a look at examples in the test suite of  
[http://dev.rubyonrails.org/browser/trunk/activerecord/test/associations\_go\_eager\_test.rb](http://dev.rubyonrails.org/browser/trunk/activerecord/test/associations_go_eager_test.rb)

best regards  
Jan P.

---

<div class="post-metadata">

**Author:** ![Peter\_Baldwin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@Peter\_Baldwin](https://www.ruby-forum.com/u/Peter_Baldwin)\
**Post date:** [January 12, 2006, 6:34pm UTC](https://www.ruby-forum.com/t/noob-associated-tables-find-query/52151/4 "2006-01-12T18:34:56Z")

</div>

class Match \< ActiveRecord::Base  
has\_many :predictions  
end

class Prediction \< ActiveRecord::Base  
belongs\_to :match  
belongs\_to :user  
end

class User \< ActiveRecord::Base  
has\_many :predictions, :include =\> :matches  
end

user = User.find(1)  
cutoff\_date = TIme.now.at\_midnight

# all in code

predictions = user.predictions.collect { |p| p.match.match\_date \<=  
cutoff\_date }

# db method

predictions = user.predictions.find(:all, :joins =\> “matches”,  
:conditions  
=\> [“predictions.match\_id = match.id and match.match\_date \<= ?”,  
cutoff\_date])

note, these are untested examples and there are pros/cons to using one  
over  
the other.

Chris

---

<div class="post-metadata">

**Author:** ![Peter\_Baldwin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@Peter\_Baldwin](https://www.ruby-forum.com/u/Peter_Baldwin)\
**Post date:** [January 13, 2006, 12:38am UTC](https://www.ruby-forum.com/t/noob-associated-tables-find-query/52151/5 "2006-01-13T00:38:56Z")

</div>

Jan and Chris, I really appreciate your responses.

I went with Chris’s alternatives, and have ended up with the following

class User \< ActiveRecord::Base  
has\_many :predictions, :include =\> :matches  
end

class Match \< ActiveRecord::Base  
has\_many :predictions  
end

class Prediction \< ActiveRecord::Base  
belongs\_to :user  
belongs\_to :match  
end

—controller—  
@user = User.find(session[:user\_id])  
@cutoff\_date = Time.new

@oldpredictions = @user.predictions.find(:all, :joins =\> “matches”,  
:conditions =\> [“predictions.match\_id = match.id and match.match\_date \<=  
?”, @cutoff\_date])

* * *

But its giving me the following error…

Mysql::Error: Unknown table ‘predictions’ in where clause: SELECT \* FROM  
predictions matches WHERE (predictions.user\_id = 2 AND  
(predictions.match\_id = match.id and match.match\_date \<= ‘2006-01-12  
18:34:14’))

and if I load up the console, and go

@user = User.find(1)  
@user.predictions

it gives me the following error

ActiveRecord::ConfigurationError: Association was not found; perhaps you  
misspelled it? You specified :include =\> :matches

Is there something wrong with the models?

Cheers again,

Pete

---

<div class="post-metadata">

**Author:** ![Peter\_Baldwin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ec9cab/32.png) [@Peter\_Baldwin](https://www.ruby-forum.com/u/Peter_Baldwin)\
**Post date:** [January 13, 2006, 1:32pm UTC](https://www.ruby-forum.com/t/noob-associated-tables-find-query/52151/6 "2006-01-13T13:32:19Z")

</div>

blasted typos…

class User \< ActiveRecord::Base  
has\_many :predictions, :include =\> :match # i originally had  
:matches, that was a typo  
end

@user = User.find(session[:user\_id])  
@cutoff\_date = Time.now

# combination of Jan’s and my suggestion

@oldpredictions = @user.predictions.find(:all, :include =\>  
:match,:conditions =\> ["  
match.match\_date \<= ?", @cutoff\_date])

sorry about that. this should fix it.

Chris