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