How to get recursive children with custom constraint?

[ilyapokrov]

I have this structure in the Category model database: id, category_id, parent_id, title, total_products.
I want to get a tree of nested child categories where the ID of the top parent is 3.
In the category model I specified:

use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
public function getLocalKeyName()
   {
      return 'category_id';
   }

I do it this way and everything works:
$tree = Category::find(3)->descendantsAndSelf;
But I want to add a custom constraint - the number of total_products is not equal to 0.
I could not find such an example in the documentation where it would be possible to specify two things at the same time - the ID of the top parent and my constraint.
I tried to simply get the tree, without specifying the ID of the top parent:

$constraint = function ($query) {
   $query->where('total_products', '!=', '0');
};
$tree = Category::treeOf($constraint)->get();

But the result was not what I expected. The number of my categories has increased 5 times =))

How can I get the result of categories that have products in a tree?

[
  {
    "id": 1,
    "title": 'Cloth',
    "children": [
      {
        "id": 2,
        "title": 't-shirts and polos',
        "children": [
          {
            "id": 3,
            "title": 't-shirts',
            "children": []
          }
          {
            "id": 4,
            "title": 'polos',
            "children": []
          }
        ]
      },
      {
        "id": 5,
        "title": 'sweaters',
        "children": []
      }
    ]
  }
]

thank you in advance!

[staudenmeir]

Hi @ilyapokrov,
Do you mean like this?

$tree = Category::find(3)->descendantsAndSelf()->where('total_products', '!=', '0')->get();

[ilyapokrov]

I tried to install it like this, but there was an error:
composer require staudenmeir/laravel-adjacency-list:"^1.0"
I searched for this error in the questions and found the answer:
#53
So I installed this version:
composer require staudenmeir/laravel-adjacency-list:"^1.6"

[staudenmeir]

What version does composer show show for staudenmeir/laravel-adjacency-list?

[ilyapokrov]

staudenmeir/laravel-adjacency-list v1.6 Recursive Laravel Eloquent relationships with CTEs

[staudenmeir]

composer require staudenmeir/laravel-adjacency-list:"^1.6"

The toTree() isn't available in v1.6, but your command should actually install v1.9.6 because that's the latest version compatible with Laravel 8. What operating system and terminal are you using?

Try running composer require staudenmeir/laravel-adjacency-list:"^1.9.6" instead.

[ilyapokrov]

Thank you! This version works!