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.
Peter Baldwin (Guest)
on 2006-01-11 15: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
Peter Baldwin (Guest)
on 2006-01-12 18: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?
Jan P. (Guest)
on 2006-01-12 18: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 P.
Chris H. (Guest)
on 2006-01-12 19: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
Pete B. (Guest)
on 2006-01-13 01: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
Chris H. (Guest)
on 2006-01-13 14: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.