How to select posts and tags from DB?

Question

MySQL

For example, a selection of 10 posts, each post can have an arbitrary number of tags, for example 5. How to make a selection correctly? how to do it in this case, with one request or make additional requests when iterating over an array from the database?

Answer 1

I really like to ask this question at interviews)
The correct answer in my opinion is: depending on the workload.
1. The obvious quick solution - stupidly 3NF in the database, 1 request to get a list of posts, 1-2 more requests to get all the tags from all these posts, merge arrays in PL.
2. As the load appears, you can deal with various denormalizations (at least you can store tag id in a denormalized way, as a maximum, tag texts too).

Answer 2

Make an additional request
UPD:
Something like this:

$posts = $sql->get("SELECT `id`, `name` FROM `posts` WHERE 1");

foreach ($posts as $post) {
    echo $post->name;

    $tags = $sql->get("SELECT `id`, `name` FROM `post_tags` WHERE `post_id` = $post->id");

    foreach ($tags as $tag) {
        echo $tag->name;
    }
}