Page 3 of 3

Re: Flexible finder methods

Posted: Fri May 15, 2009 1:43 pm
by inghamn
Heh - hopefully, we're not getting too far afield from the original point of this thread :)
allspiritseve wrote:

Code: Select all

 
SELECT rt.* FROM tags AS lt
INNER JOIN media_tags AS lmt ON (lt.id = lmt.tag_id)
INNER JOIN media_tags AS rmt ON (lmt.media_id = rmt.media_id)
INNER JOIN tags AS rt ON (rmt.tag_id = rt.id)
WHERE rt.id IN (5, 16)
 
That query doesn't account for all the other parameters that could have been used in the inital Media query. A typical media collection's SQL would look like:

Code: Select all

 
SELECT m.id AS id FROM media m
INNER JOIN media_tags mt ON m.id=mt.media_id AND mt.tag_id IN (15,6)
WHERE roll_id=5 AND year(date)>1999
GROUP BY m.id HAVING count(*)=2 ORDER BY id
 
An SQL query to get at all the tags in that set would have to account for all imagineable filters, like the roll_id and year as well as any tags chosen by the user. When it comes time to populate the Tag collection with all the "related" tags for the media collection, all I've got to go on is the MediaList itself.

And this is just for columns of the media table. The Media query could also have joined other, as-of-yet unmentioned tables as well.

Code: Select all

 
SELECT m.id AS id FROM media m
LEFT JOIN users u ON m.user_id=u.id
INNER JOIN media_tags mt ON m.id=mt.media_id AND mt.tag_id IN (15,6)
WHERE roll_id=5 AND year(date)>1999 AND u.username='inghamn'
GROUP BY m.id HAVING count(*)=2 ORDER BY id
 
It made my brain hurt trying to parse out all the possibile parts of the media SQL. I wasn't able to get everything accounted for.

Using the complete media SQL as a subquery seemed a whole lot easier.

Code: Select all

 
SELECT DISTINCT
  t.id AS id
FROM
  tags t
  JOIN media_tags mt ON t.id=mt.tag_id
  JOIN (
        SELECT m.id AS id FROM media m
        INNER JOIN media_tags mt ON m.id=mt.media_id AND mt.tag_id IN (15,6)
        WHERE roll_id=5 AND year(date)>1999
        GROUP BY m.id HAVING count(*)=2 ORDER BY id
   )related_media_ids ON mt.media_id = related_media_ids.media_id
 

Once we've got a collection, and we want some other collection to be an INTERSECT of the first collection, we have to reverse-engineer the first collection somehow....or just do the subquery.