How to get nested array from MySQL select with joins

Question

I have the models User, Post, Comment and Tag.

User creates Post.
Posts can have multiple Comment and Tag.

Each model has it's own table, so there are tables 'posts', 'comments' and 'tags'. Comments have a foreign key called 'post_id', while Tags have a many_to_many relation table called 'post_tags', in which there are two fields: 'post_id' and 'tag_id'.

I want to get a nested array like below:

Which MySQL queries should I run?
I suppose I need to alter the result with PHP to get my nested array. How?

Thanks a lot for your help :-)

    [0] => Array
    (
        [Post] => Array
            (
                [id] => 1
                [title] => First article
                [content] => aaa
                [created] => 2008-05-18 00:00:00
            )
        [Comment] => Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [post_id] => 1
                        [author] => Daniel
                        [email] => [email protected]
                        [website] => http://example.com
                        [comment] => First comment
                        [created] => 2008-05-18 00:00:00
                    )
                [1] => Array
                    (
                        [id] => 2
                        [post_id] => 1
                        [author] => Sam
                        [email] => [email protected]
                        [website] => http://example.net
                        [comment] => Second comment
                        [created] => 2008-05-18 00:00:00
                    )
            )
        [Tag] => Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [name] => Awesome
                    )
                [1] => Array
                    (
                        [id] => 2
                        [name] => Baking
                    )
            )
    )
    [1] => Array
    (
        [Post] => Array
            (...

We can't give you an SQL query without a database schema, so we can see where the data is coming from. This will be very difficult if possible at all and will involve some decoding of the resulting data on the server side, which may (probably will) be more computationally expensive than just running multiple queries. — DaveRandom
– DaveRandom, Commented Jul 18, 2012 at 22:08
I have updated the question to reflect your feedback. Furthermore it is no requirement to use just a single MySQL query. I just assumed that was best. Any solution with multiple queries is welcome. — preyz
– preyz, Commented Jul 19, 2012 at 8:18

Puggan Se · Accepted Answer · 2017-05-05 11:26:23Z

4

You better of doing 3 queries.

first fetch post (, and left join the user if you need it), and store them like:
```
$list[$row['post_id']]['Post'] = $row;
```

then fetch all post-comments and store them as

$list[$row['post_id']]['Comment'][$row['comment_id']] = $row;

then fetch all post-tags

$list[$row['post_id']]['Tags'][$row['tag_id']] = $row;

that far more effective than trying to use a single query, as a single query going to end up sending the same data multiple times

edited May 5, 2017 at 11:26

answered Jul 18, 2012 at 22:30

Puggan Se

5,8462 gold badges25 silver badges51 bronze badges

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

3 Comments

Renish Khunt Over a year ago

it is possible using single query?

Puggan Se Over a year ago

@RenishKhunt, you get alot of dublicate content from the databse, and have to handle it in php, it's slower and an unessesery mess.

William Over a year ago

I also wanted to do it with one query, but getting all the members and looping through results is much more efficient

Collectives™ on Stack Overflow

How to get nested array from MySQL select with joins

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related