Laravel: BelongsToMany or Polymorphic Relations? Practical example.

Admin

Monday, July 10, 2023 6 mins to read

When structuring a database, similar relationships may repeat, like a Task may be assigned to a User, User Group, or User Role. Is it worth thinking about polymorphic? In this example, let's compare the performance and convenience to use.

Problem Explained and Two Options Shown

It's easier to understand the problem visually, so here's the form we're dealing with:

So, how to save the data here? Two main options.

Option 1. Multiple Many-to-Many Pivot Tables

So, we can create three DB intermediate tables:

group_task: group_id, task_id
position_task: position_id, task_id
task_user: task_id, user_id

Here's the code for the Migration files:

Schema::create('group_task', function (Blueprint $table) {
    $table->foreignId('group_id')->constrained();
    $table->foreignId('task_id')->constrained();
});
Schema::create('position_task', function (Blueprint $table) {
    $table->foreignId('position_id')->constrained();
    $table->foreignId('task_id')->constrained();
});
Schema::create('task_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
    $table->foreignId('task_id')->constrained();
});

And here's the Eloquent model:

app/Models/Task.php:

use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Task extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class);
    }
 
    public function groups(): BelongsToMany
    {
        return $this->belongsToMany(Group::class);
    }
 
    public function positions(): BelongsToMany
    {
        return $this->belongsToMany(Position::class);
    }
}

Visually, it would look like this:

Option 2. Polymorphic in One Table

Alternatively, we may create this table with many-to-many polymorphic:

assignables: task_id, assignable_type, assignable_id

Here's the Migration code:

Schema::create('assignables', function (Blueprint $table) {
    $table->foreignId('task_id')->constrained();
    $table->morphs('assignable');
});

And here's the Model:

app/Models/Task.php:

use Illuminate\Database\Eloquent\Relations\MorphToMany;
 
class Task extends Model
{
    public function users(): MorphToMany
    {
        return $this->morphedByMany(User::class, 'assignable');
    }
 
    public function groups(): MorphToMany
    {
        return $this->morphedByMany(Group::class, 'assignable');
    }
 
    public function positions(): MorphToMany
    {
        return $this->morphedByMany(Position::class, 'assignable');
    }
}

These would be the values in the DB:

Visually, the DB schema would look like this:

Correct, polymorphic relations are not actual DB relationships. They assign values only on the Laravel application level but not in the DB. So there are no direct foreign keys, and you can't (easily) use automatic operations like "delete on cascade".

That's actually the first drawback of polymorphic, in general. Someone could delete the Task record assigned to someone, and no related records in assignables would be auto-deleted or even flagged. In case of many-to-many, DB would fire the error about the constraint and prevent the Task from being deleted.

Code for Storing Data is Identical

Now, let's look at the code for submitting the form and storing the new record.

Interestingly, this code would be identical in both cases of many-to-many or polymorphic relations.

We have the same $request parameters, and the same Relationship method names, so it just works.

TaskController:

$task = Task::create(['name' => $request->input('name')]);
 
if ($request->has('groups')) {
    $task->groups()->attach($request->input('groups'));
} elseif ($request->has('positions')) {
    $task->positions()->attach($request->input('positions'));
} elseif ($request->has('users')) {
    $task->users()->attach($request->input('users'));
}

Also, I've tested it with both options on seeding a lot of data and didn't see any performance difference for 100,000 records.

Finally, I've measured the DB sizes in both cases, with 100,000 tasks seeded.

Many-to-many: 19.3MB
Polymorphic: 19.2MB

Almost identical, right?

Retrieve Data: Measure Performance

Imagine we have a page called "My tasks" where the user needs to see their own tasks: assigned to them personally, their group, or their position.

Here's what a potential query would look like. Again, it's identical for polymorphic and non-polymorphic:

$tasks = Task::query()
    ->where(function ($q) {
        return $q->whereHas('groups', function ($q) {
            return $q->where('id', auth()->user()->group_id);
        })
        ->orWhereHas('positions', function ($q) {
            return $q->where('id', auth()->user()->position_id);
        })
        ->orWhereHas('users', function ($q) {
            return $q->where('id', auth()->id());
        });
    })
    ->get();

The difference is, however, in what SQL queries are run under the hood.

Here's the query for many-to-many:

select * from `tasks` where
    (exists (select * from `groups`
        inner join `group_task` on `groups`.`id` = `group_task`.`group_id`
        where `tasks`.`id` = `group_task`.`task_id` and `id` = 2)
    or exists (select * from `positions`
        inner join `position_task` on `positions`.`id` = `position_task`.`position_id`
        where `tasks`.`id` = `position_task`.`task_id` and `id` = 4)
    or exists (select * from `users`
        inner join `task_user` on `users`.`id` = `task_user`.`user_id`
        where `tasks`.`id` = `task_user`.`task_id` and `id` = 1))

Here's the query for polymorphic:

