Noob - Associated Tables Find Query


#1

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


#2

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?


#3

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

best regards
Jan P.


#4

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


#5

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


#6

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