CODEWITHSUNDEEP
phpmysql
Gocha
Gocha

Reputation: 179

Get X most popular tags from query

$query = "SELECT tag.name, tag.id
        FROM #__k2_tags as tag
        LEFT JOIN #__k2_tags_xref AS xref ON xref.tagID = tag.id
        WHERE xref.itemID IN (".implode(',', $IDs).")
        AND tag.published = 1";

in xref.tagID is tag ids eg: tagID:42 is 100 times, tagID:15 is 90 times, tagID:56 is 10 times, I want show first 42 then 15 then 56 (I want cound how many tag is in xref.tagID and order)

Upvotes: 0

Views: 71

Answers (2)

Andrea Mauro
Andrea Mauro

Reputation: 842

Try with this:

$query = "
SELECT *
FROM (
    SELECT tag.name, tag.id, count(xref.id) AS tagCount
    FROM #__k2_tags as tag
    LEFT JOIN #__k2_tags_xref AS xref ON xref.tagID = tag.id
    WHERE xref.itemID IN (".implode(',', $IDs).") AND tag.published = 1
    GROUP BY tag.name, tag.id
)
ORDER BY tagCount";

The GroupBy in the subquery allows you to count the amount of tags occurrences for each tag type.
The main query is required to sort by the aggregate value tagCount.

Upvotes: 1

Iron
Iron

Reputation: 2

You can try with group by function : 

"SELECT  tag.id, count(*)
        FROM #__k2_tags as tag
        LEFT JOIN #__k2_tags_xref AS xref ON xref.tagID = tag.id
        WHERE xref.itemID IN (".implode(',', $IDs).")
        AND tag.published = 1
GROUP BY tag.id";

Upvotes: 0

Related Questions