Question: belongsToMany Recursive Get Ancestor

[TCURT15]

Hello, I am trying to come up with a way to access a categories root ancestor from a belongsToMany relationship. I have tried using a combination of staudenmeir/eloquent-has-many-deep and staudenmeir/laravel-adjacency-list with unexpected results. The relationships are as follows

<?php

namespace App\Models;

use Staudenmeir\EloquentHasManyDeep\HasRelationships;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    use HasRelationships;

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

    public function rootCategory()
    {
        return $this->hasOneDeepFromRelations($this->categories(), (new Category())->setAlias('cat_alias')->rootAncestor());
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;

class Category extends Model
{
    use HasRecursiveRelationships;

    protected $fillable = [
        'name',
        'parent_id',
        'sort_order',
    ];
}

Example Category structure

-- Level 1 ---- Level 2 ------ Level 3 -------- Level 4

A product is associated with Level 4 and I am trying to find an efficient way to get it's root category (Level 1) without having to load every single category a product is associated with. Is there a way to achieve this? Any assistance would greatly be appreciated!

Product::with('rootCategory')->first(); 

[staudenmeir]

Hi @TCURT15, What "unexpected results" are you getting?

[TCURT15]

When a product is associated with a single category at Level 4 the above code returns null. When associated at Level 3, its returning a category at Level 4. When associated at Level 2, its returning a category at Level 3. When associated at Level 1, its returning a category at Level 2.

[staudenmeir]

Can you log the executed queries?

[TCURT15]

select *
from `product`
limit 1

select `categories`.*,
       `category_product`.`product_id` as `laravel_through_key`
from `categories`
inner join `categories` as `cat` on `cat`.`id` = `categories`.`parent_id`
inner join `category_product` on `category_product`.`category_id` = `cat`.`id`
where `category_product`.`product_id` in (1)

[staudenmeir]

The issue is that hasOneDeepFromRelations() only supports a recursive relationship as the first one in the chain.

What DBMS are you using? There's an alternative approach, but it's not yet supported by MariaDB.

The idea is to "reverse" the query:

class Category extends Model
{
    use HasRecursiveRelationships;
    use HasRelationships;

    public function products()
    {
        return $this->belongsToMany(Product::class);
    }

    public function recursiveProducts()
    {
        // Use $this->descendantsAndSelf() if you want to get the equivalent to "RootAncestorOrSelf"
        return $this->hasManyDeepFromRelations($this->descendants(), (new static)->products());
    }
}

$rootCategory = Category::isRoot()
    ->whereRelation('recursiveProducts', 'products.id', $product->id)
    ->first();