# Using a has\_many association with a LEFt JOIN

**URL:** <https://www.ruby-forum.com/t/using-a-has-many-association-with-a-left-join/143757>\
**Category:** Rails\
**Created:** [July 22, 2008, 2:48pm UTC](https://www.ruby-forum.com/t/using-a-has-many-association-with-a-left-join/143757 "2008-07-22T14:48:49Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rory\_McKinley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/6bbea6/32.png) [@Rory\_McKinley](https://www.ruby-forum.com/u/Rory_McKinley)\
**Post date:** [July 22, 2008, 2:48pm UTC](https://www.ruby-forum.com/t/using-a-has-many-association-with-a-left-join/143757/1 "2008-07-22T14:48:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Rory\_McKinley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/6bbea6/32.png) [@Rory\_McKinley](https://www.ruby-forum.com/u/Rory_McKinley)\
**Post date:** [July 22, 2008, 3:36pm UTC](https://www.ruby-forum.com/t/using-a-has-many-association-with-a-left-join/143757/2 "2008-07-22T15:36:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Rory\_McKinley](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/6bbea6/32.png) [@Rory\_McKinley](https://www.ruby-forum.com/u/Rory_McKinley)\
**Post date:** [July 23, 2008, 7:00am UTC](https://www.ruby-forum.com/t/using-a-has-many-association-with-a-left-join/143757/3 "2008-07-23T07:00:21Z")

</div>

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.