What is the correct way to store and access complex related data in a many to many relationship with Laravel?

Question

I have the following database (simplified for demonstration):

• items

  • id
  • name

• milestones

  • id
  • name

• item_milestone

  • id
  • item_id
  • milestone_id
  • completed_at

• files

  • id
  • filename
  • fileable_type
  • fileable_id

I create bunch of items which have milestones and when I loop through those I can easily access data from the item_milestone like 'completed_at'. But if I have a file that is linked to a milestone item via fileable_type = 'App\Models\ItemMilestone' and the record id - how can I access that file record in a loop?

I suppose I could have file_id in the item_milestone table and link back to the file from the item milestone rather than by polymorphic relation, but I'd have the same issue - in a loop I only have access to the pivot data unless I pull it up with another query.

@foreach($item->milestones as $milestone)
  {{File::where('fileable_type', 'App\Models\ItemMilestone')->where('fileable_id', $itemmilestone->id)->first()->filename}}
@endforeach

I'm using the file model to attach files to all kinds of other models (users, projects, etc) which is why I have a polymorphic relationship. Is there a best practices way to do this - or maybe I'm missing something (which has been known to happen)?!

Answer 1

you have polymorphic relationship between files and other models and your current approach to accessing files in a loop is functional, it can lead to potential performance issues because you are making a separate query for each milestone to retrieve its associated file.

What you can do is use Eager loading to retrieve all the related files in a more efficient way.

class Item extends Model
{
    public function milestones()
    {
        return $this->belongsToMany(Milestone::class, 'item_milestone', 'item_id', 'milestone_id')
            ->withPivot('completed_at');
    }
}

and

class Milestone extends Model
{
    public function items()
    {
        return $this->belongsToMany(Item::class, 'item_milestone', 'milestone_id', 'item_id')
            ->withPivot('completed_at');
    }

    public function files()
    {
        return $this->morphMany(File::class, 'fileable');
    }
}

Query,

$item = Item::with('milestones.files')->find($itemId);

and then access your data,

@foreach($item->milestones as $milestone)
    @foreach($milestone->files as $file)
        {{ $file->filename }}
    @endforeach
@endforeach