Getting data with orWhere

Question

Getting data with orWhere

Navigation

#1 by (1 votes)

-1

I have the following function that collects data from the books I have purchased. It certainly works, when I search for the three parameters it gives me what I want and when I do not use the parameters it gives me what I look for too. The thing is that I do it by php code and I would know how to do it in sql form without having to make two queries, but NOT in Eloquent, the fact that it looks for one query or for another in sql would be with a or, but in this case when you put the or just looking for one of them and the other does not touch it.

And what I want to ask is: is there not a better method to pick up the books that have a current user OR pick up the books that the current user has with the parameters that are given to him?

public function comprados(Request $request){

        $query= trim($request->get('searchText'));

        $gLit= trim($request->get('gLit'));

    $gArt= trim($request->get('gArt'));

    $usuario_actual=\Auth::user();

    $libros=DB::table('libro as l')
        ->join('lineapedido as lp', 'lp.lpe_libro_id', '=', 'l.id_libro')
        ->join('autor as a', 'a.id_autor', '=', 'l.lib_autor_id')
        ->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('pedido as ped', 'ped.id_pedido', '=', 'lp.lpe_pedido_id')
        ->select(DB::raw('count(*) as mismolibro'), 'l.id_libro', 'l.lib_no', 'l.lib_titulo', 'l.lib_direccion_web', 'l.lib_fecha_creacion',
            'l.lib_imagen', 'a.atr_nombre_artistico as autor', 'gl.gl_nombre as generoliterario',
            'ga.ga_nombre as generoartistico')
        ->orwhere(function($query2) use ($query, $gLit, $gArt, $usuario_actual) {
            $query2->where('lib_titulo', 'LIKE', '%' . $query . '%')
                ->where('id_generoliterario', '=', $gLit)
                ->where('id_generoartistico', '=', $gArt)
                ->where('ped.ped_cliente_id', '=', $usuario_actual->u_cliente_id);
        })
        ->groupBy('id_libro','l.lib_no', 'l.lib_titulo', 'l.lib_direccion_web', 'l.lib_fecha_creacion',
            'l.lib_imagen', 'a.atr_nombre_artistico', 'gl.gl_nombre',
            'ga.ga_nombre')
        ->paginate(7);

    if($gLit==null && $gArt==null && $query==null)
    {
        $libros=DB::table('libro as l')
            ->join('lineapedido as lp', 'lp.lpe_libro_id', '=', 'l.id_libro')
            ->join('autor as a', 'a.id_autor', '=', 'l.lib_autor_id')
            ->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('pedido as ped', 'ped.id_pedido', '=', 'lp.lpe_pedido_id')
            ->select(DB::raw('count(*) as mismolibro'), 'l.id_libro', 'l.lib_no', 'l.lib_titulo', 'l.lib_direccion_web', 'l.lib_fecha_creacion',
                'l.lib_imagen', 'a.atr_nombre_artistico as autor', 'gl.gl_nombre as generoliterario',
                'ga.ga_nombre as generoartistico')
            ->where('ped.ped_cliente_id', '=', $usuario_actual->u_cliente_id)

            ->groupBy('id_libro','l.lib_no', 'l.lib_titulo', 'l.lib_direccion_web', 'l.lib_fecha_creacion',
                'l.lib_imagen', 'a.atr_nombre_artistico', 'gl.gl_nombre',
                'ga.ga_nombre')
            ->paginate(7);
    }

    $generoart=GeneroArtistico::all();
    $generolit=GeneroLiterario::all();

    return view('almacen.libro.comprados', ["libros"=>$libros, "searchText" => $query, "generoart"=> $generoart, "generolit"=> $generolit, "gLit"=> $gLit, "gArt"=> $gArt]);
}

What I would like is for the following query to work, but nevertheless when I search for the parameters $ gLit, $ gArt, and $ query, it ignores me and simply looks for the where:

$libros=DB::table('libro as l')
            ->join('lineapedido as lp', 'lp.lpe_libro_id', '=', 'l.id_libro')
            ->join('autor as a', 'a.id_autor', '=', 'l.lib_autor_id')
            ->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('pedido as ped', 'ped.id_pedido', '=', 'lp.lpe_pedido_id')
            ->select(DB::raw('count(*) as mismolibro'), 'l.id_libro', 'l.lib_no', 'l.lib_titulo', 'l.lib_direccion_web', 'l.lib_fecha_creacion',
                'l.lib_imagen', 'a.atr_nombre_artistico as autor', 'gl.gl_nombre as generoliterario',
                'ga.ga_nombre as generoartistico')
            ->where('ped.ped_cliente_id', '=', $usuario_actual->u_cliente_id)
            ->orwhere(function($query2) use ($query, $gLit, $gArt, $usuario_actual) {
                $query2->where('lib_titulo', 'LIKE', '%' . $query . '%')
                    ->where('id_generoliterario', '=', $gLit)
                    ->where('id_generoartistico', '=', $gArt)
                    ->where('ped.ped_cliente_id', '=', $usuario_actual->u_cliente_id);
            })
            ->groupBy('id_libro','l.lib_no', 'l.lib_titulo', 'l.lib_direccion_web', 'l.lib_fecha_creacion',
                'l.lib_imagen', 'a.atr_nombre_artistico', 'gl.gl_nombre',
                'ga.ga_nombre')
            ->paginate(7);

php laravel eloquent laravel-5

asked by Maria Rosa Cambero 10.03.2018 в 19:06

source

1 answer

Problems with z index in carousel in bootstrap Problem when counting data

score 1 · Accepted Answer

I will provide a response based on Laravel 5.5+ and Eloquent relationships, in addition to the PSR-2 code style standard.

The problem is that orWhere () does not work for what you need, because it will deliver the "aggregate" of the results of the two where.

The solution is to make conditionals to define what query to perform, with when (), which receives as a first parameter a boolean, the second is what will be executed if it is true, and the third what will be executed if it is false:

public function comprados(Request $request)
{

    $query = $request->searchText;
    $gLit = $request->gLit;
    $gArt = $request->gArt;
    $usuarioActual = auth()->user();

    $filteredQuery = !empty($query) && !empty($gLit) && !empty(gArt);

    $libros = Libro::with(['lineas', 'generos', 'autor', 'pedidos']) // las relaciones implicadas
              ->when($filteredQuery, function ($q) use ($query, $gLit, $gArt, $usuarioActual) {
                  $q->whereIdGeneroliterario($gLit);
                  $q->whereIdGeneroartistico($gArt);
                  $q->where('lib_titulo', 'LIKE', '%' . $query . '%');
                  $q->wherePedClienteId($usuarioActual->u_cliente_id);
              }, function ($q) use ($usuarioActual) {
                  $q->wherePedClienteId($usuarioActual->u_cliente_id);
              })
              ->paginate(7);

    $generoArt = GeneroArtistico::all();
    $generoLit = GeneroLiterario::all();
    $searchtext = $query;

    return view('almacen.libro.comprados', compact('libros', 'searchText', 'generoLit', 'generoArt', 'gLit', 'gArt');
}

The rest is simply adapting it to your needs, like counts.