CODEWITHSUNDEEP
mysqlgroup-concat
cardflopper
cardflopper

Reputation: 1046

problems using GROUP_CONCAT in MYSQL

i have the following tables:

posts: id, title, date, content

tags: id, name

posts_tags: post_id, tag_id

I want to select the title with a list of related tags; the following query works nicely

SELECT p.title, GROUP_CONCAT( t.name ORDER BY t.name SEPARATOR ',' ) as tags_list
FROM posts AS p
JOIN posts_tags AS pt ON pt.post_id = p.id
JOIN tags AS t ON pt.tag_id = t.id
GROUP BY p.id

with this query i get something that looks like this

title             |  tags_list
==================|===============================
Old cinema        |  film,movies
cooking shows     |  cooking,food,kitchen,television
Epic War Films    |  history,movies,war
Art in France     |  art,france,history

when I want to only posts with a certain tag (for example "movies") I tried adding a where clause:

SELECT p.title, GROUP_CONCAT( t.name ORDER BY t.name SEPARATOR ',' ) as tags_list
FROM posts AS p
JOIN posts_tags AS pt ON pt.post_id = p.id
JOIN tags AS t ON pt.tag_id = t.id
WHERE t.name = 'movies'
GROUP BY p.id

i end up with this

title            |  tags_list
=================|===============================
Old cinema       |  movies
Epic War Films   |  movies

I got all the posts tagged "movies", but the problem is the "tags_list" column only shows "movies" in each row instead of including all the other tags associated with the post.

how can I fix this?

Upvotes: 1

Views: 993

Answers (2)

Stephan
Stephan

Reputation: 8090

The condition need to be put on the result of GROUP_CONCAT not on each row so as @AshwinMukhija suggested the condition needs to be in HAVING clause:

SELECT 
    p.title, 
    GROUP_CONCAT( t.name ORDER BY t.name SEPARATOR ',' ) as tags_list
FROM 
    posts AS p
JOIN posts_tags AS pt 
    ON pt.post_id = p.id
JOIN tags AS t 
    ON pt.tag_id = t.id
GROUP BY 
    p.id
HAVING
    FIND_IN_SET('movies',tags_list)

FIND_IN_SET returns true if the string movies is found in the comma separated list tags_list

Upvotes: 2

Achrome
Achrome

Reputation: 7821

You just need to shift the condition on your tag from the where clause to having

SELECT p.title, GROUP_CONCAT( t.name ORDER BY t.name SEPARATOR ',' ) as tags_list
FROM posts AS p
JOIN posts_tags AS pt ON pt.post_id = p.id
JOIN tags AS t ON pt.tag_id = t.id
GROUP BY p.id
HAVING t.name = 'movies'

Upvotes: 1

Related Questions