Eager Loading Media That is Associated with the User Model

I need to speed things up and am not sure how to do it. This is my “create” method in my “Training” model

public function create(Training $training, Request $request)
    {
        set_time_limit(60); // Set maximum execution time to 60 seconds
        $members = Personnel::with(['rank', 'shift', 'station'])
        ->where('personnel.status', 1)
        ->orderBy('personnel.lastname', 'asc')
        ->orderBy('personnel.firstname', 'asc')
        
        ->get();
       
        $shifts = Shift::where('status', 'published')->orderBy('name', 'asc')->get();
        $stations = Station::where('status', 'published')->orderBy('name', 'asc')->get();
        $units = Unit::where('status', 'published')->orderBy('name', 'asc')->get();
        $skills = Category::with('children')
            ->whereIn('taxonomy', ['training-skills'])
            ->where('parent_id', null)->get();
        $categories = Category::with('children')
            ->whereIn('taxonomy', ['training-categories'])
            ->where('parent_id', null)->get();
        $methods = Category::with('children')
            ->whereIn('taxonomy', ['training-methods'])
            ->where('parent_id', null)->get();
        $location_type = Category::with('children')
            ->whereIn('taxonomy', ['training-location-types'])
            ->where('parent_id', null)->get();
        $report  = null;
        
        // Get the old input value if it exists
        $oldStartDate = old('startdt');
        $oldEndDate = old('enddt');
        // Set the default value as the current date if there is no old value
        $default_startdt = $oldStartDate ? $oldStartDate : Carbon::now()->format('m/d/Y H:i');
        $default_enddt = $oldEndDate ? $oldEndDate : Carbon::now()->addHour()->format('m/d/Y H:i');
        
        return view('training.create', compact('methods', 'stations', 'units', 'shifts', 'skills', 'report', 'categories', 'location_type', 'default_startdt', 'default_enddt', 'training', 'members'));
    }

There is a lot going on. But the big hit on load times is the $members query. In order to speed things up, I created a “view” called “Personnel” which joins the “user” table with an “assignments” table. That view now is associaated with the Personnel model aand looks like this:

class Personnel extends Model implements HasMedia
{
    use InteractsWithMedia;

    // Set the table name to match the view name
    protected $table = 'personnel';

    // Disable timestamps if your view doesn't have these fields
    public $timestamps = false;

    // Define a relationship to the User model
    public function user()
    {
        return $this->belongsTo(User::class, 'id'); // Replace 'user_id' with the actual foreign key if different
    }

    // Define the relationship to the Rank model
    public function rank()
    {
        return $this->belongsTo(Rank::class, 'rank_id');
    }

    public function shift()
    {
        return $this->belongsTo(Shift::class, 'shift_id');
    }

    public function division()
    {
        return $this->belongsTo(Division::class, 'division_id');
    }

    public function station()
    {
        return $this->belongsTo(Station::class, 'station_id');
    }
}

That seemed to help.

My “User” model has a Spatie Media Library Pro relationship that looks like this:

public function registerMediaCollections(): void
    {
        $this
            ->addMediaCollection('profile');
    }

I know it is not as simple copying the relationship to the “Personnel” model since the media database has the “User” model hard coded into the model field.

Right now, my blade page is outputting 200+ records using some code to grab the profile picture for each record returned. For 200+ records, there is a lot of working being done here.

<table width="100%" class="table-checkboxes table table-bordered" id="p_table">
            <tbody>
                @php
                    // Define the default image URL once outside the loop
                    $defaultImageUrl = URL::asset('/media/images/silhouettes/Firefighter-Silhouette-tiny.jpg');
                @endphp
                @foreach ($members as $key => $member)
                    @php
                        // Check if the member has profile media
                        $hasProfileMedia = $member->hasMedia('profile');
                        // Get the profile media URL if it exists
                        $profileMediaUrl = $hasProfileMedia ? $member->getFirstMediaUrl('profile', 'tiny') : null;
                    @endphp

                    <tr>
                        <td>
                            <input type="checkbox" name="participants[]"
                                value="{{ $member->id }}|{{ $member->firstname }} {{ $member->lastname }}|rank|{{ $hasProfileMedia ? $profileMediaUrl : $defaultImageUrl }}"
                                style=" width: 30px;height: 30px; ">
                        </td>
                        <td class="m-0 p-0">

                            <img src="{{ $hasProfileMedia ? $profileMediaUrl : $defaultImageUrl }}"
                                alt="Default Image" name="member" id="member"
                                width="50px">
                            {{-- @if ($member->hasMedia('profile'))
                            <img src="{{ $member->getFirstMediaUrl('profile','thumb') }}" alt="Default Image" name="member"
                                id="member" width="50px">
                        @else
                            <img src="{{ URL::asset('/media/images/silhouettes/Firefighter-Silhouette-tiny.jpg') }}" alt="Default Image"
                                name="member" id="member" width="50px">
                        @endif --}}

                        </td>
                        <td>
                            <div class='user-fullname'>
                                {{ $member->lastname }}, {{ $member->firstname }}
                            </div>
                            <div>
                                <small>
                                    {{ $member->rank->name ?? 'N/A' }} (Shift
                                    {{ $member->shift->name ?? 'N/A' }},
                                    {{ $member->station->name ?? 'N/A' }})
                                </small>
                            </div>
                        </td>
                    </tr>
                @endforeach
            </tbody>
        </table>

Is there a way to eager load the profile images, now that I am using the Personnel model which is actual a view of User and Assignment? Is there anything else I should be doing to speed the loading of 200+ records?

LaryAI

2 years ago

Level 58

Eager loading media in Laravel when using Spatie's Media Library can be a bit tricky because the media are typically associated with a model using a polymorphic relationship. However, you can still optimize the query to reduce the number of database calls.

Here's a solution that should help you eager load the profile images and speed up the loading of your records:

First, ensure that you have the media relationship defined in your User model since the media is directly associated with it.

use Spatie\MediaLibrary\HasMedia;
use Spatie\MediaLibrary\InteractsWithMedia;

class User extends Model implements HasMedia
{
    use InteractsWithMedia;

    public function registerMediaCollections(): void
    {
        $this->addMediaCollection('profile');
    }
}

Next, in your Personnel model, you can define a method to eager load the media for the associated user. This method will leverage the load method on the collection to load the media for all related users at once.

class Personnel extends Model
{
    // ... existing code ...

    public function loadUserMedia()
    {
        $this->load(['user' => function ($query) {
            $query->with('media');
        }]);
    }
}

In your controller method, you can call this new method on the collection of Personnel objects after you retrieve them.

public function create(Training $training, Request $request)
{
    // ... existing code ...

    $members = Personnel::with(['rank', 'shift', 'station'])
        ->where('personnel.status', 1)
        ->orderBy('personnel.lastname', 'asc')
        ->orderBy('personnel.firstname', 'asc')
        ->get();

    // Eager load the media for all related users
    $members->each->loadUserMedia();

    // ... existing code ...

    return view('training.create', compact('methods', 'stations', 'units', 'shifts', 'skills', 'report', 'categories', 'location_type', 'default_startdt', 'default_enddt', 'training', 'members'));
}

Finally, in your Blade template, you can access the media through the user relationship.

@foreach ($members as $key => $member)
    @php
        $user = $member->user; // Get the associated user
        $hasProfileMedia = $user && $user->hasMedia('profile');
        $profileMediaUrl = $hasProfileMedia ? $user->getFirstMediaUrl('profile', 'tiny') : null;
    @endphp

    <tr>
        <!-- ... -->
        <td class="m-0 p-0">
            <img src="{{ $hasProfileMedia ? $profileMediaUrl : $defaultImageUrl }}"
                 alt="Default Image" name="member" id="member"
                 width="50px">
        </td>
        <!-- ... -->
    </tr>
@endforeach

By using this approach, you're reducing the number of queries to the database by eager loading the media for all users in a single query, rather than having a separate query for each user's media. This should significantly improve the performance when loading a large number of records.

Please or to participate in this conversation.