raktim135's avatar

Fetch all post along with tag details without intermediate table or model

Hello developers, I have 2 tables namely posts and tags, column as below.

posts: id, title, description, tag_ids tags: id, tag_name

I have saved multiple id in comma separated format in tag_ids column. Now I want to fetch all the posts along with tags details from tags table in array format with each post. So, can anyone help me out.

Note: I can't create a intermediate table for it. So, solution must be without it.

0 likes
2 replies
Snapey's avatar

Can you reorganise your data so that you DONT save the tags as a comma separated list, and instead use a pivot table?

How many tags do you expect to have in total?

tykus's avatar

You can achieve it, but an intermediate table will be more performant:

SELECT  posts.id,
        posts.title,
        posts.description,
        GROUP_CONCAT(tags.name ORDER BY tags.id) tags
FROM    posts
        INNER JOIN tags ON FIND_IN_SET(tags.id, posts.tag_ids) > 0
GROUP   BY posts.id

Please or to participate in this conversation.