0

I have two tables Posts, categories. Here in the posts table I stored the category values as comma separated string like this 5,8,23,7. While displaying the posts, I just want to show the post categories as comma separated like this Flower, Birds, Animals. So I tried some queries nothing helped me to get it. The Posts Table Example.

ID  Post title      categories
3   Example Post     5,7,23,8

And the Categories Table will be like this

ID   name  
5    Flowers
7    Animals
8    Birds
23   Naturals

And I want result like this 

ID  Post Tile        Category
3   Example Post     Flowers, Animals, Birds

For that I tried this query but didn't help me to get it .

SELECT post.ID, post.Post_title, (SELECT cat.name FROM Categories as cat WHERE cat.ID IN (post.category)) AS Categories FROM Posts as post

And it returns only one category, it retrieves the first category name only.

Scuzzy
  • 12,186
  • 1
  • 46
  • 46
Kvvaradha
  • 732
  • 1
  • 13
  • 28
  • 1
    Storing multiple categories in a string like that will make these types of queries hard. One consideration, not entirely related to how to make this query work, would be to make a third table with schema {post ID, category ID}, linking posts to categories. – jackarms Aug 15 '17 at 04:34
  • 1
    you should learn more about many-to-many case, check this [implement table relationship](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) – raksa Aug 15 '17 at 04:38
  • Far better than the accepted answer is to fix your schema – Strawberry Aug 15 '17 at 06:47

3 Answers3

1

If you simply must use that schema, you could try something like this:

select P.ID, P.Title, (
       select group_concat(C.name SEPARATOR ', ')
       from Categories C
       where LOCATE(CONCAT(C.ID, ','), P.categories) > 0
          or LOCATE(CONCAT(', ', C.ID), P.categories) > 0
) as categories
from Post P;

It's hacky because in a comma separated list either a value occurs before a comma or after a comma, taking into account values at the beginning or end of the list. You can't just do a straight substring, because otherwise you'll get a category ID of 5 matched to a 'categories' value of '1, 2, 555'.

jackarms
  • 1,343
  • 7
  • 14
0

I believe you can use group_concat? Just join the Categories table and group_concat the name group_concat(name)

as for the JOIN try to use find_in_set(Categories.ID, Post.ID) > 0

Thou this approach may not work if the comma delimited Category ID has space i.e. 1, 2 etc...But if you are saving it accurately this may work.

Learning
  • 163
  • 1
  • 11
0

EDIT: Updated to consider the fact that Posts.categories is a CSV value.

You need to use the GROUP_CONCAT() function, and also the trick posted in SQL split comma separated row in order to split the JOIN CSV and then create the output CSV:

SELECT
    Posts.ID,
    Posts.Post_title,
    GROUP_CONCAT(Categories.name SEPARATOR ',') AS `Category`
FROM Posts
INNER JOIN Categories
    ON Categories.ID IN (
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(Posts.categories, ',', n.n), ',', -1) value
        FROM (
            SELECT a.N + b.N * 10 + 1 n
            FROM
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
            ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
            ORDER BY n
        ) n
        WHERE n.n <= 1 + (LENGTH(Posts.categories) - LENGTH(REPLACE(Posts.categories, ',', '')))
        ORDER BY value
    )
GROUP BY
    Posts.ID,
    Posts.Post_title

Fiddle: http://sqlfiddle.com/#!9/b1ddc9/4

e_i_pi
  • 4,590
  • 4
  • 27
  • 45
  • 1
    How does `ON Categories.ID = Posts.category` work? I believe OP is storing post categories all together as a string – jackarms Aug 15 '17 at 04:36
  • Thanks for the query. But It results the same thing, like first category it shows. – Kvvaradha Aug 15 '17 at 04:43
  • Sorry, didn't see the schema of `Posts`, will update my answer in a tic – e_i_pi Aug 15 '17 at 04:44
  • @Kvvaradha as already pointed out by jackarms a straight = on JOIN would not work. – Learning Aug 15 '17 at 04:45
  • This is a bad design by the way, it violates 1NF I believe, you really should have a bridging table between `Posts` and `Categories`, not a `categories` column in `Posts` – e_i_pi Aug 15 '17 at 04:45
  • @e_i_pi,its bad design. But it kept stored more datas in it. So I can get the things like this. – Kvvaradha Aug 15 '17 at 04:47
  • Also, please read this Wikipedia article on associative entities - it makes more sense to store this "bridging" or "junction" data in it's own table. https://en.wikipedia.org/wiki/Associative_entity – e_i_pi Aug 15 '17 at 04:59
  • Instead of IN I think JOIN is better in this case? – Learning Aug 15 '17 at 05:25
  • If we're looking at best case (i.e. performant) querying, I stick to my original suggestion of junction tables. You could change it to a `JOIN`, but you could also store your data 3NF – e_i_pi Aug 15 '17 at 05:36
  • Agreed, but I'm talking of the current query that was provided. – Learning Aug 15 '17 at 05:38
  • Actually, I can't see how another `JOIN` would work here. The `IN` clause is the join predicate for `Categories`, and the join against `Categories` is needed as it forms part of the `SELECT` cohort. The only other option I can see is a `CROSS JOIN` against `Categories` and changing the `IN` clause to a `WHERE EXISTS` - I would question the semantics of that. Do you have a fiddle you can post demonstrating what you mean? – e_i_pi Aug 15 '17 at 05:44
  • @e_i_pi it worked like charm. Also you have given a perfect code with my tables. Thank you so much – Kvvaradha Aug 15 '17 at 06:00
  • Yes cross join its basically the same, with slightly different. Anyway your answer is already accepted maybe no need to fiddle? – Learning Aug 15 '17 at 06:13