Check if all ancestors fulfill condition

[jacobmllr95]

I struggle to find a proper solution to only include models wehere all it's ancestors fulfill a certain condition.
Simplified, I have a Page model which has an active column. I now wan't to get all pages where all parent pages are active.

I came up with the following query, which works:

$activePages = Page::query()
    ->where('active', true)
    ->withCount([
        'ancestors as __total_ancestors_count',
        'ancestors as __active_ancestors_count' => fn($query) => $query->where('active', true),
    ])
    ->havingRaw('__total_ancestors_count = __active_ancestors_count')
    ->get();

But I need to wrap this conditions in a logic group to be able to perform other conditions on the query. For this reason the "with count solution" does not work anymore:

$activePagesWhitelist = [10, 20];

$activePages = Page::query()
    ->whereIn('id', $activePagesWhitelist)
    // Not working since aggregates are not supported in logical groups.
    ->orWhere(
        fn($query) => $query
            ->where('active', true)
            ->withCount([
                'ancestors as __total_ancestors_count',
                'ancestors as __active_ancestors_count' => fn($query) => $query->where('active', true),
            ])
            ->havingRaw('__total_ancestors_count = __active_ancestors_count')
    )
    ->get();

Is there a better/proper way to archive this?

[staudenmeir]

Hi @jacobmllr95,
You could check the "opposite" - that a page doesn't have any inactive ancestors:

Page::whereNotHas('ancestors', fn($query) => $query->where('active', false))

[jacobmllr95]

@staudenmeir Thank you! Checking the opposite works like a charm :)
I just needed to replace your whereNotHas clause with the correct whereDoesntHave clause.