Laravel 5.6 query whereNotExist

Question

Laravel 5.6 query whereNotExist

Navigation

#1 by (0 votes)

3

I have a practice where you should add cars to an insurer and be able to register your insurance, and I'm trying to make a query that, when selecting an insurer from a list, I return all vehicles that are not in that insurer (including those that are in others), but to run it does not return anything.

This would be the function in the controller:

public function mostrarVehNAs(Request $request){
            $aseguradora = 2;
            $datos = \DB::table('crlo_vehiculos')
            ->whereNotExists(function ($query) {
                $aseguradora = 2;
                $query->select(\DB::raw(1))
                      ->from('crlo_veh_asegurados')
                      ->whereRaw('crlo_veh_asegurados.id_aseguradora', '=', $aseguradora);
            })
            ->get();
            return $datos;
            }

The tables would be:

insurers

------------------------------------ | id_aseguradora | nom | direccion | ------------------------------------

vehicles

------------------------------------------------------------- | id_vehiculo | marca | modelo | ano | tipo | color | serial | --------------------------------------------------------------

vehicles_insured

------------------------------------------------------- | id_aseg | id_vehiculo | id_aseguradora | id_vigencia | --------------------------------------------------------

I'm new to laravel, so I still do not understand how to structure the query.

Thank you in advance for your answers.

mysql laravel laravel-5

asked by Carlos Roberto Luna Ochoa 28.08.2018 в 17:53

source

1 answer

pass an array of c # to c ++ How to recover the last id generated in oracle 11g

score 0 · Answer 1

You tested using the whereNotIn method of the Where Clauses ?

$aseguradora_id = 2;
$veh_asegurados = DB::table('veh_asegurados')
                    ->whereNotIn('id_aseguradora', [$aseguradora_id])
                    ->get();

I think the above code is what you need but if you need the vehicles in the vehiculos table you could get the ids of the vehicles to get the info using the method whereIn

$aseguradora_id = 2;
$veh_asegurados_ids = DB::table('veh_asegurados')
                    ->whereNotIn('id_aseguradora', [$aseguradora_id])
                    ->pluck('id_vehiculo');

$vehiculos = DB::table('vehiculos')
                    ->whereIn('id', $veh_asegurados_ids)
                    ->get();