I'm not much of a database guru so I need some help on a query I'm working on. In my photo community project I want to richly visualize tags by not only showing the tag name and counter (# of images inside them), I also want to show a thumb of the most popular image inside the tag (most karma).
The table setup is as follow:
Image table holds basic image metadata, important is the karma field
Imagefile table holds multiple entries per image, one for each format
Tag table holds tag definitions
Tag_map table maps tags to images
In my usual trial and error query authoring I have come this far:
SELECT * FROM
(SELECT tag.name, tag.id, COUNT(tag_map.tag_id) as cnt
FROM tag INNER JOIN tag_map ON (tag.id = tag_map.tag_id)
INNER JOIN image ON tag_map.image_id = image.id
INNER JOIN imagefile on image.id = imagefile.image_id
WHERE imagefile.type = 'smallthumb'
GROUP BY tag.name
ORDER BY cnt DESC)
as T1 WHERE cnt > 0 ORDER BY cnt DESC
[column clause of inner query snipped for the sake of simplicity]
This query gives me somewhat what I need. The outer query makes sure that only tags are returned for which there is at least 1 image. The inner query returns the tag details, such as its name, count (# of images) and the thumb. In addition, I can sort the inner query as I want (by most images, alphabetically, most recent, etc)
So far so good. The problem however is that this query does not match the most popular image (most karma) of the tag, it seems to always take the most recent one in the tag.
How can I make sure that the most popular image is matched with the tag?
