[FIXED] How to count data use relationship in laravel?

Issue

Table user example :

id | name | status | first_name | last_name | create_at

Table post example :

id | title | name | description | user_id | status | create_at

In model user:

public function posts()
{
    return $this->hasMany(Post::class);
}

In model post:

public function users()
{
    return $this->belongsTo(Users::class);
}

In the post table database, there is a status column with three values ​​of 1, 2, 3, now I want to count how many status = 1, and how many status = 2, 3

I have two ways to handle that is to use a relationship or call the post model to handle it In UserController.php:

// The best way to try is relationship
    $user = User::select(['id', 'name'])
              ->with(['posts:user_id'])
              ->where('type', 2)
              ->get();

Or:

$user = User::select(['id', 'name'])
          ->where('type', 2)
          ->get();
    foreach ($user as $val) {
      $statusPublic[] = Posts::where('user_id', $val['id'])->where('status', '=', 1)->count();
      $statusPrivate[] = Posts::where('user_id', $val['id'])->whereIn('status', [2, 3])->count();
    }

My problem is that in the posts table it has 300,000 items . If you handle the status count like that, it's very slow. Sometimes up to 20s. Is there any way to improve? Thanks

Solution

You need to use the withCount.

In model user

public function posts_status_one()
{
    return $this->hasMany(Post::class)->where('status', '=', 1);
}

public function posts_status_other()
{
    return $this->hasMany(Post::class)->whereIn('status', [2, 3]);
}

Now in your query

$user = User::select(['id', 'name'])
    ->withCount(['posts_status_one', 'posts_status_other'])
    ->where('type', 2)
    ->get();

By using withCount the n + 1 query, will reduce to 3 query.

Answered By - parth