mysql – Laravel 5.6 consulta whereNotExist

Question:

I have a practice where cars must be added to an insurer and be able to register their insurance, and I am trying to make a query that, when selecting an insurer from a list, will return all the vehicles that are not in that insurer (including those that are in others), but running 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 |
--------------------------------------------------------------

insured_vehicles

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

I'm new to laravel, so I still don't quite understand how to structure the query.

Thanks in advance for your answers.

Answer:

Did you try 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 are needing but if what you need is the vehicles from the vehiculos table you could obtain the vehicle ids to obtain the info using the whereIn method

$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();
Scroll to Top