So this query works beautifully:

Post::where('id', $id)
    ->with([
        'comments' => function($query) {
           $query->where('marked_as', 'active');
        },
    ])
    ->withCount([
        'comments as comments_active_count' => function ($q) {
            $q->where('comments.marked_as', 'active');
        },
        'comments as comments_not_active_count' => function ($q) {
            $q->where('comments.marked_as', 'not_active');
        },
    ])
    ->firstOrFail();

But next to getting the count separately, I also like to get the comments collection separately for each status, like so (this doesn't work):

Post::where('id', $id)
    ->with([
        'comments as comments_active' => function($query) {
           $query->where('marked_as', 'active');
        },
        'comments as comments_not_active' => function($query) {
           $query->where('marked_as', 'not_active');
        },
    ])
    ->withCount([
        'comments as comments_active_count' => function ($q) {
            $q->where('comments.marked_as', 'active');
        },
        'comments as comments_not_active_count' => function ($q) {
            $q->where('comments.marked_as', 'not_active');
        },
    ])
    ->firstOrFail();

Is this possible?

CodePudding user response：

You cannot give an alias for with Instead, you can create 2 more relationships

public function active_comments()
{
    return $this->hasMany(Comment::class)->where('marked_as', 'active');
}

public function inactive_comments()
{
    return $this->hasMany(Comment::class)->where('marked_as', 'not_active');
}

and use

->with([ 'active_comments', 'inactive_comments' ])

and

->withCount([ 'active_comments', 'inactive_comments' ])