How to get the number of entities related to relationships?

Question

Laravel

There are links, for example:
- Posts
- Categories of posts
- Users

Let's say a moderator can give access to users to publish their posts in certain categories.

Database

categories
- id
- name

users
- id
- name

user_categories
- user_id
- category_id

posts
- id
- user_id
- category_id
- title
- description

How to get a list of users through links, for each one to display a list of categories with the number of posts related to this user?

I settled on this:

$query = Users::query()->with([
    'categories' => function (BelongsToMany $relation) {
        $relation->withCount(['posts' => function (Builder $builder) {
            // ...
        }]);
    }
]);

How to be further? Raw requests do not want to write.

Answer 1

Decision

$query = Users::query()->with([
    'categories' => function (BelongsToMany $relation) {
        $relation->withCount(['posts' => function (Builder $builder) {
            $builder->whereRaw("`post`.`user_id` = `user_categories`.`user_id`");
        }]);
    }
]);

Answer 2

Not sure, but can be added to the model Categories

App\Category.php

/**
 * Получить все посты для пользователя.
 */
public function usersPosts()
{
    return $this->hasManyThrough('App\User', 'App\Post');
}

interpretation from documentation:

To execute this query, Eloquent looks up the catalog_id in the posts intermediate table. After matching post IDs are found, they are used in the query against the users table.

Answer 3

$users = User::with(['categories' => function ($query) {
         $query->withCount(['posts' => function ($q) {
          $q->whereColumn('posts.user_id', 'user_categories.user_id');
          }]);
       }])->get();