How to get results from table with a condition related to pivot table

Here's my simplified 'flights' table: id seats

and pivot table (used for bookings) flight_id tourist_id

Now in the controller I got: $flights = Flight::all();

My question is, how do I get only those flights that have free seats available for booking and pass those flights to view? I would need to get flight_id count from pivot for each flight and if it's less than $flight->seats than keep it and than pass it on.

Any thoughts?

staudenmeir

7 years ago

Best Answer

Level 24

Use withCount():

$flights = Flight::withCount('tourists')
    ->having('seats', '>', DB::raw('tourists_count'))
    ->get();

tete

7 years ago

Level 1

Nice one, staudenmeir :)

I felt querying DB was the way to do it, considering pivot doesn't have a model.

Thank you.

tete

7 years ago

Level 1

I'm trying to filter the above results further by getting only those flights with available seats that are not booked by tourist with $id.

I've tried all of the below with no success:

$flights = Flight::withCount('tourist')
            ->having('tourist', '<>', $id)
            ->having('seats', '>', DB::raw('tourist_count'))
            ->get();

and

$flights = Flight::withCount('tourist')
            ->has('tourist', '!=', $id)
            ->having('seats', '>', DB::raw('tourist_count'))
            ->get();

and

$flights = Flight::withCount('tourist', function ($query) use ($id) {
            $query->where('tourist', '!=', $id);
        })
            ->having('seats', '>', DB::raw('tourist_count'))
            ->get();

Help appreciated.

tete

7 years ago

Level 1

I kinda solved it but I'm sure there's a better way. First, in the controller I prepare an array with tourist bookings (booked flight ids) and pass it on to view.

$booked = [];
        foreach ($tourist->flight as $f) {
            $booked[] = $f->id;
        }

Then, in view, I loop through flights with free seats and check whether their ids are not in the tourist bookings array.

@foreach( $flights as $flight )
        @if( !in_array($flight->id, $booked) )
            <div>{{ $flight }}</div>
        @endif
@endforeach

Again, there must be a nicer way, so if anyone knows it, please, post it on.

Please or to participate in this conversation.