This is the query that I am using for the result above:
SELECT wp.id, wp.post_title, tt.taxonomy, t.name
FROM wp_posts wp
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms t ON t.term_id = tt.term_id
WHERE tt.taxonomy like 'pa_%'
and now I want to concat values of name where the taxonomy is same. So I changed query by adding group_concat:
SELECT wp.id, wp.post_title, tt.taxonomy, GROUP_CONCAT(t.name)
FROM wp_posts wp
INNER JOIN wp_term_relationships r ON wp.ID = r.object_id
INNER JOIN wp_term_taxonomy tt ON r.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN wp_terms t ON t.term_id = tt.term_id
WHERE tt.taxonomy like 'pa_%'
It ended up like below:
As you see the result is not correct which means it takes all values where i want only the same taxonomy
How I can achieve this by concatenating proper values with related taxonomy?
CodePudding user response：
This is aggregation -- because you want one row per group in the result set. You also need a way to aggregate strings, which MySQL provides in the form of group_concat():
SELECT wp.id, wp.post_title, tt.taxonomy,
GROUP_CONCAT(t.name)
FROM wp_posts wp JOIn
wp_term_relationships r
ON wp.ID = r.object_id JOIn
wp_term_taxonomy tt
ON r.term_taxonomy_id = tt.term_taxonomy_id JOin
wp_terms t
ON t.term_id = tt.term_id
WHERE tt.taxonomy like 'pa_%'
GROUP BY wp.id, wp.post_title, tt.taxonomy;
Note that the unaggregated columns are in the GROUP BY.
