Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

gurinder's avatar

Get paginated posts and also get status count in one query

Hello

I need a help with fetching paginated collection of posts with count of status in one query.

My post index query is

public function index(PostFilter $filter)
{
    return Post::with(['author', 'category', 'tags'])
        ->withTrashed()
        ->filter($filter)
        ->paginate(request()->perPage ? request()->perPage : 15);
}

Post Status count query is:

public function getPostsCountByStatus()
{
    return [
        'draft_count' => Post::draft()->count(),
        'published_count' => Post::published()->count(),
        'trashed_count' => Post::onlyTrashed()->count(),
    ];
}

Post Model is

class Post extends Model
{

    protected $dates = ['publish_at', 'deleted_at'];

    protected $withCount = ['comments'];

    public function comments()
    {
        return $this->hasMany(Comment::class)->with('owner');
    }

    public function scopePublished($query)
    {
        return $query->where('publish_at', '<', Carbon::now())
          ->where('deleted_at', null);
    }

    public function scopeDraft($query)
    {
        return $query->where('publish_at', null)
         ->where('deleted_at', null);
    }

    public function scopeUserPosts($query)
    {
        return $query->where('author_id', '=', auth()->id());
    }

    public function scopeFilter($builder, QueryFilter $filter)
    {
        return $filter->apply($builder);
    }

}

I am trying to fetch all the post filtered by given query and also require to fetch the post count based on status of the posts which is determined by model Scope as you can see above in post model.

Is there any way i can fetch it all together with one query

Thanks fo rany help

0 likes
0 replies

Please or to participate in this conversation.