Authentication return "Column 'id' in where clause is ambiguous" because a GlobalScope

Can you show your login code?

@tisuchi its just:

Auth::login($user);

@ag2laracast You should be using also the table name here: whereid= 1

should be something like where user.id = 1

The id column exists in both tables, you have to specify which one should be used in the ẁhereclause.

@jeffer8a Yes, I know, but the query that I showed, on the question, comes from Laravel Authentication.

how about showing us the globalscope code. i.e. the ::apply method?

Global Scope

$builder->join('playables as p', 'users.id', '=', 'p.user_id')
        ->join('league_of_legends as lol', function ($join) use ($game){
            $join->on( 'p.playable_id', '=', 'lol.id');
            $join->on('p.playable_type', 'like', DB::raw( "'%$game%'"));
});

I think clause 'where id = 1 ' comes from EloquentUserProvider::retrieveById

(what's weird is why there is no 'and' before 'id = 1' [my sql could be rusty.])

I can think of 3 options:

1. you could just DB::raw the whole thing. and reset the builder:

2. or you can remove that 'where' from the $builder $where's array then append your own. 'and where user.id = ?'

3. you could redefine which UserProvider you use in your project. via auth.php and then in your AuthService provider. And from there extend the EloquentUserProvider to override public function retrieveById($identifier)

there has got to be a better way to do this. Laravel does have some pieces that are hard coded. like that. like always adding that exact same clause!

Any of this make sense?

The Third option make sense to me. I will override the method retrieveById. Also, I resolved this issue changing the primary_key to "users.id". But this cased others errors like: User:find(1) -> "Column 'users.users.id' not found".

Thanks @robrogers3 for your attention. :)

one question, this global scope will be applied to all User queries. Even though it's hosed, yeah? Is there a way you are only applying it to login?

Cause if global it will be applied everywhere. If that's what you want, then the fix is (pretty)easy. Essentially, you will unset the initial where that does not prefix the id to users.id. Look for property $wheres in 'Illuminate/Database/Query/Builder

let me know.

This global scope will be applied to all User queries. Because of that, when I make a Login, throws the MySQL exception. I think, EloquentUserProvider should use table_name.id in WHERE clauses, where table_name is the variable $table in your Model.

But, where on Login code I will unset the $wheres variable?

Good News!!

1. I was wrong. Which is good.

2. It's easy. (pretty easy).

Bottom line, you create your own CustomUserProvider.

Here are the steps:

• Create MyCustomUserProvider {} class in App\ (or wherever)

Note: It must implement Illuminate\Contracts\Auth\UserProvider;

I think this is the 'hard-ish' part.

See: Illuminate\Auth\DatabaseUserProvider and Illuminate\Auth\EloquentUserProvider; I think the DatabaseUserProvider is easier to figure out, and perhaps more closely matches what you need to do.

• Create MyCustomAuthProvider class in app/Providers (or YourCustomAuthProvider)

This is where you set $app->['auth'] to the above MyCustomUser provider

    public function boot()
    {
        $this->app['auth']->extend('my-custom',function()
        {
            return new MyCustomUserProvider(new User); //see here. a (blank) User gets past in to constructor.
        });
    }

• Register MyCustomUserProvider in config/app.php (otherwise it's not found)

'providers' => [
        'App\Providers\MyCustomAuthProvider',
        ...
        ]

• Update config/auth.php

change this:

    'providers' => [
        'users' => [
            'driver' => 'eloquent', //nope dont want this one!
            'model' => App\User::class,
        ],
        //...
    ]

to this in order to use 'MyCustomeUserProvider

    'providers' => [
        'users' => [
            'driver' => 'my-custom', //see how this matches the above $this->app['auth']->extend('my-custom', ...
            'model' => App\User::class,
        ],
        //...
    ]

Final Note: I cobbled this together from different sources.

https://laravel.io/forum/11-04-2014-laravel-5-how-do-i-create-a-custom-auth-in-laravel-5 <-- most steps. https://laracasts.com/discuss/channels/laravel/replacing-the-laravel-authentication-with-a-custom-authentication <-- helps on implementation of Illuminate\Auth\UserProvider;

check them out for details.