Make a Laravel Query with 2 database connections

Asked by: Juan Lopez

I have that query that I can execute perfectly in phpmyadmin:

SELECT f.`id`, f.`cod_factura`,f.`empresa` FROM 
`gen`.`facturas` f WHERE f.`id_cliente` IN(SELECT 
`id` FROM `gen`.`clientes` WHERE `dni` IN(SELECT 
`dni` FROM `ciiis17`.`users` WHERE `id` IN(SELECT `user_id` FROM 
`ciiis17`.`registrations` WHERE `has_bill`=1))) AND `enviado`=0  
AND `concepto`='1' 

Now I try to make that query in Laravel. I'm trying something like this:

$result = DB::connection('gen')
              ->table('facturas')
              ->select(DB::raw('id, cod_factura, empresa'))
              ->whereIn('id_cliente', DB::table('clientes')
                                      ->select(DB::raw('id'))
                                      ->whereIn('dni', DB::connection('ciiis17')
                                                       ->table('users')
                                                       ->select(DB::raw('dni'))
                                                       ->whereIn('id', DB::table('registrations')
                                                                       ->select(DB::raw('user_id'))
                                                                       ->where(['has_bill', 1],['enviado', 0],['concepto', '1']))))->get();

But I get this error:

"QueryException in Connection.php line 729: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'gen.registrations' doesn't exist"

It asks for "gen.registrations" but it should be "ciiis17.registrations"

The SQL executed finally is this:

"select id, cod_factura, empresa from `facturas` where `id_cliente` in 
(select id from `clientes` where `dni` in (select dni from `users` where 
`id` in (select user_id from `registrations` where (`0` = ? and `1` = ?))))"

Thanks

Source