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 -- Posted via http://www.ruby-forum.com/.
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?-- Posted via http://www.ruby-forum.com/.
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 Prill
Peter Baldwin wrote:
>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?
>>
>>
>
>
>
>
>
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
On 1/12/06, Peter Baldwin <pucbaldwin@yahoo.co.uk>
wrote:>
> 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?
>
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060112/c67fd125/attachment.html
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 -- Posted via http://www.ruby-forum.com/.
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
On 1/12/06, Pete Baldwin <pucbaldwin@yahoo.co.uk>
wrote:>
> 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
>
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060113/81093ee8/attachment.html