Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel where condition within 'with'

I have an eloquent query:

        $cameras=Jump::Video()
            ->with('flight','flight.aircraft','flight.airfield')
            ->with('student','student.logininfo','student.logininfo.reservations')
            ->with('camera','camera.status')
            ->whereBetween('data',[ $from, $to])
            ->orderBy($sortBy, $orderDesc)
            ->paginate($request->perPage);

and relations between student, logininfo and reservations

Model Jump.php

    public function scopeVideo($query)
    {
        return $query->where('exercise', '104');
    }

    public function student() {
        return $this->hasOne('AppModelsPersonalData','id','student');
    }

Model PersonalData.php

    public function logininfo() {
        return $this->hasOne('AppUser','id_user','id');
    }

Model User.php

    public function reservations() {
        return $this->hasMany('AppModelsReservation','user_id', 'id');
    }

I’d like to get only Jumps where ‘type’ in table ‘reservation’ (model Reservation) is equal $request->type

I was trying:

->where('student.logininfo.reservations','=',$request->type)

OR

->whereHas('student.logininfo.reservations',function($query) use ($request) {
    return $query->where('type','=',$request->type)
})

and it didn’t work. Any suggestions?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thank you for your post. It's a really helpful. Unfortunately withWhereHas is killing my server. I don't know why but there is no response from server (connection timed out). It looks like overloaded. I can't even stop listening (php artisan serve) and I need to kill php (laravel) process.

    I was using flight, flight.aircraft etc because it was simplification for this case and in my real code I use specific fields to limit size of downloaded objects. My real code looks like that:

            $cameras=Jump::Video()
        ->with('flight:id,nr,data,data_zalozenia,godz_ladowania,samolot,miejsce',
            'flight.aircraft:id,typ,znaki',
            'flight.airfield:id,icao,miejscowosc')
        ->with('ins1:id,imie,nazwisko,ilosc_skokow','ins1.logininfo:id,id_user,email,avatar,role')
        ->with('skydiver:id,imie,nazwisko,ilosc_skokow,waga','skydiver.logininfo:id,id_user,username,email,avatar,role')
        ->with('student:id,imie,nazwisko,ilosc_skokow,waga','student.logininfo:id,id_user,username,email,avatar,role','student.logininfo.reservations')
        ->with('camera','camera.status','camera.editor:id,imie,nazwisko','camera.editor.logininfo:id,id_user,email,avatar,role')
        ->whereBetween('data',[ $from, $to])
        ->orderBy($sortBy, $orderDesc)
        ->paginate($request->perPage);

    I changed this code to:

            $cameras=Jump::Video()
        ->with('flight:id,nr,data,data_zalozenia,godz_ladowania,samolot,miejsce',
            'flight.aircraft:id,typ,znaki',
            'flight.airfield:id,icao,miejscowosc')
        ->with('ins1:id,imie,nazwisko,ilosc_skokow','ins1.logininfo:id,id_user,email,avatar,role')
        ->with('skydiver:id,imie,nazwisko,ilosc_skokow,waga','skydiver.logininfo:id,id_user,username,email,avatar,role')
        ->with('student:id,imie,nazwisko,ilosc_skokow,waga','student.logininfo:id,id_user,username,email,avatar,role')
        ->withWhereHas('student.logininfo.reservations', function ($query) use ($request) {
            return $query->where('rezerwacja.kamera', $request->type);})
        ->with('camera','camera.status','camera.editor:id,imie,nazwisko','camera.editor.logininfo:id,id_user,email,avatar,role')
        ->whereBetween('data',[ $from, $to])
        ->orderBy($sortBy, $orderDesc)
        ->paginate($request->perPage);

    And my server (laravel) is not responding anymore :( I have to kill the php process and restart server to bring it back to live.

    whereHas is killing my server as well:

            $cameras=Jump::Video()
        ->with('flight:id,nr,data,data_zalozenia,godz_ladowania,samolot,miejsce',
            'flight.aircraft:id,typ,znaki',
            'flight.airfield:id,icao,miejscowosc')
        ->with('ins1:id,imie,nazwisko,ilosc_skokow','ins1.logininfo:id,id_user,email,avatar,role')
        ->with('skydiver:id,imie,nazwisko,ilosc_skokow,waga','skydiver.logininfo:id,id_user,username,email,avatar,role')
        ->with('student:id,imie,nazwisko,ilosc_skokow,waga','student.logininfo:id,id_user,username,email,avatar,role')
        ->whereHas('student.logininfo.reservations', function ($query) use ($request) {
            return $query->where('rezerwacja.kamera', $request->type);
        })
        ->with('camera','camera.status','camera.editor:id,imie,nazwisko','camera.editor.logininfo:id,id_user,email,avatar,role')
        ->whereBetween('data',[ $from, $to])
        ->orderBy($sortBy, $orderDesc)
        ->paginate($request->perPage);

  2. 0

    Couple things, you don’t need multiple ->with() clauses, nor do you need to chain the same Table multiple times. You can reduce your ->with()->with()->with() to simply:

    ->with(['flight.aircraft', 'flight.airfield', 'student.logininfo.reservations' , 'camera.status'])

    Dot-notation like student.logininfo.reservations will load the students, logininfo and reservations Relationships, so you don’t need ->with('a', 'a.b', 'a.b.c'), only ->with('a.b.c').

    Next, if you only want to load reservations of a specific type, you can append a function to your with():

    ->with(['student.logininfo.reservations' => function ($query) use ($request) {
  return $query->where('reservations.type', $request->type);
}]);

    If you need to limit the Jump models to only those that have a specific reservations.type, use ->whereHas():

    ->whereHas('student.logininfo.reservations', function ($query) use ($request) {
  return $query->where('reservations.type', $request->type);
});

    If you need filter AND limit, then you use ->withWhereHas():

    ->withWhereHas('student.logininfo.reservations', function ($query) use ($request) {
  return $query->where('reservations.type', $request->type);
});

    So, putting it all together:

    $cameras = Jump::video()->with(['flight.aircraft', 'flight.airfield', 'camera.status'])
->withWhereHas('student.logininfo.reservations', function ($query) use ($request) {
  return $query->where('reservations.type', $request->type);
})->whereBetween('data',[ $from, $to])
->orderBy($sortBy, $orderDesc)
->paginate($request->perPage);

    Adjust as needed, and reference the documentation if you get stuck:

    https://laravel.com/docs/11.x/eloquent-relationships#querying-relations

    https://laravel.com/docs/11.x/eloquent-relationships#querying-relationship-existence

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search