2

I have the following query that is grabbing entries, but I would also like a 'categories' column that will show a pipe seperated list of the categories assigned to each entry.

I have tried using CONCAT_WS as below, but the results just duplicate each entry for every category assigned to that entry. I was wondering if someone could tell me where I'm going wrong?

SELECT exp_channel_data.entry_id AS entry_id, exp_channel_titles.title AS title, exp_channel_titles.url_title AS url_title, CONCAT_WS('|',cp.cat_id) AS categories
FROM exp_channel_data
LEFT JOIN exp_channel_titles ON exp_channel_titles.entry_id = exp_channel_data.entry_id
LEFT JOIN exp_category_posts AS cp ON exp_channel_data.entry_id = cp.entry_id
LIMIT 100
5

I believe this is what you're looking for:

SELECT exp_channel_data.entry_id AS entry_id, exp_channel_titles.title AS title, exp_channel_titles.url_title AS url_title, GROUP_CONCAT(cp.cat_id SEPARATOR '|') AS categories
FROM exp_channel_data
LEFT JOIN exp_channel_titles ON exp_channel_titles.entry_id = exp_channel_data.entry_id
LEFT JOIN exp_category_posts AS cp ON exp_channel_data.entry_id = cp.entry_id
GROUP BY entry_id
LIMIT 100
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.