Find related items, which have a count-condition-CodePudding

I'd like to create a query scope for my model called Ticket. This Ticket hasMany replies (Model: Reply). And each reply can have a status (Enum 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.

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?

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.

Thanks for your help :-)

CodePudding user response：

To filter tickets based on the number of replies with a given status, you can use the having method on the Builder instance in your scope. This method allows you to add conditions to the query based on the values of aggregate functions, such as the number of replies.

Here is an example of how you can modify your scope to only return tickets that have more than 2 replies with the UNREAD status:

    public function scopeEscalatedTickets(Builder $query): Builder
{
    return $query->has('replies', function (Builder $q) {
        $q->where('status', Status::UNREAD);
    })->having('replies_count', '>', 2);
}

This scope will first filter the tickets to only include those that have at least one reply with the UNREAD status, using the has and where methods. Then, it will use the having method to only include tickets that have more than 2 replies that match the condition.

Note that this scope assumes that you have a replies_count column in the tickets table, which holds the number of replies for each ticket. You can generate this column using the withCount method on the Builder instance, like this:

$tickets = Ticket::withCount('replies')->get();

This will attach a replies_count attribute to each ticket in the $tickets collection, which you can use in your scope to filter the tickets based on the number of replies.

I hope this helps! Let me know if you have any other questions.

CodePudding user response：

You can use the whereHas() it will check if the model has the given relationship with a condition :

$query->whereHas('replies', function() {
    $query->where('status', Status::UNREAD);
}, '>', 2);

now it will query all ticket that have more than 2 replies and with Status::UNREAD