Obtain data from tables of different database

Question

Obtain data from tables of different database

Navigation

#1 by (2 votes)

0

I have a dilemma that I try to do something that I do not know if it is even possible, I have 2 databases dpnmwin and spi_intranet of which dpnmwin is an existing one of a long time ago and which I can not touch its structure for nothing, for which I had to create the other. In dpnmwin there are many employee data which I bring without problem in a query between the 2 databases, the problem comes when I try to get the name of rol that employees get in the system that I develop. spi_intranet I had to create it because the other database did not have tables or fields for important information that I need like (roles, user, password), so create the other one.

With this query:

$user = Employee::join('spi_intranet.users', 'dpnmwin.nmtrabajador.CODIGO', 'spi_intranet.users.ci')
                            ->where('dpnmwin.nmtrabajador.CODIGO', '=', $auth->ci)
                            ->first();

It brings me all the data without problem but the rol of the user brings me is the id of rol that they have in table roles that is in spi_intranet , with relations in laravel I can take the name of the role but only of the user who is logged in at that moment, but of all the others only brings me the id . Try to make a relation between the table nmtrabajador (dpnmwin) and roles (spi_intranet) asi

Model Employee

public function role(){
    return $this->belongsTo('App\Role');
}

Model Role

public function employee(){

   return $this->hasMany('App\Employee');

}

Which gives me the following error

SQLSTATE [42S02]: Base table or view not found: 1146 Table 'dpnmwin.roles' does not exist (SQL: select * from roles where roles . id = 1 limit 1)

And I know that the error is because in the table nmtrabajador there is no field roles

QUESTION: Is it possible to make this relationship between 2 tables of different tables? or is there any way to do this?

php laravel laravel-5

asked by Edwin Aquino 01.06.2018 в 19:00

source

1 answer

Autocomplete failure IONIC checkbox list

score 2 · Accepted Answer

For you to work well relations between 2 databases you have to specify the connection in each model.

I do not understand well how your databases or your connections are composed, but assuming that: config / database

'connections' => [
    '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,
    ],
    'mysql2' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', '127.0.0.1'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE_2', 'forge'),
        'username' => env('DB_USERNAME_2', 'forge'),
        'password' => env('DB_PASSWORD_2', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
    ],
]

.env

DB_DATABASE=dpnmwin  

DB_DATABASE_2=spi_intranet

In your case it would be something like:

Model Employee

protected $connection = 'mysql'; // mysql -> dpnmwin
protected $table = 'nmtrabajador'; // supongo que se llama aí por la consulta que pusiste en la presunta

public function role() {
    return $this->belongsTo('App\Role', 'role_id'); 
    // Estoy suponiendo que el id del rol lo almacenás 
    // en el campo role_id pero si se llama de otra manera 
    // debes modificar eso
}

Model Role

protected $connection = 'mysql2'; // mysql2 -> spi_intranet
protected $table = 'roles';

public function employee() {
   return $this->hasMany('App\Employee');    
}

With this you should be able to do

$employee_rol = Employee::find(1)->role; // Rol del usuario con id 1
$rol_employee = Rol::find(1)->employee; // Usuarios con el id de rol 1
dd($employee_rol, $rol_employee);