Forum: Ruby on Rails Noob - Associated Tables Find Query

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
A910489fae527be952bb51bf0b95cb31?d=identicon&s=25 Peter Baldwin (Guest)
on 2006-01-11 14:44
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
A5c7012591aa2902256ae1bdb855e2cb?d=identicon&s=25 Peter Baldwin (Guest)
on 2006-01-12 17:35
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?
E48d29dc8fedb2878fa518d41cc63d88?d=identicon&s=25 Jan Prill (Guest)
on 2006-01-12 17:52
(Received via mailing list)
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/activerec...

best regards
Jan Prill
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2006-01-12 18:34
(Received via mailing list)
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
A5c7012591aa2902256ae1bdb855e2cb?d=identicon&s=25 Pete Baldwin (Guest)
on 2006-01-13 00:38
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
A2c85dc5ee81b12e3cc0a6522e8d079d?d=identicon&s=25 Chris Hall (Guest)
on 2006-01-13 13:32
(Received via mailing list)
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
This topic is locked and can not be replied to.