Query Scope: Filter items, who got relations with specific condition

Hello everybody,

I'd like to solve the following problem: I'd like to create a query scope for my model called Ticket. This Ticket hasMany replies (Reply). And each reply can have a status.

Now I'd like to create a scope on the Ticket model, which should filter all tickets having more than 2 replies in the status UNREAD. In my case, the status itself is just an PHP-enum called Status.

This is my first attempt:

public function scopeEscalatedTickets(Builder $query): Builder
{
    return $query->has('replies', function (Builder $q){
        $q->whereNot('status', Status::READ);
    });
}

But now I'm stuck: How can I create the count-condition so this takes into account that I just want the tickets having more than 2 replies which do not have the Status::READ?

Thanks for your help :-)

ahoi

3 years ago

Level 5

My second thought about using something like

->withCount('replies')->having('replies_count', '>', 2)

does not work too, and inspecting the SQL-query I at least found out that withCount() really just counts all the related items and ignores other conditions.

Sergiu17

3 years ago

Level 60

@ahoi hello, I can't test this right now, but you could count(*) ( you will use selectRaw for this I guess ) then group by replies.ticket_id and then add having

try first in plain sql, then convert the query to eloquent

1 like

webrobert

3 years ago

Best Answer

Level 51

@ahoi

Second example here is count with a filter

https://laravel.com/docs/9.x/eloquent-relationships#counting-related-models

1 like

ahoi

3 years ago

Level 5

@webrobert

Yep, this was it.

The solution that works for me is:

public function scopeEscalatedTickets(Builder $query): Builder
{
    return $query
        ->withCount(
            [
                'replies as escalated_replies' => function (Builder $query) {
                    $query->whereNot('status', Status::READ);
                },
            ]
        )->having('escalated_replies', '>', 2);
}

1 like

Please or to participate in this conversation.