lara28580's avatar

How to eager load only the matching records

Is it somehow possible to eager load only matching records? My query looks like so

EventParticipant::with(['user', 'events'])
            ->whereHas('events', function ($query) {
                $query->where('replay_vimeo_id', '<>', null)
                    ->where('ends_at', '<', Carbon::now());
            })
            ->whereExists(function ($query) {
                $query->from('event_event_participant')
                    ->where('created_at', '>', 'events.ends_at')
                    //->where('replay_email_sent', false)
                ;
            })
            ->get();

What I want now is to eager load only the events where this matches

  ->whereHas('events', function ($query) {
                $query->where('replay_vimeo_id', '<>', null)
                    ->where('ends_at', '<', Carbon::now());
            })
            ->whereExists(function ($query) {
                $query->from('event_event_participant')
                    ->where('created_at', '>', 'events.ends_at')
                    //->where('replay_email_sent', false)
                ;

Maybe someone can help

0 likes
11 replies
tykus's avatar

You can use the new withWhereHas method to (i) constrain the main query and (ii) constrain the eager-loaded relations in one step:

->withWhereHas('events', function ($query) {
    $query->where('replay_vimeo_id', '<>', null)
        ->where('ends_at', '<', Carbon::now());
    })
1 like
lara28580's avatar

@tykus Thanks for your answer and what is with the part?

->whereExists(function ($query) {
                $query->from('event_event_participant')
                    ->where('created_at', '>', 'events.ends_at')
                    //->where('replay_email_sent', false)
                ;
tykus's avatar

@SmokeTM if that constrains the eager-load; then it can go inside the Closure. I don't know your schema, so don't know what event_event_participant is

1 like
lara28580's avatar

@tykus event_event_participant Is the pivot table. We have a many to many relation here between events and event_participants. How should that look like then it can go inside the Closure?

tykus's avatar

@SmokeTM something like this:

->withWhereHas('events', function ($query) {
    $query->where('replay_vimeo_id', '<>', null)
        ->where('ends_at', '<', Carbon::now());
        ->whereExists(function ($query) {
            $query->from('event_event_participant')
                ->where('event_participants.created_at', '>', 'events.ends_at')
                //->where('replay_email_sent', false)
                ;
        })
});

Or you could probably swap the whereExists for a join

MichalOravec's avatar

@SmokeTM Maybe this will work:

EventParticipant::with('user')->withWhereHas('events', function ($query) {
    $query->whereNotNull('replay_vimeo_id')
        ->where('ends_at', '<', now())
        ->wherePivot('created_at', '>', 'events.ends_at');
})->get();

I'm not sure about wherePivot and events.ends_at.

1 like
JonathanH-UK's avatar

Oh man! This is where I really start to hate using Eloquent as this is something I can do in seconds using a raw SQL query and yet it's really hard to do in Eloquent... Worse still for me, the 'withWhereHas' option looks like it might do what I need but I'm on Laravel 8 at the moment and it was added in Laravel 9 :-(

In my case I'm trying to retrieve Trainees which belong to a specific Organisation but only list the ones which also have at least one training record which has expired. i.e. Trainee::where('organisation_id',1)->with('expiredRecords') but constraining it to only return Trainee models where the 'expiredRecords' relationship returns 1 or more values.

In terms of the OP's question - I've so far not been able to find a satisfactory and reliable way of chaining and constraining Eloquent relationships in this way.

The wherePivot idea highlights that eloquent can be so dumb and confusing at times - So far as I can tell it's been an option since at least Laravel 5.x but only on a belongsToMany class, so when you use it on a Query Builder, laravel 'helpfully' decides you must mean a column on your table called 'pivot', which of course does not exist. . I'm honestly not even sure if withWhereHas will allow you to access intermediate columns added to your pivot table - you might be able to add it directly to the events relationship on your model? e.g.

public function events(bool $createdAfter = false) {
  $relationship = $this->belongsToMany(Event::class);
  if ( $createdAfter ) {
    $relatiionship = $relationship->wherePivot('created_at', '>', 'events.ends_at');
  }
  return $relationship;
}```
I'm afraid this is only a guess and I don't know if you'll be able to pass 'true'  as an argument using `with('events')` -  you might have to do something creative.
1 like

Please or to participate in this conversation.