Using a has_many association with a LEFt JOIN

rubyonrails-talk
1

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

2

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)

3

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.