How I can put composite keys in models in Laravel 5?

Question

I have in my DataBase a table with two primary keys (id and language_id) and I need put it in my models. The default primaryKey in Models (Model.php in Laravel 5) is id, and I want that the primaryKeys will be id and id_language. I tried put it with arrays or a String with ',' but it doesn't work. It says me that the array could not be converted in String.

Nonreturnable

Eloquent does not support composite primary keys. – Deemster

I found the solution to this implementing the suggested here #36332505 – Brierroot

Answer 1

You can't. Eloquent doesn't support composite primary keys.

Here's a Github issue regarding this.

Irving

I've been coming back to this answer again and again hoping that Eloquent will get composite key support. The issue has been locked down. Who could be the right person to talk about eloquent? It's a little too much to go through src, I'd love a head start and understand why composite support is so hard to get. – Emmerich

Don't know when it was added, but they are available in Laravel 5.6+ (laravel.com/docs/5.6/migrations#creating-indexes) – Egret

@HonestObjections They are available in migrations, and have been for several versions. That's not the issue. Eloquent still does not support composite keys (for some reason). – Hinz

My mistake, I'd assumed there would be eloquent support if they were in migrations – Egret

It does support now – Castalia

@ShahidKarimi At the time of me writing this (22 Aug 2021), the official documentation for both 8.x and Master explicitly state that composite primary keys are not supported in Eloquent laravel.com/docs/8.x/eloquent#primary-keys To be clear, the OP was asking about models (Eloquent) and not migrations (which do support composite primary keys) – Flense

The reason is that they can't imagine why you would want a model that is not accessed by way of a URL. (Even though they support pivot table models...) – Duckworth

Answer 2

I wrote this simple PHP trait to adapt Eloquent to handle composite keys:

<?php

namespace App\Model\Traits; // *** Adjust this to match your model namespace! ***

use Illuminate\Database\Eloquent\Builder;

trait HasCompositePrimaryKey
{
    /**
     * Get the value indicating whether the IDs are incrementing.
     *
     * @return bool
     */
    public function getIncrementing()
    {
        return false;
    }

    /**
     * Set the keys for a save update query.
     *
     * @param  \Illuminate\Database\Eloquent\Builder $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    protected function setKeysForSaveQuery(Builder $query)
    {
        foreach ($this->getKeyName() as $key) {
            // UPDATE: Added isset() per devflow's comment.
            if (isset($this->$key))
                $query->where($key, '=', $this->$key);
            else
                throw new Exception(__METHOD__ . 'Missing part of the primary key: ' . $key);
        }

        return $query;
    }

    // UPDATE: From jessedp. See his edit, below.
    /**
     * Execute a query for a single record by ID.
     *
     * @param  array  $ids Array of keys, like [column => value].
     * @param  array  $columns
     * @return mixed|static
     */
    public static function find($ids, $columns = ['*'])
    {
        $me = new self;
        $query = $me->newQuery();
        foreach ($me->getKeyName() as $key) {
            $query->where($key, '=', $ids[$key]);
        }
        return $query->first($columns);
    }
}

Place that in a Traits directory under your main model directory, then you can add a simple one-liner to the top of any composite-key model:

class MyModel extends Eloquent {
    use Traits\HasCompositePrimaryKey; // *** THIS!!! ***

    /**
     * The primary key of the table.
     * 
     * @var string
     */
    protected $primaryKey = array('key1', 'key2');

    ...

---

addded by jessedp:
This worked wonderfully for me until I wanted to use Model::find ... so the following is some code (that could probably be better) that can be added to the hasCompositePrimaryKey trait above:

protected static function find($id, $columns = ['*'])
{
    $me = new self;
    $query = $me->newQuery();
    $i=0;

    foreach ($me->getKeyName() as $key) {
        $query->where($key, '=', $id[$i]);
        $i++;
    }

    return $query->first($columns);
}

---

Update 2016-11-17

I'm now maintaining this as part of an open-source package called LaravelTreats.

Update 2020-06-10

LaravelTreats is dead, but enjoy the code anyways :)

Over the years, a few in-depth use cases have been brought to my attention where this breaks down. This should work for the vast majority of use cases, but know that if you try to get fancy, you might have to rethink your approach.

Transport

NOTE if you using primary keys with boolean type, you need to fix the if statement at setKeysForSaveQuery() function. if($this->$key) to if(isset($this->$key)) – Solstice

@jesse Where is this find() method coming from? It doesn't exist on Illuminate\Database\Eloquent\Model, and it's public and not static in Illuminate\Database\Query\Builder – Transport

Failed on Laravel 5.3 when using find(), traced it to method getQualifiedKeyName() of Illuminate\Database\Eloquent\Model class – Harwin

@avonnadozie Please create an issue on the Github repo, and give as much context as you can. Thanks for the report! – Transport

This doesn't work on Pivot tables if you're query passes through them via. hasManyThrough. I described the details in an issue in your repo at github.com/mopo922/LaravelTreats/issues/6. – Wildebeest

For those who wonder how to use find here: If your primary pair is ['A','B'] then you find your entry with Model::find(['A' => 'first_part_of_your_key', 'B' => 'second_part_of_your_key']). Also the copy & paste script contains an error in the custom method find the first parameter should be $id and not $ids. – Antho

