MySQL Combining data from multiple tables (foreign key) into an array

Question

What I'm trying to accomplish is query the data for all categories in the categories table, but also add a "posts" property that lists out the IDs of all posts within that category into an array.

Database tables:

'categories' table
+---------+----------+
|      id | title    |
+---------+----------+
|     100 | "categ1" |
|     101 | "categ2" |
|     102 | "categ3" |
|     103 | "categ4" |
+---------+----------+

'posts' table
+---------+----------+----------+
|      id | title    | category |
+---------+----------+----------+
|       1 | "abc"    | 100      |
|       2 | "def"    | 101      |
|       3 | "ghi"    | 100      |
|       4 | "jkl"    | 102      |
+---------+----------+----------+

Output goal: (json_encode, manually add 'categories' to top-level)

{
    "categories": [
        {
            "id": 100,
            "title": "categ1",
            "posts": [1, 3] (Post IDs of those in category 10)
        }
        {
            "id": 102,
            "title": "categ2",
            "posts": [2]
        }
        {
            "id": 103,
            "title": "categ3",
            "posts": [4]
        }
        {
            "id": 104,
            "title": "categ4",
            "posts": []
        }
    ]
}

While the basic query of getting the categories is easy, I can't figure out a way to generate the posts property based off the posts table and the posts category/category id relationship.

Can provide any more information if anything is unclear.

sgeddes · Accepted Answer · 2014-03-05 05:03:50Z

1

If I'm understanding your question correctly, it sounds like you're looking for GROUP_CONCAT():

select c.id, c.title, group_concat(p.id) posts
from categories c
  left join posts p on c.id = p.category
group by c.id

SQL Fiddle Demo

answered Mar 5, 2014 at 5:03

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Graham Over a year ago

Ah yeah this was exactly what I was looking for (short of the array, but seems I'll have to manually explode it anyways so no issue there). Thanks!

Collectives™ on Stack Overflow

MySQL Combining data from multiple tables (foreign key) into an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related