CODEWITHSUNDEEP
phpdatabaselaravel
h-kys
h-kys

Reputation: 175

How to use two foreignId in Laravel

I have two tables like:

User:

Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('loginid')->unique();
        $table->string('password');
        $table->rememberToken();
        $table->timestamps();
    });

IP:

Schema::create('i_p_s', function (Blueprint $table) {
        $table->id();
        $table->string('address')->unique();

        $table->foreignId('user_id')->nullable();
        $table->string('hostname');
        $table->string('description')->nullable();
        $table->timestamps();

        $table->index('user_id');
    });

IP Model:

public function User() {
    return $this->belongsTo(User::class);
}

User Model:

public function IPs() {
    return $this->hasMany(IP::class);
}

The user_id column means this IP is using by which user.

And now I want to add a new column last_modified which means who is the last editor of this row.

So I think the last_modified should be $table->foreignId('user_id')->nullable(); too.

But how to define the relationship in IP model?

Additionally, I call the user_id like this now.

$ips = IP::with('user')->get();

@foreach ($ips as $ip)
    {{ $ip->user }}
@endforeach

So how can I call the last_modified after the definition?

Thanks a lot

Upvotes: 2

Views: 7356

Answers (1)

Danaq
Danaq

Reputation: 673

As shown in the docs (https://laravel.com/docs/7.x/migrations#foreign-key-constraints), 

$table->foreignId('user_id')->nullable();

is just a shortcut of the "old" way

Schema::table('i_p_s', function (Blueprint $table) {
    $table->unsignedBigInteger('user_id');

    $table->foreign('user_id')->references('id')->on('users');
});

The problem with your code would be, that you need also the constrained()-method. It will dissolve a given column name like user_id into like "Laravel, please use the column id of the table users here".
I'm not sure if the nullable()-method will be useable for this shortcut.

In the same way, your relations will be dissolved within your models. If you're not adding additional values to the belongsTo() and haveMany()-methods, Laravel will try to find its way through your databse by assuming standard naming conventions for primary keys and table names (if the table names are not set within your model).

  • primary keys are assumed as id. This is also the reason why $table->ip() works.
  • table names are assumed as the plural of the model name. That means also, you have to make sure to set the name of your i_p_s table within your IP-model as it does not follow the convention. Event better would be to think about an adaption to the convention and call your table ips.
  • foreign keys should be (to be able to dissolve things that way) named by the singular table name, underscore, primary key. in other words user_id.

So, your assumption should be right apart from the fact that you cannot add a second column called user_id. For your second foreign key, your code should look like the "normal/ traditional" way of doing this.

Schema::table('i_p_s', function (Blueprint $table) {
    $table->unsignedBigInteger('last_modified')->nullable();

    $table->foreign('last_modified')->references('id')->on('users');
});

I'm pretty sure that this will work, although I didn't tested this yet. Unfortunately, I'm not sure if you can provide the column name and table also within the constrained method. If so, that would be pretty handy. Give it a try, otherwise use the traditional way.

The relation within the model should then look like this:

public function hasChanged() {
    $this->hasMany(IP::class, 'last_modified', 'id');
}

last_modified is the foreign key within the i_p_s-table while id is the local column of your owning User-model.

Bringing this into reverse for the IP-model:

public function wasChangedBy() {
    $this->belongsTo(User::class, 'last_modified', 'id');
}

In both cases you can dispense on setting the 'id' column as primary key of your users-table as it is standard.

The relations are the same as in your example because of the construction/ architecture. In 99% this is always a one-to-many relation.

Last but not least, it was a bit strange to see this construction of the same foreign key two times referencing in the same table. But I found this post, which says it is eventually totally normal to do so.
Mysql: using two foreign keys to the same table

The only other way I could think of would be to have an intermediate table between i_p_s and users but this would lead to a loop in your database between these two tables which also is a thing you should avoid. So I would say this is ok.

Hope this helps ;)

Upvotes: 7

Related Questions