Thanks @Antho - fixed the $ids issue. – Transport

@Transport I'm shocked why many people complains about this problem and why Laravel's autors doesn't add this feature. A pity and a shame at same time. Your solution it works! Thank you so much for dedicate time for that! However, if I try to make a relation with belongToMany it throws this exception: array_key_exists(): The first argument should be either a string or an integer. This happens because it expects "primaryKey" to be a value but in fact and is a array. ¿Do you know some workaround to solve that ? – Petula

@JordiEspada there's a PR on my github repo that attempts to solve that, but i haven't had time to check it out. take a look and see if it helps you, and let me know your conclusion! – Transport

Sadly, composite keys do still seem to break queued jobs that serialize a model, regardless of the inclusion of this trait. – Hinz

Take care that you must have public $incrementing = false; in your model – Dianoetic

Does this train also work with hasMany relation if one of the models uses composite keys? – Antho

There are definitely some limitations here, which I suspect is why this has never been officially supported. – Transport

Thank you for creating your model solution and your repo. I was using it the past 3 years. However, I also run into tricky issues and I realized that I actually don't need an Eloquent model to represent my pivot table. I have now created a service class that handles whatever I want to do with that pivot table :) – Antho

The new self returns an empty array, cannot use find method. How to fix this issue? – Zoroastrianism

Answer 3

It seems it changed, since this one works with at least Laravel 5.1:

$table->primary(['key1', 'key2']);

I just run the migration and what i see in the database fits to what i put in code above (of course the name fields above are just for presentation purposes).

Update: this is true for migrations, but as soon as you want to insert via eloquent it doesn´t work with composite keys and will never do (last entry):

https://github.com/laravel/framework/issues/5517

Bonitabonito

Answer 4

In migrations you can simply define composite primary keys for a table as @erick-suarez and @sba said, in your Schema::create or Schema::table block write $table->primary(['key1', 'key2']);

In the Eloquent model that represents that table you can't directly use that composite key with Eloquent methods e.g. find($key) nor save($data) but you can still retrieve the model instance for viewing purposes using

$modelObject = ModelName::where(['key1' => $key1, 'key2' => $key2])->first();

And if you want to update a record in that table you can use QueryBuilder methods like this:

ModelName::where(['key1' => $key1, 'key2' => $key2])->update($data);

Where $data is the data associative array you want to update your model with like so ['attribute1' => 'value1', ..].

---

Note: You can still use Eloquent relationships safely for retrieval with such models, as they are commonly used as pivot tables that break many-to-many relationship structures.

Hypochondriasis

Answer 5

An easy solution, that works well for me (Laravel 5.7):

Define one of the primary keys in the Model:

class MyTable extends Model
{
        protected $primaryKey = 'user_id';
        protected $table = 'my_table';
        protected $fillable = ['user_id', 'post_id', 'flag'];

So pass both primary keys in the Controller, here I used in the first of the two parameters of the updateOrCreate method, that receives two arrays:

Controller:

public function post_foo(Request $request)
{
//PK1 is user_id, PK2 is post_id
//flag - is the field that has its value changed

$task = MyTable::updateOrCreate(
    ['user_id' => $request->header('UID'),'post_id' => $request->input('post_id')],
    ['user_id' => $request->header('UID'),'post_id' => $request->input('post_id'),'flag' => $request->input('flag')]
    );
    return $task;
}

Explaining the solution:

The problem of you not having an id column as the primary key is solved with only one primary key in Model, the correct row in DB is found with the two primary keys in the Controller.

Brahman

Answer 6

you can try to use following module

https://github.com/maksimru/composite-primary-keys

simply add HasCompositePrimaryKey trait to your model and specify array value as primary key

Suckow

Answer 7

<?php

use Illuminate\Database\Eloquent\Builder;

class YourModel extends Model 
{
    protected function setKeysForSaveQuery(Builder $query)
    {
        $query
            ->where('key1', '=', $this->getAttribute('key1'))
            ->where('key2', '=', $this->getAttribute('key2'));

        return $query;
    }
    
}

Equanimity

Answer 8

Try looking at this documentation for inserting many to many relations with CK

https://laravel.com/docs/5.2/eloquent-relationships#inserting-many-to-many-relationships

Edit: some extra info
As you can see in the documentation, the attach and detach functions create the links needed in the CK intermediate table. So yo don't have to create them yourself ;)

In your case it would be model->languages()->attach(language_id)

Excited

Answer 9

https://laravel.com/docs/5.3/migrations#columns

Yes, u can.

i share my migration code:

    <?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class RegistroEmpresa extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('registro_empresa', function(Blueprint $table)
        {
            $table->string('licencia',24);
            $table->string('rut',12);
            $table->string('nombre_empresa');
            $table->string('direccion');
            $table->string('comuna_Estado');
            $table->string('ciudad');
            $table->string('pais');
            $table->string('fono');
            $table->string('email');
            $table->string('paginaweb');
            $table->string('descripcion_tienda');
            $table->string('monedauso');
            $table->timestamps();
            $table->primary(['licencia', 'rut']);
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('registro_empresa');
    }

}

Dave

That creates a table. How do you write a working model that queries that table? – Undervalue

The question was about models, not migrations. – Abortifacient