CODEWITHSUNDEEP
mysqlsqllaraveleloquentmariadb
Alex Harris
Alex Harris

Reputation: 6392

Trouble with Eloquent knowing actual primary (composite) key

There is a table in the database with the following initial migrations:

public function up()
    {
        Schema::create('products_markets', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('product_id')->unsigned();
            $table->foreign('product_id')->references('id')->on('products');
            $table->integer('country_code_id')->unsigned();
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->useCurrent();
        });

    $sql = 'ALTER TABLE `products_markets` ADD UNIQUE `unique_index`(`product_id`, `country_code_id`)';
    DB::connection()->getPdo()->exec($sql);
}

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

And everything was working fine; however, once it was realized that it did not make sense to have an auto-incrementing primary key id a new migration was made:

public function up()
    {
        Schema::table('products_markets', function (Blueprint $table) {
            DB::unprepared("ALTER TABLE products_markets ADD INDEX country_code_id (country_code_id)");
            DB::unprepared('ALTER TABLE products_markets MODIFY id INT NOT NULL');
            DB::unprepared('ALTER TABLE products_markets DROP PRIMARY KEY');
            DB::unprepared('ALTER TABLE products_markets DROP COLUMN id');
            DB::unprepared('ALTER TABLE products_markets ADD PRIMARY KEY (country_code_id, product_id)');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('products_markets', function (Blueprint $table) {
            DB::unprepared('DROP INDEX country_code_id ON products_markets');
            DB::unprepared('ALTER TABLE products_markets DROP PRIMARY KEY');
            $table->increments('id');
        });
    }

While this code works on our server and on some machines locally, other machines seem to be throwing errors during tests:

ProductTest::test_scopeMarket_only_returns_products_for_user_country Illuminate\Database\QueryException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: update products_markets set country_code_id = 121, updated_at = 2016-08-29 17:05:21 where id is null)

The test in question errors on the following line:

$products[0]->markets()->first()->update(['country_code_id' => 121]);

We have composer dumpautoload, re-migrating, clearing cache; however, nothing seems to help. Does this have to do with using DB::unprepared to ALTER the tables?

Upvotes: 0

Views: 225

Answers (1)

Alex Harris
Alex Harris

Reputation: 6392

The answer is that eloquent does not support composite primary keys.

If you run :

$products[0]->markets()->update(['country_code_id' => 121]);

it will run; however, if you run:

$products[0]->markets()->get()->update(['country_code_id' => 121]);

it will fail because first() returns a model and the default model primaryKey is id and composite primaryKeys are not handled by Eloquent.

Upvotes: 1

Related Questions