Search code examples
mysqlleft-joingroup-concat

GROUP CONCAT concats too much duplicate values when LEFT JOINing two different tables

I have a query that fetches posts where I LEFT JOIN two tables:

Categories and tags: LEFT JOIN to the linking table →　INNER JOIN to the category and tag names table.

LEFT JOIN wp_term_relationships AS term_link ON wpp.ID = term_link.object_id 
INNER JOIN wp_terms AS terms ON term_link.term_taxonomy_id = terms.term_id

Extra post details as post meta:

LEFT JOIN wp_postmeta AS postmeta ON wpp.ID = postmeta.post_id

Now. I want to show all categories in a list with commas, with the MySQL GROUP_CONCAT:

GROUP_CONCAT(terms.name SEPARATOR ', ') AS allcatname

However, the problem is, because of my 2nd LEFT JOIN to wp_postmeta, somehow all the categories get copied for each wp_postmeta record to be found. So my 'allcatname' looks like this:

drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drinks, drink

So that happened...
The reason I want to keep GROUP_CONCAT is because I need it for filtering through "HAVING". So I'm looking for a way to show only one category per category record that's linked to each post: Let the group concat show. E.g. "drinks, soda, cola" if it has those three categories.

Here is the full Query:

SELECT
    SQL_CALC_FOUND_ROWS
    wpp.ID, wpp.post_title, wpp.post_author,
    wpp.post_status, 
    GROUP_CONCAT(terms.name SEPARATOR ', ') AS allcatname
FROM wp_posts AS wpp 

LEFT JOIN wp_term_relationships AS term_link ON wpp.ID = term_link.object_id 
INNER JOIN wp_terms AS terms ON term_link.term_taxonomy_id = terms.term_id 
LEFT JOIN wp_postmeta AS postmeta ON wpp.ID = postmeta.post_id

WHERE wpp.post_type = 'post'
    AND wpp.post_warning <> 'no_image'
    AND wpp.post_status <> 'trash'
    AND wpp.post_status <> 'auto-draft'
    AND (post_title LIKE '%$search_string%' OR postmeta.meta_value LIKE '%$search_string%')
GROUP BY wpp.ID 
ORDER BY post_date DESC 
LIMIT 20
Solution

  • Use:

    GROUP_CONCAT(DISTINCT terms.name SEPARATOR ', ') AS allcatname

    Instead of:

    GROUP_CONCAT(terms.name SEPARATOR ', ') AS allcatname