Cadet latest training date

I have Cadets (many to many) Squads App\Models\Cadets

public function squads(){
        return $this->belongsToMany(Squad::class);
    }

Squads (one to many) Trainings App\Models\Squads

public function trainings(){
        return $this->hasMany(Training::class);
    }

I want to present list of all Cadets and I want to show last training date

foreach( $cadets as $cadet){ $cadet->squad[x]->training[]->start_date }

Right now I create additional function in Cadet model

public function lastTrainingDate(){
        $a = '';
        $result = DB::select('
            SELECT DATE_FORMAT(start_time, "%d.%m.%Y") as start_time 
            FROM swe2021.trainings 
            WHERE 
                start_time is not null
	            AND squad_id in ( select squad_id from swe2021.cadet_squad where cadet_id = ?)
                ORDER BY start_time desc', [$this->id]);
        if(!empty($result)){
            $a = $result[0]->start_time;
        }
        return $a;
    }

but may be it is some better way?

CorvS

5 years ago

Level 27

@tomylimon Sounds like a case for addSelect:

https://laravel.com/docs/8.x/eloquent#subquery-selects

martinbean

5 years ago

Best Answer

Level 80

@tomylimon I’d be wary about that lastTrainingDate method in your model, because if you call them in a loop, then you’ll be introducing an N+1 problem to your application, where a database query is issued in every iteration of your loop.

Instead, use Eloquent’s relation methods. If you want the latest training for squads then you can just constrain the relation query:

$squads = Squad::with([
    'trainings' => function ($query) {
        $query->latest()->take(1);
    },
])->get();

This will fetch squads, and they’ll have only the latest training loaded which you can access:

@foreach($squads as $squad)
  <dl>
    <dt>Squad ID</dt>
    <dd>{{ $squad->id }}</dd>
    <dt>Latest training date</dt>
    <dd>
      @if($squad->training->isEmpty())
        <span>No training yet.</span>
      @else
        <time>{{ $squad->training->first()->start_time->toDateTimeString() }}</time>
      @endif
    </dd>
  <dl>
@endforeach

Please or to participate in this conversation.