Defining a HasManyThrough relationship in Laravel when direct references are absent in tables

Question

I'm currently working with Laravel 11 and have the following database structure:

• users

  • id

• artworks

  • id

• collections

  • id

• collection_user

  • id
  • collection_id
  • user_id

• artwork_collection

  • id
  • artwork_id
  • collection_id

In this setup, a User can have multiple Collections, a Collection can belong to multiple Users, and an Artwork can be part of several Collections. I believe I've set up the structure and models correctly:

class Artwork extends Model
{
    public function collections(): BelongsToMany
    {
        return $this->belongsToMany(Collection::class)->withTimestamps();
    }
}

class Collection extends Model
{
    public function artworks(): BelongsToMany
    {
        return $this->belongsToMany(Artwork::class)->withTimestamps();
    }

    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class)->withTimestamps();
    }
}

class User extends Authenticatable
{
    public function collections(): BelongsToMany
    {
        return $this->belongsToMany(Collection::class)->withTimestamps();
    }
}

My question is: How should I define the artworks() relation function in the User model to retrieve all Artworks a User has through Collections?

I attempted to use the hasManyThrough relation, but it seems to require direct references between the artworks and collections tables to users.

public function artworks(): HasManyThrough
{
    return $this->hasManyThrough(Artwork::class, Collection::class);
}

Should I introduce a pivot model? Or perhaps I need to perform a join operation?

Answer 1

After some fiddling (and help from AI) I came up with this. I dont know if that is best practice and would love to know the actual Laravel approach for this (if it isnt already):

public function artworks()
{
    return $this->belongsToMany(Artwork::class, 'collection_user', 'user_id', 'collection_id')
        ->join('artwork_collection AS ac', 'collection_user.collection_id', '=', 'ac.collection_id')
        ->join('artworks AS aw', 'ac.artwork_id', '=', 'aw.id')
        ->distinct()
        ->withTimestamps();
}