How to count data use relationship in laravel?

Table user example :

id | name | status | first_name | last_name | create_at

Table post example :

id | title | name | description | user_id | 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

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2WCiXzH
via IFTTT