TransWikia.com

Falla el filtro where en la consulta

Stack Overflow en español Asked on January 10, 2021

Tengo el siguiente código que funciona bien.

$visits = Visit::leftjoin('profiles', 'visits.user_id', '=', 'profiles.user_id')
                        ->selectRaw('visits.id as vid, profiles.name as uname, visits.first_name as vfn, visits.last_name as vln,
                gender, visits.cuit as vc, DATE_FORMAT(date_of_birth, "%d-%m-%Y") as birth, DATE_FORMAT(visits.created_at, "%d-%m-%Y") as date, TIME(visits.created_at) as hour')
                        ->orWhere('profiles.grandfather_id', $id)
                        ->orWhere('profiles.father_id', $id)
                        ->orWhere('profiles.user_id', $id)
                        ->paginate(50);

        return view('admin.dashboard.visits.admin-index', compact('visits'));

Pero al agregar esta sentencia, no la incluye en el filtro:

->where('visits.created_at', '>=', now()->subDays(15))

Queda así:

$visits = Visit::leftjoin('profiles', 'visits.user_id', '=', 'profiles.user_id')
                        ->selectRaw('visits.id as vid, profiles.name as uname, visits.first_name as vfn, visits.last_name as vln,
                gender, visits.cuit as vc, DATE_FORMAT(date_of_birth, "%d-%m-%Y") as birth, DATE_FORMAT(visits.created_at, "%d-%m-%Y") as date, TIME(visits.created_at) as hour')
                        ->where('visits.created_at', '>=', now()->subDays(15))
                        ->orWhere('profiles.grandfather_id', $id)
                        ->orWhere('profiles.father_id', $id)
                        ->orWhere('profiles.user_id', $id)
                        ->paginate(50);

        return view('admin.dashboard.visits.admin-index', compact('visits'));

Quitando estas tres y dejando la anterior funciona:

->orWhere('profiles.grandfather_id', $id)
->orWhere('profiles.father_id', $id)
->orWhere('profiles.user_id', $id)

Así:

$visits = Visit::leftjoin('profiles', 'visits.user_id', '=', 'profiles.user_id')
                        ->selectRaw('visits.id as vid, profiles.name as uname, visits.first_name as vfn, visits.last_name as vln,
                gender, visits.cuit as vc, DATE_FORMAT(date_of_birth, "%d-%m-%Y") as birth, DATE_FORMAT(visits.created_at, "%d-%m-%Y") as date, TIME(visits.created_at) as hour')
                        ->where('visits.created_at', '>=', now()->subDays(15))
                        ->paginate(50);

        return view('admin.dashboard.visits.admin-index', compact('visits'));

Consulta Sql();

select visits.id as vid, profiles.name as uname, visits.first_name as vfn, visits.last_name as vln, gender, visits.cuit as vc, DATE_FORMAT(date_of_birth, "%d-%m-%Y") as birth, DATE_FORMAT(visits.created_at, "%d-%m-%Y") as date, TIME(visits.created_at) as hour from `visits` left join `profiles` on `visits`.`user_id` = `profiles`.`user_id` where `visits`.`created_at` >= ? or `profiles`.`grandfather_id` = ? or `profiles`.`father_id` = ? or `profiles`.`user_id` = ?

One Answer

Basado en la documentación se puede estar debiendo a un problema agrupando las condiciones a cumplir por los múltiples where que existen en tu consulta.

La solución propuesta es usar el método orWhere una función la cual por dentro retornará los múltiples where opcionales.

Además de eso podemos mover el ->where('visits.created_at', '>=', now()->subDays(15)) antes de la solución propuesta arriba, ya que ese no parece ser opcional a cumplirse.

Quendando así:

$visits = Visit::leftjoin('profiles', 'visits.user_id', '=', 'profiles.user_id')
                ->selectRaw('visits.id as vid, 
                            profiles.name as uname, 
                            visits.first_name as vfn, 
                            visits.last_name as vln,
                            gender, 
                            visits.cuit as vc, 
                            DATE_FORMAT(date_of_birth, "%d-%m-%Y") as birth, 
                            DATE_FORMAT(visits.created_at, "%d-%m-%Y") as date, TIME(visits.created_at) as hour')
                ->where('visits.created_at', '>=', now()->subDays(15))
                ->orWhere(function ($query) use($id){
                    return $query->orWhere('profiles.grandfather_id', $id)
                                ->orWhere('profiles.father_id', $id)
                                ->orWhere('profiles.user_id', $id);
                })->paginate(50);

Answered by BetaM on January 10, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP