Joining Multiple Tables

I’m trying to replicate a join in RoR that I can do in SQL very
simply.

Three tables/models that I have are: Users, Users_Activities,
Activities and Codes.

Users and Activities have appropriate has_many associations through
Users_Activities model, so it’s easy to do a join to get all
activities for a given user.

Codes, on the other hand, is a different story. It’s kind of a
“repository” of all codified data. For example, the Activity table
has a “Activity Type” field that is an integer field. To get the
actual Activity Type name, you have to look it up in the Codes table
like so (joining with user table to get all activities for user id
“1”):

select activities.*, codes.name from activities, users_activities,
codes
where users_activities.user_id = 1
and users_activities.activity_id = activities.id
and activities.activity_type = codes.id

I can do part of the above query if i don’t include the Codes table
like so:

Activity.all(:joins => :users_activities, :conditions =>
{‘users_activities.user_id’ => “1”})

How do I also join Codes?

joins => [:users_activities, :codes]

On Tue, Mar 30, 2010 at 3:03 PM, khagimoto [email protected]
wrote:

Codes, on the other hand, is a different story. It’s kind of a
and activities.activity_type = codes.id

I can do part of the above query if i don’t include the Codes table
like so:

Activity.all(:joins => :users_activities, :conditions =>
{‘users_activities.user_id’ => “1”})

How do I also join Codes?

class User < AcrtiveRecord::Base
has_many :user_activities
end

class UserActivity < ActiveRecord::Base
belongs_to :user
belongs_to :code, :foreign_key => “activity_type”
end

Activity.find_all_by_user_id(1, :include => :code)

This will return a collection of all of the activities for user#1 with
attached instances of the code for each activity


Rick DeNatale

Blog: http://talklikeaduck.denhaven2.com/
Twitter: http://twitter.com/RickDeNatale
WWR: http://www.workingwithrails.com/person/9021-rick-denatale
LinkedIn: http://www.linkedin.com/in/rickdenatale

Not sure if I understand this completely… The “activity_type” field
is in Activity.

If I try to do as you suggested, it complains that
“Association named ‘code’ was not found”
when it tries to execute the line Activity.find…

Thanks, but this doesn’t seem to get me the fields from the Codes in
the ResultSet - and it also tries to do
activities.id = codes.id
when it should really be
activities.activity_type = codes.id
will keep looking - thanks though!

On Tue, Mar 30, 2010 at 6:35 PM, khagimoto [email protected]
wrote:

Not sure if I understand this completely… The “activity_type” field
is in Activity.

If I try to do as you suggested, it complains that
“Association named ‘code’ was not found”
when it tries to execute the line Activity.find…

Did you add the association declaration:

belongs_to :code, :foreign_key => “activity_type”

to the Activity class?

That says that the activities table has a foreign key field called
activity_type which is the key of a record in the codes table.

And it creates the association called code which the error message is
complaining about.

activities for a given user.
where users_activities.user_id = 1


You received this message because you are subscribed to the Google G. “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [email protected]
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.


Rick DeNatale

Blog: http://talklikeaduck.denhaven2.com/
Twitter: http://twitter.com/RickDeNatale
WWR: http://www.workingwithrails.com/person/9021-rick-denatale
LinkedIn: http://www.linkedin.com/in/rickdenatale

Ooooooooh… ok - I moved that line from UserActivity class and it’s
working now.
Thanks so much for your help and explanation!

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs