ManyToMany with Eager Loading

[mahdinickdel]

Given the relationship:

Product → has many to many → Attribute → has many → Value

how to get Product::with(['attributes.values']) with values only related to each product not all values related to attribute.

[staudenmeir]

Please post the migration/structure of all tables involved.

[mahdinickdel]

class Attribute extends Model
{
    public function values()
    {
        return $this->hasMany('App\Models\Value');
    }
}

class Value extends Model
{
    public function attribute()
    {
        return $this->belongsTo('App\Models\Attribute');
    }
}

class Product extends Model
{
    public function attributes()
    {
        return $this->belongsToMany('App\Models\Attribute')->using('App\Models\AttributeProduct');
    }

    public function values()
    {
        return $this->belongsToMany('App\Models\Value')->using('App\Models\ProductValue');
    }
}

[mahdinickdel]

Schema::create('products', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
});

Schema::create('attributes', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->timestamps();
});

Schema::create('values', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('attribute_id')->index();
            $table->string('name');
            $table->timestamps();
});

Schema::create('product_value', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('product_id')->index();
            $table->integer('value_id')->index();
});

[staudenmeir]

Is there also an attribute_product table?

[mahdinickdel]

yes there is

class AttributeProduct extends Pivot
{
    public function attribute()
    {
        return $this->belongsTo('App\Models\Attribute');
    }

    public function product()
    {
        return $this->belongsTo('App\Models\Product');
    }
}

[staudenmeir]

It's possible with a direct relationship, a JOIN clause and a constraint:

class Product extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function attributeValues()
    {
        return $this->hasManyDeep(Value::class, ['attribute_product', Attribute::class])
            ->join('product_value', 'product_value.value_id', '=', 'values.id')
            ->whereColumn('product_value.product_id', '=', 'attribute_product.product_id');
    }
}

$products = Product::with(['attributeValues'])->get();

[mahdinickdel]

this way we get data related to product values and attributes are removed

[mahdinickdel]

take a look at this structure

[
    {
        "id": 1,
        "name": "Product 1",
        "attributes": [
            {
                "id": 1,
                "name": "Attribute 1",
                "values": [
                    {
                        "id": 1,
                        "name": "value 1"
                    },
                    {
                        "id": 2,
                        "name": "value 2"
                    }
                ]
            },
            {
                "id": 2,
                "name": "Attribute 2",
                "values": [
                    {
                        "id": 3,
                        "name": "value 4"
                    },
                    {
                        "id": 4,
                        "name": "value 3"
                    }
                ]
            }
        ]
    }
]

[staudenmeir]

It's not possible to implement this query with two separate relationships.

You can still get the intermediate attributes: https://github.com/staudenmeir/eloquent-has-many-deep#intermediate-and-pivot-data

If the result should be structured like this, you need to rearrange it manually.

[mahdinickdel]

but i've seen this implemented on spatie/permissions package where you can get something like this:

User::with(['roles.permissions'])

which do the exact thing

[staudenmeir]

What query does it execute?

[mahdinickdel]

    /**
     * Return all the permissions the model has via roles.
     */
    public function getPermissionsViaRoles(): Collection
    {
        return $this->loadMissing('roles', 'roles.permissions')
            ->roles->flatMap(function ($role) {
                return $role->permissions;
            })->sort()->values();
    }

[mahdinickdel]

let me check the query

[mahdinickdel]

my bad, its not get the permissions related to user through role with eager loading. thanks for you help though ;)

[mahdinickdel]

in your solution, i can't use has nor WhereHas

$products = Product::whereHas('attributeValues')->with(['attributeValues'])->get();

gives me an Column not found error

[staudenmeir]

What's the whole error message?

[mahdinickdel]

Column not found: 1054 Unknown column 'product_value.product_id' in 'where clause' (SQL: select from products where exists (select from values inner join attributes on attributes.id = values.attribute_id inner join attribute_product on attribute_product.attribute_id = attributes.id where products.id = attribute_product.product_id and product_value.product_id = attribute_product.product_id))

[staudenmeir]

whereHas() doesn't consider the custom JOIN clause, you need to add it manually:

Product::whereHas(['attributeValues' => function ($query) {
    $query->join('product_value', 'product_value.value_id', '=', 'values.id');
}])->with(['attributeValues'])->get();

[mahdinickdel]

thanks ;)