Using a has_many association with a LEFt JOIN

Hello List

I have a Milestone model as follows:

class Milestone < ActiveRecord::Base
   belongs_to :goal
   belongs_to :user
   has_many :progresses, :order => 'created_at DESC'
   has_many :views

Which has an association with a model called views:

class View < ActiveRecord::Base
     set_primary_keys :milestone_id, :user_id
     belongs_to :user
     belongs_to :milestone

The logic goes as follows:

Each milestone belongs only to one user, but may be viewed by many users. The views are there to track when last a user viewed a milestone (whether the milestone belonged to the user viewing it, or to another user). When the user goes to the "milestones" UI, I want to display all milestones (including those that haven't been viewed by the user at all). I also want to track when last, if at all, the currently logged in user has viewed each milestone.

Because I will have many milestones and many users, it seems rather wasteful to pull all the views for each milestone, and then loop through the views looking for a view belonging to the current user. What I would like to do is pull all the milestones, plus any views for the currently logged in user (the @user object stores the logged in user's details).

In raw SQL, this translates to something like:

SELECT milestones.name, views.view_time
FROM milestones LEFT JOIN views ON views.milestone_id = milestones.id
WHERE views.user_id = #{@user.id} /* Currently logged in user */

I would prefer to avoid using the raw SQL avenue if possible, so does anybody have any suggestions as to how to achieve this with Rails?

Regards

Rory

In raw SQL, this translates to something like:

SELECT milestones.name, views.view_time
FROM milestones LEFT JOIN views ON views.milestone_id = milestones.id
WHERE views.user_id = #{@user.id} /* Currently logged in user */

Create the view in the database, and attach a model to it. From the
Rails perspective, it's just another model.

migration:

self.up
  sql = "CREATE OR REPLACE VIEW `milestoneview` AS SELECT"
  sql += "`m`.`milestone_id` AS `milestone_id`"
  sql += "`v`.`view_time` AS `view_time`"
  # add whatever other fields may be useful
  sql += "FROM milestones m"
  sql += "LEFT JOIN views v ON v.milestone_id = m.id"
  execute sql
end

save the WHERE to be applied in the Rails app as an adder to your find
method just like a regular table

model:

class Milestoneview < ActiveRecord::Base
  belongs_to :goal
  belongs_to :user
  has_many :progresses, :foreign_key => 'milestone_id', :order =>
'created_at DESC'
  has_many :views, :foreign_key => 'milestone_id'
end

and off you go (or something close to the above)

Ar Chron wrote:

In raw SQL, this translates to something like:

SELECT milestones.name, views.view_time
FROM milestones LEFT JOIN views ON views.milestone_id = milestones.id
WHERE views.user_id = #{@user.id} /* Currently logged in user */

Create the view in the database, and attach a model to it. From the Rails perspective, it's just another model.

<snip>

Thanks Ar

That was a route I hadn't thought of, and definitely one that I will consider for both this problem and future problems of a similar nature. As a quick fix, I decided just to split the views out of the model, and retrieve them on their own using View.find.