Laravel Passport: Custom column name SQL error

Question

I am doing an API using Laravel's Passport, and whenever I send a request to one of my resources, I get this error:

{
    "message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id' in 'where clause' (SQL: select * from `users` where `id` = 1 limit 1)",
    "exception": "Illuminate\\Database\\QueryException",
    ...
}

We can see that it doesn't find the id column, that's because I renamed it to USER_ID.

I have seen this answer, but I don't see how I could use it in my case.

I tried this in my User.php model:

public function findForPassport($username) {
    return $this->where('USER_ID', $username)->first();
}

But it didn't fix the problem.

Thank you for your help.

show us some code. How does the code gives you this issue look like? — Indra, May 07 '18 at 14:38
Just curious, why choose `user_id` over `id`? `id` to me, is the more idiomatic choice — Derek Pollard, May 07 '18 at 14:38
@Derek actually there are some database designs where you do this. In that case when you do a join or an union you don't need to specify the key anymore. Ex: Select * from users join roles instead of select * from users u join role r on r.user_id = u.id — Indra, May 07 '18 at 14:40
It's to normalise the column names when doing junctures between 2 tables. I take the four first characters of the table's name. And when this is keys, ie primary keys or foreign keys, I make them upper case. — JacopoStanchi, May 07 '18 at 14:41
`protected $primaryKey = "user_id";` in your User model should fix this for you — Derek Pollard, May 07 '18 at 14:42
@Derek Thank you, that fixed my problem. Now I have another problem but this is not on this topic. — JacopoStanchi, May 07 '18 at 14:45

score 2 · Accepted Answer · answered May 07 '18 at 17:47

Derek's comment is correct, just set protected $primaryKey = 'user_id'. If you're interested in the nuts and bolts of it though, read on:

That issue is actually not specific to Passport, it has more to do with the Auth Guard behavior, which is abstracted from Passport.

Laravel's out-of-the-box App\User Model extends Illuminate\Foundation\Auth\User, which uses the Illuminate\Auth\Authenticatable trait. That trait is just a convenience for satisfying the conditions of the Illuminate\Contracts\Auth\Authenticatable interface, one of which is to have basically a getter for whatever your username and password fields are.

This trait defaults the username field to be the same as that tables primary key. But it doesn't have to be, if you were to override the getAuthIdentifierName() in your own Model class and make it return a field of your choosing.

Laravel Passport: Custom column name SQL error

1 Answers1