Define Connection on whereHas

have you tried defining connection in model? in User model

class User extends Authenticatable
{
    use Notifiable;

    protected $connection = 'HRIS';

in config/database.php

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

        'HRIS' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => 'HRIS',
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

Halo @vajid ,

Yap, i already define the $connection property at Model and database.php for HRIS and Lara.

But still, the Permissions wont read the HRIS connection...

Is there a better way instead of using QB?

Thank you

You can even use DB, and specify connection

DB::connection('HRIS')->table('permissions').... rest of your query

make sure specified connection has required table.

Refer https://laravel.com/docs/5.7/database#using-multiple-database-connections

Hai @vajid,

yap, finally i use Query Builder, with a little "sneak peek" at whereHas query. But unfortunately, the only way i can define a connection is at the sub query, so i have to "define" the database name directly.

here is the finaly result:

$permissions = DB::table('roles')->whereExists(function ($query) {
    $query
    ->from('ZUSER_HRIS.dbo.adm_user')
    ->join('authorizations', 'adm_user.login_name', '=', 'authorizations.user_login_name')
    ->whereRaw('roles.id = authorizations.authorization_id')
    ->where('authorizations.authorization_type', 'App\Entities\Auth\Role');
})->get();

Thank you for helping me.

You are not the only one getting this issue. https://github.com/laravel/framework/issues/9355

Good to know, you have find the solution :)

Take a look at this package: https://github.com/hoyvoy/laravel-cross-database-subqueries

Holahola @vajid , yeah almost desperate to fix it. I don't want to change the core like the fix solution at the Issue, but i guess, i'll try to prefix the table with the DB name.

@staudenmeir thank you for helping me, i will try this package ASAP. Thank you for the suggestion.

Morning (in my place) @staudenmeir, i already try the package, but it's not success.

The package add DB to my table example:

table users -> HRIS.users (automatically).

And eloquent still return the same error invalid object name HRIS.users but now it adds HRIS to the error.

I guess i will use QB for temporary fix.

thank you

@rizaldywirawan, yes you are right changing in the core is bad practice, new update will break it.

@vajid I found another solution!

We can use prefix at DB for example:

$table = 'HRIS.dbo.users';

it works!