I'm trying to find some records based on the tags they have. In order to find out which tags a record has i add them to the result using a subquery. To find out which results should be returned i added a having statement on the end of the query. But something tells me this is not the best way.
SELECT e.id, e.title, e.text, e.introduction,
UNIX_TIMESTAMP(e.starts_on) AS starts_on,
UNIX_TIMESTAMP(e.ends_on) AS ends_on,
m.id AS meta_id,
m.url,
cm.title AS category_title,
cm.url AS category_url,
CONCAT(
",",
(
SELECT GROUP_CONCAT(t.id)
FROM modules_tags AS mt
JOIN tags AS t ON t.id = mt.tag_id
WHERE mt.other_id = e.id
),","
) AS tags_search
FROM event_posts AS e
INNER JOIN meta AS m ON e.meta_id = m.id
LEFT JOIN event_categories AS c ON e.category_id = c.id
LEFT JOIN meta AS cm ON c.meta_id = cm.id
LEFT JOIN modules_tags AS mt ON mt.other_id = e.id
LEFT JOIN tags AS t ON t.id = mt.tag_id
WHERE 1 HAVING tags_search LIKE '%,5,%' AND tags_search LIKE '%,6,%'