select * from `tasks` where
    (exists (select * from `groups`
        inner join `assignables` on `groups`.`id` = `assignables`.`assignable_id`
        where `tasks`.`id` = `assignables`.`task_id`
            and `assignables`.`assignable_type` = 'App\Models\Group'
            and `id` = 2)
    or exists (select * from `positions`
        inner join `assignables` on `positions`.`id` = `assignables`.`assignable_id`
        where `tasks`.`id` = `assignables`.`task_id`
            and `assignables`.`assignable_type` = 'App\Models\Position'
            and `id` = 4)
    or exists (select * from `users`
        inner join `assignables` on `users`.`id` = `assignables`.`assignable_id`
        where `tasks`.`id` = `assignables`.`task_id`
            and `assignables`.`assignable_type` = 'App\Models\User'
            and `id` = 1))

I've tested the performance on 100,000 tasks with randomly seeded groups/positions/users, and those queries run like this:

Many-to-many: 0.24s
Polymorphic: 0.29s

The first one is a little faster, but not a big difference.

Best of Both Worlds: Extra HasMany Relationship For Querying?

Looking at the queries above, I thought that maybe whereHas() repeated three times is not the best solution.

After all, we don't need the IDs of the related tables of users/groups/positions, we just need to filter whether the records exist in the polymorphic table.

So why don't we generate a Model for the polymorphic table and introduce a new relationship in the Task Model:

php artisan make:model Assignable

We don't even need to edit anything that Model, it just needs to exists. and then...

app/Models/Task.php:

use Illuminate\Database\Eloquent\Relations\HasMany;
 
class Task extends Model
{
    public function users(): MorphToMany { ... }
    public function groups(): MorphToMany { ... }
    public function position(): MorphToMany { ... }
 
    public function assignables(): HasMany 
    { 
        return $this->hasMany(Assignable::class, 'task_id'); 
    } 
}

And then we can query only that one relationship with one whereHas() statement. Like this:

$tasks = Task::query()
    ->whereHas('assignables', function ($q) {
        return $q->where(function ($q) {
            return $q->where('assignable_type', Group::class)
                ->where('assignable_id', auth()->user()->group_id);
        })
        ->orWhere(function ($q) {
            return $q->where('assignable_type', Position::class)
                ->where('assignable_id', auth()->user()->position_id);
        })
        ->orWhere(function ($q) {
            return $q->where('assignable_type', User::class)
                ->where('assignable_id', auth()->id());
        });
    })
    ->get();

The SQL query under the hood is this:

select * from `tasks`
    where exists (select * from `assignables`
        where `tasks`.`id` = `assignables`.`task_id`
        and ((`assignable_type` = 'App\Models\Group' and `assignable_id` = 2)
            or (`assignable_type` = 'App\Models\Position' and `assignable_id` = 4)
            or (`assignable_type` = 'App\Models\User' and `assignable_id` = 1)))

So it doesn't even touch the second-level tables, it just works with tasks and assignables.

The result in performance: 0.042s!

Compare that to the 0.24s or 0.29s with "regular" whereHas() from above. Wow.

So yeah, the main takeaway here is that you may get creative when querying the data from polymorphic structure.

Future-Proof - Scenario 1: Add Extra Column(s)

Another point I want to discuss is flexibility.

What if you want to add an extra field, but only to some cases of the assignment? For example, if the task is assigned to a user, then they have a due_date.

In the case of many-to-many, we have more flexibility: we can add that column specifically to the task_user table.

But with polymorphic, we need to add it to the common assignables table, where in 2/3 of the cases, that column would be NULL and wouldn't play any role.

Is that a waste of DB space? Maybe. But generally, you probably wouldn't feel the difference in practice for one column.

But if, at some point, you need a more significant new structure for one of those relationships, then many-to-many gives you more freedom to introduce whatever you want.

Again, it depends on the specific scenario, and in case of more significant changes, you may re-think your whole structure at that time.

Future-Proof - Scenario 2: Add Extra Model(s)

A similar but different scenario: what if you add another Model to assign the Task to? For example, you have a new City model and want to assign tasks to cities.

In the case of many-to-many, you need to make DB changes and introduce a new city_task table.

But with polymorphic, you don't need to change anything in the database, just add another Eloquent relation method in the Task model, and you're good to go!

That's the main advantage and convenience of polymorphic structure: when you actually are not sure which Models you would have relations with in the future.

It is especially useful for packages that create their own DB tables: package creators don't know what DB structure the package users would have, so they often leave it flexible with polymorphism.

The best example is spatie/laravel-medialibrary with media DB table allowing you to assign a media file to any other Eloquent model.

Schema::create('media', function (Blueprint $table) {
    $table->id();
 
    $table->morphs('model');
    // ... other fields

In Conclusion

In this particular scenario of a record being assigned to multiple tables, there's not much difference whether you choose multiple many-to-many or polymorphic.

When querying the data, however, you can get creative and use some non-standard ways to query the polymorphic data with better performance.

You may want to introduce polymorphism to have a single structure instead of multiple tables, but not sure it's worth doing because you're losing the foreign key constraints and a bit of code readability for developers unfamiliar with terms like assignables.

Of course, there could be other scenarios where polymorphic is more beneficial, particularly if you want to add a new Model to assign to, then you don't need to create a new DB table.

What do you think? Would you prefer many-to-many or polymorphic in this situation?

If you want to see more examples like this, I have a 2-hour video course called How to Structure Databases in Laravel