How to design tables and build relationships in Laravel Eloquent for this type of data?

Question

Database design

Hello.

There is a database containing users, books and user comments on books, served by Laravel.

There are the following tables:
users , with fields id, name
books , with fields id, name
comments , with fields id, user_id, book_id,

comment to which he left a comment and for each book only his comments.

Something like this or a little more complicated (without maps on the received models with the results of other queries by hand):

User::whereId(1)->with(['books', 'books.comments'])->get();

To end up with something like:

[
   'id' => 1,
   'name' => 'Иван',
   'books' => [
      [
         'id' => 'book-1',
         'name' => 'Три поросёнка',
         'comments' => [
            [
               'id' => 'comment-1',
               'user_id' => 1,
               'book_id' => 'book-1',
               'comment' => 'Класс!',
            ],
            [
               'id' => 'comment-2',
               'user_id' => 1,
               'book_id' => 'book-1',
               'comment' => 'Очень глубоко...',
            ],
         ],
      ],
    [
         'id' => 'book-2',
         'name' => 'Букварь',
         'comments' => [
            [
               'id' => 'comment-3',
               'user_id' => 1,
               'book_id' => 'book-2',
               'comment' => 'Пишу комментарий',
            ],
            [
               'id' => 'comment-4',
               'user_id' => 1,
               'book_id' => 'book-2',
               'comment' => 'Только сейчас дошел посыл автора',
            ],
         ],
      ],
   ],
]

How to implement it? What relationships like hasManyThrough, etc. need to be done?

Answer 1

Adding a relation to User

public function booksWhereHasMyComments()
{
     return $this->hasManyThrough(Book::class, Comment::class);
}

Request

$user->booksWhereHasMyComments()
    ->with(['comments' => function ($query) use ($user) {
        $query->where('user_id', $user->id);
    }])
    ->get();