Joining Multiple Tables

rubyonrails-talk
1

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?

2

joins => [:users_activities, :codes]

3

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

4

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!

5

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...

6

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.

7

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