2 foreign keys referencing same primary key laraavel

Question

I'm relatively new in Laravel and I would like to learn how to create recursive relation with 2 foreign keys referencing the primary key.

I have table item_associations which has 2 FK referenced to the item table's PK:

Schema::create('item_associations', function (Blueprint $table) {
        $table->increments('id');
        $table->timestamps();
        $table->unsignedInteger('item_id');
        $table->foreign('item_id')
                ->references('id')
                ->on('items');
        $table->unsignedInteger('item2_id');
        $table->foreign('item2_id')
                ->references('id')
                ->on('items');
    });

Here is my ItemAssociation model:

protected $table = 'item_associations';

    public function items(){
        return $this->belongsTo('App\Models\Item','item_id');
    }

    public function item2_id(){
        return $this->belongsTo('App\Models\Item','item2_id', 'item_id');
    }

here is my Item model:

protected $table = 'items';

public function item_associations(){
        return $this->hasMany(UserType::class);
    }

here is my Welcome controller:

$item_association = DB::table('item_associations')
        ->join ('items', 'item_associations.item_id', '=', 'items.id')
        ->join ('items', 'item_associations.item2_id', '=', 'items.id')
        ->get();
return view('welcome', [
            'item_association' => $item_association,    
            ]);

Apparently, I receive an error: SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'items' (SQL: select * from item_associations inner join items on item_associations.item_id = items.id inner join items on item_associations.item2_id = items.id)

in blade I would like to get something like this:

Is there any reason for not using many-to-many relationship? — Bulent, Oct 28 '21 at 11:41
If item2_id is equal to item_id then it should not have a separate relationship. If they are different ids then you need to use a many to many relationship. — Diego Dieh, Oct 28 '21 at 11:51
@Bulent i want my item2_id be related with item_id, for instance, if the customer orders food that is item_it there will be additional items(let says gravies) that may come with the chosen item that's why I need item2_id — Amir Adamov, Oct 28 '21 at 13:01
@DiegoDieh i want my item2_id be related with item_id, for instance, if the customer orders food that is item_it there will be additional items(let says gravies) that may come with the chosen item that's why I need item2_id — Amir Adamov, Oct 28 '21 at 13:02

score 1 · Answer 1 · edited Oct 28 '21 at 14:44

What if you want to add another thing besides gravies?

I can suggest you 2 options.

First, instead of creating foreign keys in the separate columns, keep ids in a column by casting them an array. This is easy to setup and very flexible way.

Second, which is a better way in my opinion, using proper eloquent relationships like following:

The structure you want to achieve is a form of many-to-many relationship.

You need 3 tables: items, associations, association_item.

// Models/Item.php

public function associations()
{
    return $this->belongsToMany(Association::class);
}

// Models\Association.php

public function items()
{
    return $this->belongsToMany(Item::class);
}

public function additions()
{
    return $this->hasMany($this, 'parent_id', 'id');
}

// Migrations

Schema::create('items', function (Blueprint $table) {
    $table->increments('id');
    // some fields
    $table->timestamps();
});

Schema::create('associations', function (Blueprint $table) {
    $table->increments('id');
    // some fields
    $table->foreingId('parent_id')->nullable()->costrained('assosiations')
    $table->timestamps();
});

Schema::create('association_item', function (Blueprint $table) {
    $table->increments('id');
    $table->foreignId('item_id');
    $table->foreingId('association_id');
    $table->timestamps();
});

2 foreign keys referencing same primary key laraavel

1 Answers1