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
