Using a has_many association with a LEFt JOIN

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

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.