How to filter rows in query properly?

Question

I want to filter records with relation which has another relation with conditions and with count > 0

Code:

    $classClass = ClassClass::query()
        ->with('parent')
        ->with('children', function (BelongsTo $query) {
            $query
                ->withCount(['serialNumbers' => function($query) {
                    $query
                        ->whereNull('serial_number_parent_id')
                        ->whereNull('inherit_class_for_serial_number_id');
                }]);
        })
        ->where('parent_class_id','=', $class->id)
        ->orderBy('id')
        ->get();

as you see, i'm getting classClass record which has related children class record with serialNumbers and condition. I want to filter count of serialNumbers in this sql generating query class, how i can do that?

PS i want to add having {children}->serial_numbers_count > 0 statement

Answer 1

Found a solution:

$classClass = ClassClass::query()
        ->with('parent')
        ->whereHas('children.serialNumbers', function (Builder $query) {
            $query
                ->whereNull('serial_number_parent_id')
                ->whereNull('inherit_class_for_serial_number_id');
        }, '>=',1)
        ->where('parent_class_id','=', $class->id)
        ->orderBy('id')
        ->get();

Answer 2

There's a plenty of ways to clean your code:

I'll go with scopes, my favorite to clean my code, you can tweak as you like:

Children.php

//Create a scope
function scopeWithSerialNumbersCount($query, $count = null){
    $query->withCount(['serialNumbers' => function ($query) {
        $query
            ->whereNull('serial_number_parent_id')
            ->whereNull('inherit_class_for_serial_number_id');
    }]);

    if($count > 0){
        $query->having('serial_numbers_count', '>=', $count);
    }
}

Then you can call it like that

$classClass = ClassClass::query()
        ->with('parent')
        ->with('children', function ($query) {
            $query->withSerialNumbersCount(5);
            // Or if don't want having
            $query->withSerialNumbersCount();
        })
        ->where('parent_class_id', '=', $class->id)
        ->orderBy('id')
        ->get();