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.

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.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs