Prevent duplicated events returned from SQL query
I was working on a SQL query at work to find related events to given event where they have one or more similar artists.
Here’s SQL query.
SELECT
p.ID AS id,
p.post_title AS title,
meta.meta_value AS startTime,
artist_tr.term_taxonomy_id AS artistId
FROM
wp_posts AS p
INNER JOIN wp_postmeta AS meta ON p.ID = meta.post_id
AND meta.meta_key = 'start_time'
INNER JOIN wp_term_relationships AS artist_tr ON p.ID = artist_tr.object_id
INNER JOIN wp_term_taxonomy AS artist_tax ON artist_tr.term_taxonomy_id = artist_tax.term_id
AND artist_tax.taxonomy = 'artist'
WHERE
p.post_type = 'event'
-- current event id
AND p.ID != 74
AND p.post_status = 'active'
-- (11, 10, 9, 8) are artists ids of current event
AND artist_tr.term_taxonomy_id IN(11, 10, 9, 8)
ORDER BY
meta.meta_value ASC
And this is the output of above SQL query. We have a problem as duplicated events are returned. The current query retrieves duplicate events because it checks each artist ID individually. For example, Event 1
has artist ID of 8
and 10
, so it’s been found twice.
id | title | startTime | artistId |
---|---|---|---|
79 | Event 1 | 1713296400 | 8 |
79 | Event 1 | 1713296400 | 10 |
81 | Event 3 | 1714419660 | 8 |
77 | Event 5 | 1714506000 | 8 |
77 | Event 5 | 1714506000 | 9 |
We can add a GROUP BY
before ORDER BY
to group by post id.
SELECT
p.ID AS id,
p.post_title AS title,
meta.meta_value AS startTime,
artist_tr.term_taxonomy_id AS artistId
FROM
wp_posts AS p
INNER JOIN wp_postmeta AS meta ON p.ID = meta.post_id
AND meta.meta_key = 'start_time'
INNER JOIN wp_term_relationships AS artist_tr ON p.ID = artist_tr.object_id
INNER JOIN wp_term_taxonomy AS artist_tax ON artist_tr.term_taxonomy_id = artist_tax.term_id
AND artist_tax.taxonomy = 'artist'
WHERE
p.post_type = 'event'
-- current event id
AND p.ID != 74
AND p.post_status = 'active'
-- (11, 10, 9, 8) are artists ids of current event
AND artist_tr.term_taxonomy_id IN(11, 10, 9, 8)
GROUP BY p.ID -- Add a group by here
ORDER BY
meta.meta_value ASC
And this is the result coming back.
id | title | startTime | artistId |
---|---|---|---|
79 | Event 1 | 1713296400 | 8 |
81 | Event 3 | 1714419660 | 8 |
77 | Event 5 | 1714506000 | 8 |
This works, but what if I want to keep those artists ids? We can add a GROUP_CONCAT
.
SELECT
p.ID AS id,
p.post_title AS title,
meta.meta_value AS startTime,
-- artist_tr.term_taxonomy_id AS artistId
GROUP_CONCAT(artist_tr.term_taxonomy_id SEPARATOR ',') AS artistIds
FROM
wp_posts AS p
INNER JOIN wp_postmeta AS meta ON p.ID = meta.post_id
AND meta.meta_key = 'start_time'
INNER JOIN wp_term_relationships AS artist_tr ON p.ID = artist_tr.object_id
INNER JOIN wp_term_taxonomy AS artist_tax ON artist_tr.term_taxonomy_id = artist_tax.term_id
AND artist_tax.taxonomy = 'artist'
WHERE
p.post_type = 'event'
AND p.ID != 74
AND p.post_status = 'active'
AND artist_tr.term_taxonomy_id IN(11, 10, 9, 8)
GROUP BY p.ID
ORDER BY
meta.meta_value ASC
And now we have all the artists ids returned.
id | title | startTime | artistIds |
---|---|---|---|
79 | Event 1 | 1713296400 | 8,10 |
81 | Event 3 | 1714419660 | 8 |
77 | Event 5 | 1714506000 | 8,9 |