Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

Eloquent find() fails with Unknown column 'id' in where clause

ErrorSQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select * from `songs` where `id` = 5 limit 1)

Short answer

Eloquent assumes the primary key is a column called id in the table named after the model. The error means one of those guesses is wrong. Compare the real table and its key to the model, then set protected $primaryKey or protected $table, or the owner key of a belongsTo relation.

  • Applies to PHP 8.3, Laravel 12, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Look at the columns of the table named in the SQL, in the database itself, and read which one is the primary key.

    php artisan db:table songs
  2. 2Match what you see, then jump to its fix

    1. 1db:table shows a key such as song_id and no id column.The primary key column is not called idFix
    2. 2The table in the SQL is not the one you meant, or it has no id because it is a pivot or legacy table.The model is bound to a different table than the one you expectFix
    3. 3The failing query is on the parent table and is triggered by loading a relation.A belongsTo relation looks up the parent by idFix

What does the error look like?

  • SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select * from `songs` where `id` = 5 limit 1)

Cause 1 of 3

The primary key column is not called id

1Check

Eloquent assumes each table has a primary key column named id, and find() uses it.

grep -n "primaryKey" app/Models/Song.php

2Fix

Tell the model the real key. If the key is not an auto-incrementing integer, also set $incrementing and $keyType.

class Song extends Model
{
    protected $primaryKey = 'song_id';
}

3Verify

find() now queries the right column.

php artisan tinker
>>> App\Models\Song::find(5)

Confirmed by laravel.com

Did this fix it?

Cause 2 of 3

The model is bound to a different table than the one you expect

1Check

By convention the snake case, plural class name is the table name. A class named Song uses songs; AirTrafficController uses air_traffic_controllers.

grep -n "\$table" app/Models/Song.php

2Fix

Declare the table name when it does not follow that rule.

protected $table = 'tbl_songs';

3Verify

The SQL now names the right table.

php artisan tinker
>>> App\Models\Song::where('title', 'x')->toSql()

Confirmed by laravel.com

Did this fix it?

Cause 3 of 3

A belongsTo relation looks up the parent by id

1Check

belongsTo defaults to matching the parent's primary key (id). When the parent uses a different column, the relation has to say so with its third argument.

grep -n "belongsTo" app/Models/*.php

2Fix

Pass the foreign key and the parent's owner key.

public function album(): BelongsTo
{
    return $this->belongsTo(Album::class, 'album_code', 'code');
}

3Verify

The relation loads without the error.

php artisan tinker
>>> App\Models\Song::first()->album

Confirmed by laravel.com

Did this fix it?

How do I stop it coming back?

  • Name primary keys id in new tables so the defaults work.
  • For legacy tables, set $table and $primaryKey in the model before writing queries.
  • Run php artisan db:table on an unfamiliar table before modelling it.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Back end

php artisan migrate stops with could not find driver

could not find driver (Connection: mysql, SQL: select * from information_schema.tables ...)

Laravel talks to the database through a PDO driver, and this error means the PHP that runs artisan has none for your DB_CONNECTION.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.