Use of the WHERE NOT IN in Laravel

Question

Use of the WHERE NOT IN in Laravel

Navigation

#1 by (0 votes)

0

I want to obtain the books without repeating that there is in the table book but if they are not already added to the table lineapedido with the client that is making the query. Instead of that, with the following code I get the books that are in the lineapedido table that of course, are repeated.

$libros=DB::table('libro as l')
            ->join('autor as a', 'l.lib_autor_id', '=', 'a.id_autor')
            ->join('generoartistico as ga', 'ga.id_generoartistico', '=', 'a.atr_genero_artistico_id')
            ->join('generoliterario as gl', 'gl.id_generoliterario', '=', 'l.lib_genero_literario_id')
            ->join('lineapedido as lp', 'lp.lpe_libro_id', '=', 'l.id_libro')
            ->join('pedido as ped', 'ped.id_pedido', '=', 'lp.lpe_pedido_id')
            ->join('cliente as cli', 'cli.id_cliente', '=', 'ped.ped_cliente_id')
            ->select('l.id_libro', 'l.lib_no', 'l.lib_titulo', 'l.lib_direccion_web', 'l.lib_fecha_creacion',
                'l.lib_precio','l.lib_activo','a.atr_nombre_artistico as autor', 'gl.gl_nombre as generoliterario',
                'ga.ga_nombre as generoartistico', 'a.id_autor')
            ->when($filteredQuery, function($q) use ($query, $gArt, $gLit, $usuario_actual){
                        $q->where('lib_titulo', 'LIKE', '%'. $query . '%');
                        $q->where('id_generoartistico', '=', $gArt);
                        $q->where('id_generoliterario', '=', $gLit);
                        $q->where('lib_activo', '=', '1');
                        $q->whereNotIn('id_cliente', function($query){
                            $query->select('ped_cliente_id')->from('pedido');

                        });

                    })->get();

This would be the query you would want:

 SELECT 'libro'.*, 'autor'.'atr_nombre_artistico', 'generoartistico'.'ga_nombre', 'generoliterario'.'gl_nombre' 
FROM 'libro' 
INNER JOIN 'autor' ON 'autor'.'id_autor'='libro'.'lib_autor_id' 
INNER JOIN 'generoartistico' ON 'generoartistico'.'id_generoartistico'='autor'.'atr_genero_artistico_id' 
INNER JOIN 'generoliterario' ON 'generoliterario'.'id_generoliterario'='libro'.'lib_genero_literario_id' 
INNER JOIN 'lineapedido' ON 'lineapedido'.'lpe_libro_id'='libro'.'id_libro' 
INNER JOIN 'pedido' ON 'pedido'.'id_pedido'='lineapedido'.'lpe_pedido_id' 
INNER JOIN 'cliente' ON 'cliente'.'id_cliente'='pedido'.'ped_cliente_id' 
WHERE $usuario_actual->u_cliente_id NOT IN (SELECT 'pedido'.'ped_cliente_id' FROM 'pedido') 
GROUP BY 'libro'.'id_libro';

Would anyone know how to put that query in laravel?

eloquent laravel-5

asked by Maria Rosa Cambero 26.03.2018 в 22:16

source

1 answer

How to add 7 column in Sql? Can I update several elements of my table at the same time?

score 0 · Answer 1

One option to consider could be the following:

$pedidos = DB::table('pedido')->select('pedido.ped_cliente_id')->get()->toArray();
$pedidos_array = collect($pedidos)->map(function($x){ return (array) $x; })->toArray(); 

DB::table('libro')
->join('autor','autor.id_autor','=','libro.lib_autor_id')
->join('generoartistico','generoartistico.id_generoartistico','=','autor.generoartistico.')
->join('generoliterario','generoliterario.id_generoliterario','=','libro.lib_genero_literario_id')
->join('lineapedido','lineapedido.lpe_libro_id','=','libro.id_libro')
->join('pedido','pedido.id_pedido','=','lineapedido.lpe_pedido_id')
->join('cliente','cliente.id_cliente','=','pedido.ped_cliente_id')
->whereNotIn($usuario_actual->u_cliente_id, $pedidos_array)
->groupBy('libro.id_libro')
->select('libro.*','autor.atr_nombre_artistico','generoartistico.ga_nombre','generoliterario.gl_nombre')
->get();

Something you can use in the same way when generating this type of complex queries to validate is generating the expected query, it is to place before the query a DB::enableQueryLog(); and placing at the end of the query a dd(DB::getQueryLog()); .