I have these tables:

products (infinitely nested)

• id
• parent_id

links:

• id
• product_id

user_follows_link

• user_id
• linkd_id

I would like to write a query that returns the products that have links that a user follows, but if the number of links of a product and its descendants is higher for a parent product, the parent product should be returned instead of its child (recursive logic).

The idea is to show the users only one product if they follow links from products that are descendants of that product, no matter how deeply nested. But that product should be as deep in the tree as possible (parent products and their descendants should have the same number of links that are followed by the user).

Essentially I am looking for something likes the following (but that doesn't work because of the havingRaw inside the whereDoesntHave):

return Product::query()
            ->withCount([
                'allLinks' => function ($q) use ($user) {
                    $q->whereHas('isFollowedBy', function ($q) use ($user) {
                        $q->where('id', $user->id);
                    });
                }
            ])
            ->whereDoesntHave('parent as p', function ($q) use ($user) {
                $q->withCount([
                    'allLinks' => function ($q) use ($user) {
                        $q->whereHas('isFollowedBy', function ($q) use ($user) {
                            $q->where('id', $user->id);
                        });
                    }
                ])
                    ->havingRaw('all_links_count > p.all_links_count');
            })
            ->havingRaw('all_links_count > 0')
            ->get();

If you think this logic is way too complicated, I'm happy to take suggestions on what to persist to the database to make it more efficient.