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
  ]
]

Answer 1

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...