# Find with join over aggregated

**URL:** <https://www.ruby-forum.com/t/find-with-join-over-aggregated/175358>\
**Category:** Rails\
**Created:** [September 11, 2009, 7:34pm UTC](https://www.ruby-forum.com/t/find-with-join-over-aggregated/175358 "2009-09-11T19:34:38Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![bitrocker](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b782af/32.png) [@bitrocker](https://www.ruby-forum.com/u/bitrocker)\
**Post date:** [September 11, 2009, 7:34pm UTC](https://www.ruby-forum.com/t/find-with-join-over-aggregated/175358/1 "2009-09-11T19:34:38Z")

</div>

Hi,

I want to get a query mapped to an ActiveRecord model, but face  
problems. Here is what the query (for e.g. user.id = 2) could look  
like:

SELECT \*  
FROM `music`

LEFT OUTER JOIN (  
SELECT MIN( taggings.created\_at ) AS first\_time\_tagged, music2.id  
AS id  
FROM taggings  
LEFT OUTER JOIN music AS music2 ON taggings.taggable\_id =  
music2.id  
GROUP BY music2.id  
)get\_first\_tagging ON music.id = get\_first\_tagging.id

INNER JOIN taggings ON taggings.taggable\_id = music.id

INNER JOIN tags ON taggings.tag\_id = tags.id

WHERE taggings.tagger\_id = 2  
ORDER BY first\_time\_tagged

As you may have guessed, I am using acts\_as\_taggable\_on and as  
delicious serves as good example for tagging integration, I wanted to  
show tagged item (music in this case) in a list (like done on  
delicous), sorted by the first time one tagged that item (and the view  
should group item by days). As there is no association for the first  
join, I can`t do that the easy way via :include (or passing an  
association to to :joins). Passing

:include =\> [:taggings =\> :tag],  
:joins =\> "SELECT MIN( taggings.created\_at ) AS first\_time\_tagged,  
music2.id AS id  
FROM taggings  
LEFT OUTER JOIN music AS music2 ON taggings.taggable\_id  
= music2.id  
GROUP BY music2.id  
)get\_first\_tagging ON music.id = get\_first\_tagging.id

seems to ingnore the include. So I am really wondering how a clever  
way to do that could look like. Currently I use:

@item = Music.find( :all, :include =\> [:taggings =\> :tag],  
:conditions =\>  
[“taggings.tagger\_id = ?”, @user],  
:order =\> [“taggings.created\_at  
DESC”])

In combinations with a method in the model to query the smallest time  
of all associated taggings. But that introduces the n+1 queries  
problem when called for n items in the view - not so nice…

Another solution would be to get the min of item.taggings (by the way,  
knows someone a comparison method for Time?), but I really wonder if  
there is no (native) way to do all that in the DB.

Cheers,  
Chris

---

<div class="post-metadata">

**Author:** ![bitrocker](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b782af/32.png) [@bitrocker](https://www.ruby-forum.com/u/bitrocker)\
**Post date:** [September 20, 2009, 4:25pm UTC](https://www.ruby-forum.com/t/find-with-join-over-aggregated/175358/2 "2009-09-20T16:25:42Z")

</div>

Well,

I could figure out hoe to do it: Using :joins (without :include) and  
the proper table aliases works…