MySQL：请求使用Array创建JSON对象

Question

Ok, I have a simple question... I have a database with two tables :

Articles (id, content, title, date)

Comments (id_article, username, content)

And I want to obtain a JSON array like this :

[
    {
    "id": "5785634a87c5a0075bf211de",
    "title": "Elita",
    "content": "Commodo ea enim dolor enim adipisicing ut mollit .",
    "date":"15July2016"
    "comments": [
        {
        "username": "Tran",
        "content": "Ea aliqua sit fugiat adipisicing."
        },
        {
        "username": "Noreen",
        "content": "Commodo qui ea nulla est officia. non."
        },
        {
        "username": "Gilliam",
        "content": "Esse cupidatat adipisicing sit minim."
        }
    ]
    }
]

So my question ... Is it possible with only one request? :) Thanks you

Answer 1

You can LEFT JOIN to get all the data you need, when iterating over it you just have to take care about how you handle which data.

$lastArticleId = null;
$comments = [];
$elements = [];
$sql = 'SELECT Articles.*, Comments.content AS comment, Comments.username FROM Articles LEFT JOIN Comments ON Articles.id = Comments.id_article';
$result = $pdo->query($sql);
foreach ($result->fetchAll(PDO::FETCH_OBJ) as $dataset) {
    if($lastArticleId !== $dataset->id){
        $lastArticleId = $dataset->id;
        $elements[$lastArticleId] = [
            'id'       => $dataset->id,
            'title'    => $dataset->title,
            'content'  => $dataset->content,
            'date'     => $dataset->date,
            'comments' => [],
        ];
    }
    $comments[$lastArticleId][] = [
        'content' => $dataset->comment,
        'username' => $dataset->username,
    ];
}
foreach ($elements as $key => $article) {
    $article['comments'] = $comments[$key];
    $fullData[] = $article;
}
echo json_encode($fullData);

But: Just because you can, doesn't mean you should.

Splitting this task in two queries is a lot easier to write and to read. So if you later for some reason need to touch this piece of code (or someone else) he will be glad you chose the 2 query approach.

Also it is good practice to split tasks, so ideally you would create methods in your database-access-layer getAllArticles() and another one getCommentsByArticleId($id_article). This way you could fetch all the comments, without having to load the whole article.