SQLSTATE [42000]：语法错误或访问冲突：1066 laravel中不唯一的表/别名

Question

I have synced data where I attach some products to another one as related ones, save and sync method works just fine, my issue in edit part.

I get this error when I try to load my product edit page:

SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'product_relatives' (SQL: select `product_relatives`.*, `product_relatives`.`product_id` as `pivot_product_id`, `product_relatives`.`relatives_id` as `pivot_relatives_id` from `product_relatives` inner join `product_relatives` on `product_relatives`.`id` = `product_relatives`.`relatives_id` where `product_relatives`.`product_id` = 49)

Codes

this is my edit function

public function edit($id)
    {
      $product = Product::findOrFail($id);
      //another synced data and how i retrieve them
      $suboptions = Suboption::all();
      $suboptions2 = array();
      foreach($suboptions as $suboption) {
        $suboptions2[$suboption->id] = $suboption->title;
      }

      // my issue comes from here
      $relatives = ProductRelative::all();
      $relatives2 = array();
      foreach($relatives as $relative) {
        $relatives2[$relative->id] = $relative->title;
      }

      return view('admin.products.edit', compact('product','suboptions2', 'relatives2'));
    }

blade code

{{ Form::label('relatives', 'Relative Products') }}

{{Form::select('relatives[]', $relatives2, null, ['class' => 'form-control tagsselector', 'multiple' => 'multiple'])}}

product model

public function relatives()
  {
    return $this->belongsToMany(ProductRelative::class, 'product_relatives', 'product_id', 'relatives_id');
  }

relatives model

public $timestamps = false;
    protected $table = 'product_relatives';

    public $fillable = ['product_id', 'relatives_id'];


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

any idea how to fix that?

Answer 1

The SQL statement that is failing makes perfect sense. It is attempting to join the table product_relatives to the product_relatives table. So the error Not unique table or alias being thrown makes sense based on the query alone.

The next step is to try and think of reasons Laravel might be getting mixed up here.

Since you are trying to create a belongsToMany with the same model, you'll need these tables

1. products
2. product_relatives

Now, you only need to create one model:

1. Product

Your Product model should contain:

public function relatives()
{
    return $this->belongsToMany(Product::class, 'product_relative', 'product_id', 'relative_id');
}

Your ProductRelative model should contain:

public function product()
{
    return $this->belongsTo(Product::class, 'product_id');
}
public function relative()
{
    return $this->belongsTo(Product::class, 'relatives_id');
}

The issue was happening because you were using the ProductRelative model as a pivot table, and tried to create a BelongsToMany relationship within it using the same table name as the model itself.