Ad

Laravel: Select Data Based On Other Data In Another Table

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. enter image description here

Queues Tabelenter image description here

Buses Table

enter image description here

Stations Table

enter image description here

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);
}
Ad

Answer

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();
Ad
source: stackoverflow.com
Ad