Laravel: select data based on other data in another table

Question

I'm working on Bus Station Management System. and i'm trying to queue bus in Schedule. so what i want is this:

• i want to display all buses of specific station that are not in queue of specific schedule.

  - like if there are 5 buses in a station then two of them are in a queue of specific schedule, i want to display all other 3 buses to be selected to the queue.

so here are is the schedules table.

Queues Tabel

Buses Table

Stations Table

so i did it like this

public function getBusQueue(Request $request)
{
    $id = $request->id; // id of the targert schedule

    // getting the target station id;
    $schedule = Schedule::find($id);
    $stations_id = $schedule->station_id;

    // getting the buses that are not in queues table
    $buses = DB::table("buses")->select('*')
                ->whereNotIn('bus_number',function($query) {
                    $query->select('bus_number')->from('queues');
                })
                ->where('Schedule_id', $id)
                ->where('station_id', $stations_id)
                ->get();

    $data = $buses;  
    return Response()->json($data);
}

Answer 1

If I understand correctly, the relevant schedule in this query is the queue's, not the bus's.

(Also, you seem to have mistyped the schedule_id column name in the buses table. It is currently named schedual_id .)

Try changing the query to the following:

$buses = DB::table("buses")->select('*')
    ->whereNotIn('bus_number',function($query) use($id) {
        $query->select('bus_number')
            ->from('queues')
            ->where('schedule_id', $id);
    })
    ->where('station_id', $stations_id)
    ->get();