# Joining Multiple Tables **URL:** \ **Category:** Rails\ **Created:** [March 30, 2010, 9:06pm UTC](https://www.ruby-forum.com/t/joining-multiple-tables/186047 "2010-03-30T21:06:11Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![khagimoto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ee59a6/32.png) [@khagimoto](https://www.ruby-forum.com/u/khagimoto)\ **Post date:** [March 30, 2010, 9:06pm UTC](https://www.ruby-forum.com/t/joining-multiple-tables/186047/1 "2010-03-30T21:06:11Z")
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? ---
**Author:** ![khagimoto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ee59a6/32.png) [@khagimoto](https://www.ruby-forum.com/u/khagimoto)\ **Post date:** [March 30, 2010, 10:06pm UTC](https://www.ruby-forum.com/t/joining-multiple-tables/186047/2 "2010-03-30T22:06:28Z")
joins =\> [:users\_activities, :codes] ---
**Author:** ![khagimoto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ee59a6/32.png) [@khagimoto](https://www.ruby-forum.com/u/khagimoto)\ **Post date:** [March 30, 2010, 10:07pm UTC](https://www.ruby-forum.com/t/joining-multiple-tables/186047/3 "2010-03-30T22:07:38Z")
On Tue, Mar 30, 2010 at 3:03 PM, khagimoto [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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/](http://talklikeaduck.denhaven2.com/) Twitter: [http://twitter.com/RickDeNatale](http://twitter.com/RickDeNatale) WWR: [http://www.workingwithrails.com/person/9021-rick-denatale](http://www.workingwithrails.com/person/9021-rick-denatale) LinkedIn: [http://www.linkedin.com/in/rickdenatale](http://www.linkedin.com/in/rickdenatale) ---
**Author:** ![khagimoto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ee59a6/32.png) [@khagimoto](https://www.ruby-forum.com/u/khagimoto)\ **Post date:** [March 31, 2010, 12:36am UTC](https://www.ruby-forum.com/t/joining-multiple-tables/186047/4 "2010-03-31T00:36:53Z")
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… ---
**Author:** ![khagimoto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ee59a6/32.png) [@khagimoto](https://www.ruby-forum.com/u/khagimoto)\ **Post date:** [March 31, 2010, 12:31am UTC](https://www.ruby-forum.com/t/joining-multiple-tables/186047/5 "2010-03-31T00:31:37Z")
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! ---
**Author:** ![khagimoto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ee59a6/32.png) [@khagimoto](https://www.ruby-forum.com/u/khagimoto)\ **Post date:** [March 31, 2010, 1:22am UTC](https://www.ruby-forum.com/t/joining-multiple-tables/186047/6 "2010-03-31T01:22:34Z")
On Tue, Mar 30, 2010 at 6:35 PM, khagimoto [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 removed\_email\_address@domain.invalid. > To unsubscribe from this group, send email to removed\_email\_address@domain.invalid. > For more options, visit this group at [http://groups.google.com/group/rubyonrails-talk?hl=en](http://groups.google.com/group/rubyonrails-talk?hl=en). – Rick DeNatale Blog: [http://talklikeaduck.denhaven2.com/](http://talklikeaduck.denhaven2.com/) Twitter: [http://twitter.com/RickDeNatale](http://twitter.com/RickDeNatale) WWR: [http://www.workingwithrails.com/person/9021-rick-denatale](http://www.workingwithrails.com/person/9021-rick-denatale) LinkedIn: [http://www.linkedin.com/in/rickdenatale](http://www.linkedin.com/in/rickdenatale) ---
**Author:** ![khagimoto](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/ee59a6/32.png) [@khagimoto](https://www.ruby-forum.com/u/khagimoto)\ **Post date:** [March 31, 2010, 2:43am UTC](https://www.ruby-forum.com/t/joining-multiple-tables/186047/7 "2010-03-31T02:43:19Z")
Ooooooooh… ok - I moved that line from UserActivity class and it’s working now. Thanks so much for your help and explanation!