Tell me, please, what kind of query can be used to get all records related to recursion?

[ilyapokrov]

I have a product and category model. The relationship between them is many-to-many.
Example: I want to get all products that are related to the clothing category (jackets, socks, panties).
Now I do this:

$idsChildCategories = Category::find($category->id)->descendantsAndSelf()->where('total_products', '!=', 0)->pluck('id')->toArray();

$allProducts = Product::whereHas('categories', function ($query) use ($idsChildCategories) {
                $query->whereIn('categories.id', $idsChildCategories);
            })
                ->where('price', '!=', null)
                ->orderBy($sort['value'], $sort['flags'])
                ->with('categories', 'genders', 'brands', 'colors', 'styles', 'materials', 'seasons', 'countries', 'features', 'ages')
                ->get();

It seems to me that I can do this with one simpler query.
I tried this, but I get an empty collection:

$allProducts = Category::find($category->id)->recursiveRoles()->get();

class Category extends Model
{
    use \Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

   public function recursiveRoles()
      {
         return $this->belongsToManyOfDescendantsAndSelf(Product::class);
      }
}

I'm using Laravel 8 and laravel-adjacency-list:"^1.9.6".

[staudenmeir]

$allProducts = Category::find($category->id)->recursiveRoles()->get();

Please log the executed SQL and share the Product::categories() relationship.

[staudenmeir]

How did you define the categories relationship in the Product model?

[ilyapokrov]

class Product extends Model
{
    public function categories()
    {
        return $this->belongsToMany(Category::class);
    }
}

[staudenmeir]

Your recursiveRoles relationship is correct and works for me.

I tried this, but I get an empty collection:

Does this category definitely have categories that aren't soft-deleted? I can only reproduce this issue if you provide some sample data (e.g. as a database dump).

[ilyapokrov]

@staudenmeir
I think I've figured it out. The following method works for me:
$allProducts = Category::find($category->category_id)->recursiveRoles()->get();
Instead of "$category->id" I indicated "$category->category_id".

Because I changed getLocalKeyName =)))

public function getLocalKeyName()
    {
        return 'category_id';
    }