Laravel Basic BelongsToMany

Question

I have ProductCollection Model and AttributeValue Model which have a common table product_collection_attributes that has 2 columns: product_collection_id and attribute_value_id that are a foreign key to their respective tables and are both a primary key.

In ProductCollection Model relation is:

public function attributes()
{
  return $this->belongsToMany(AttributeValue::class, 'product_collection_attributes', 'product_collection_id');
}

And in AttributeValue Model relation is:

public function product_collections()
{
  return $this->belongsToMany(ProductCollection::class, 'product_collection_attributes', 'attribute_value_id');
}

My query is: $productCollection->attributes()->get(); which is returning an empty array, However if query was DB::table('product_collection_attributes')->where('product_collection_id', $productCollection->id)->get(); its returning a full array!!

AND THE MOST IMPORTANT Thing this happens only after the following code, WHICH IS BREAKING with error: (Exception: "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-2' for key 'product_collection_attributes.PRIMARY' (Connection: mysql, SQL: insert into product_collection_attributes (attribute_value_id, product_collection_id) values (2, 2))"): `

$productCollection->update([
 SOMEINFO...,
 'allow_price_change' => 0
]);
$data['attributes'] = [1,2];
$productCollection->attributes()->delete();
if (isset($data['attributes']) && count($data['attributes'])) {
 $data['attributes'] = array_unique($data['attributes']);
 $data['attributes'] = array_values(array_filter($data['attributes']));
 $productCollection->attributes()->attach($data['attributes']);
}

SO I REPLACED THE ABOVE CODE WITH:

$productCollection->update([
 SOMEINFO...,
 'allow_price_change' => 0
]);
$data['attributes'] = [1,2];
DB::table('product_collection_attributes')->where('product_collection_id', $productCollection->id)->delete();
if (isset($data['attributes']) && count($data['attributes'])) {
 $data['attributes'] = array_unique($data['attributes']);
 $data['attributes'] = array_values(array_filter($data['attributes']));
 $productCollection->attributes()->attach($data['attributes']);
}

And NOW I have this problem, $productCollection->attributes()->get() doesn't work anymore!! and only DB::table('product_collection_attributes')->where('product_collection_id', $productCollection->id)->get(); works!!

And note that if I update something in product collection that isn't a boolean all works fine!!, so update $productCollection->update(['name' => 'aaa', ...]) then update attributes will be fine

I don't know why and it is important to use ProductCollection::with(['attributes']) in almost all of my cases specially with joins, and it is forcing me to use DB instead!

How can we fix that?

Answer 1

The problem was in the way I was doing the delete of $productCollection->attributes().

The Correct way $productCollection->attributes()->detach(); And not ->delete().

Problem was easy to see after migration refresh. What actually happened the record of the AttributeValue was deleted!! and I was using soft deletes so it was hard to notice!!

The new migration showed that.

SO fix is: $productCollection->attributes()->detach();