Laravel save() is updating the wrong mysql record

Question

I have a user settings table which consists of 3 columns (or see images below): user_id, setting, value.

I then have two functions one to save timezones and one to save timeformats.

public function setTimezone($timezone){
    $settings = UserSettingsModel::firstOrNew(['user_id' => $this->user->id, 'setting' => 'timezone']);
    $settings->setting = 'timezone';
    $settings->value   = $timezone;
    $result = $settings->save();
}

public function setTimeFormat($timeformat){
    $settings = UserSettingsModel::firstOrNew(['user_id' => $this->user->id, 'setting' => 'timeformat']);
    $settings->setting = 'timeformat';
    $settings->value   = $timeformat;
    $result = $settings->save();
}

When I run the functions for the first time (each one is ran independently via an ajax call) I get this which is correct:

Then when I try to update the timeformat, (again each is independently ran) I get this:

For some reason the timezone setting is overwriting the timeformat setting, and I am not sure why.

Here is the UserSettingsModel class:

class UserSettingsModel extends BaseModel{
    protected $table      = 'user_settings';
    protected $fillable   = ['user_id', 'setting', 'value'];
    protected $primaryKey = 'user_id';
    public $timestamps    = false;
}

Here is the query log for a timezone update:

array:2 [
  0 => array:3 [
    "query" => "select * from `user_settings` where (`user_id` = ? and `setting` = ?) limit 1"
    "bindings" => array:2 [
      0 => 1
      1 => "timezone"
    ]
    "time" => 0.27
  ]
  1 => array:3 [
    "query" => "update `user_settings` set `value` = ? where `user_id` = ?"
    "bindings" => array:2 [
      0 => "America/Chicago"
      1 => 1
    ]
    "time" => 19.73
  ]
]

Here is the query log for a timeformat query:

array:2 [
  0 => array:3 [
    "query" => "select * from `user_settings` where (`user_id` = ? and `setting` = ?) limit 1"
    "bindings" => array:2 [
      0 => 1
      1 => "timeformat"
    ]
    "time" => 0.25
  ]
  1 => array:3 [
    "query" => "update `user_settings` set `value` = ? where `user_id` = ?"
    "bindings" => array:2 [
      0 => "12hr"
      1 => 1
    ]
    "time" => 13.67
  ]
]

not an expert of Laravel... but I would check the code that makes the ajax call. — Paolo, Feb 29 '16 at 17:14
It is sending the correct data, and the variables `$timezone` and `$timeformat` have the correct data in the dump. — Get Off My Lawn, Feb 29 '16 at 17:16
I don't see anything wrong with the laravel code. Does it do what you want if you go directly to the url? — Derek Pollard, Feb 29 '16 at 17:17
Does the `AJAX` url look correct in the console when you call it? — Derek Pollard, Feb 29 '16 at 17:20
Yeah `timezone = /web/v1/user/timezone` and `timeformat = /web/v1/user/timeformat` — Get Off My Lawn, Feb 29 '16 at 17:23
I would check the query that actually gets executed - http://stackoverflow.com/a/14536215/1579327 - Also `value` is a mysql reserved keyword so not the best choice for a column name. If Laravel orm doesn't escape it properly may misbehave. Last thought: `user_id` is a key, **not** a primary key, right? — Paolo, Feb 29 '16 at 17:40
I have updated the question with the output of the query logs — Get Off My Lawn, Feb 29 '16 at 17:47
the query is clearly wrong (the `where` clause in the `update` statement) - maybe laravel is messing with composite primary keys? add a primary key column `id` and let the other two be standard columns then retry... — Paolo, Feb 29 '16 at 17:51
It seems that Laravel is that bad that it doesn't support composite keys. You can find a hacky workaround here https://github.com/laravel/framework/issues/5517 — nikita2206, Feb 29 '16 at 17:59

Paolo · Accepted Answer · 2016-02-29T18:02:58.997

You have a composite primary key made of user_id and settings.

But then your model declares

    protected $primaryKey = 'user_id';

The query log you posted clearly shows that the SELECT statement is correct but the UPDATE is not because the WHERE clause specifies a requirement only for user_id.

Googling around (and as others commented) Laravel seems to have issues with composite keys.

If you dont find a decent workaround you may add a id column (primary key, int, auto-increment) to the table and update the model definition.

Then let the other columns be standard columns (just make them indexed if needed).

You'll then have some extra work when adding a new user as I guess you want to add a row for each setting...

Adding the auto incrementing primary key works. I then changed the primary composite keys to unique composite keys. — Get Off My Lawn, Feb 29 '16 at 18:02

Laravel save() is updating the wrong mysql record

1 Answers1