laravel-5eloquentcomposite-keypolymorphic-relationship

SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause'


I keep getting the error

"SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' "

with the sql output:

    update `contactables` set `updated_at` = 2019-08-16 20:35:56, `active` = 0 where `` is null and `key_ID` = 235852

I was attempting to use the eloquent firstOrNew/firstOrCreate method on a polymorphic model. I've tried all sorts of variants of this:

  $record1 = Contactable::firstOrNew(
    [
     'contactable_ID' => $location_ID,
     'user_ID' => $user_ID,
     'contactable_type' => Location::class,
    ]);
  $record = Contactable::find($record1->key_ID);
  $record->fill(
    [
     'active' => $active,
    ]);
  $record->save();
    $primaryKey = 'key_ID';
    $guarded = [];
    $fillable = [
        'user_ID',
        'use_loc_contact_info',
        'contactable_ID',
        'contactable_type',
        'active',
        'flag',
        'print_and_save',
        'marketing',
      ];

This appears to only happen when trying to update a record where I actually change something. Creating records seems to work fine, updating where I don't change anything seems to work. I'm at a total loss as to what to do next...

I've looked at these (and a dozen+ others that weren't really related):

Laravel eloquent firstOrNew method doesn't update or insert

laravel auth:api returns SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (SQL: select * from `users` where ``

Thanks for any help, or new questions!


Solution

  • After a fair amount of looking around, I finally found a solution for this based on this issue I stumbled across. I forgot that this table has a composite key, which was the source for this problem.

    I included this in my custom pivot model to override the laravel default for "setKeysForSaveQuery":

    class Contactable extends MorphPivot {
        ...
        protected function setKeysForSaveQuery(Builder $query) {
            $query
                //Put appropriate values for keys here:
                ->where('contactable_type', '=', $this->contactable_type)
                ->where('contactable_ID', '=', $this->contactable_ID)
                ->where('user_ID', '=', $this->user_ID);
    
            return $query;
        }
        ...
    }
    

    it worked like a charm, and fixed the problem that was resulting in eloquent creating the ...where '' is null and 'key_ID' = 235852 query

    I also referenced the following that informed my solution but mostly didn't solve my overly complex composite-key-on-custom-polymorphic-relationship problem. I hope this path helps someone else.

    Laravel eloquent firstOrNew method doesn't update or insert

    Creating and Update Laravel Eloquent

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

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

    Laravel Model with Two Primary Keys update

    https://laravel.com/docs/5.7/migrations#creating-indexes

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

    https://blog.maqe.com/solved-eloquent-doesnt-support-composite-primary-keys-62b740120f

    https://www.reddit.com/r/laravel/comments/alrgwk/composite_key_in_table_only/