Vertisan Vertisan - 1 year ago 96
MySQL Question

Laravel - Get records collection, if not exists in another table

I need to get all records from table IF these records not exists in another table, e.g.

Table 'Cars'

| ID | Name_of_car
| 1 .| Toyota
| 2 .| Ford

Table 'Crashed_cars'

| car_id | crash_date
| 1 .....| 22-02-2016

Now I want to get all not crashed cars - how can I do this?

At the moment I use a loop, but due to the number of entries (about 3000) I would have to use the direct collection. Here is my query:

Cars::select( 'id', 'firstname', 'lastname', 'car', 'color' )->get();

Answer Source

The best way is probably to get the ids of all crashed cars, and then searching for all other cars. In other words, something like this:

$crashedCarIds = CrashedCar::pluck('car_id')->all();
$cars = Car::whereNotIn('id', $crashedCarIds)->select(...)->get();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download