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]

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

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!

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

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.

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