ManyToMany with extra pivot table

Hi all, I got a situation that I cannot seem to solve.

I got the following setup

users (id)
companies (id)
roles (id, roleable_type, roleable_id) /// roleable in this example points to company but can also point to tenant
roles_users (role_id, user_id)

Now the tricky part

in my user I have companies(): BelongsToMany {} relation but I need to make an extra join for the roles OR roles_users table. I even extended BelongsToMany and got it working (got all companies) from the user. But now with inverse User::query()->has('companies') I find myself hacking BelongsToMany even further.

Hope anyone has experienced this before and can point me in the right direction.

sos99

3 years ago

Level 7

Hi @rogierv

Please look at

https://laravel.com/docs/9.x/eloquent-relationships#retrieving-intermediate-table-columns

By default, only the model keys will be present on the pivot model. If your intermediate table contains extra attributes, you must specify them when defining the relationship:

return $this->belongsToMany(Role::class)->withPivot('active', 'created_by');

good luck! :)

rogierv

3 years ago

Level 6

The issue isn't with the pivot table. The issue is that I need an additional pivot table where the relatedKey and relatedPivot key are both from different tables. So I need to join 4 tables together instead of the regular three tables.

I hope that clarifies the issue im having :)

rogierv

3 years ago

Level 6

I got it working. If anyone is interested make a custom HasManyThrough

<?php

namespace App\Support;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Relations\HasManyThrough;
use App\Company;

class CustomManyThrough extends HasManyThrough
{
    protected function performJoin(Builder $query = null)
    {
        $query = $query ?: $this->query;

        if ($query->getQuery()->from === 'users') {
            $query->join('model_has_roles', fn ($q) => $q->on('model_has_roles.model_id', '=', 'users.id'));
        }

        if ($query->getQuery()->from === 'companies') {
            $query->join('roles', fn ($q) => $q->on('roles.owner_id', '=', 'companies.id')->where('roles.owner_type', COmpany::class));
        }

        parent::performJoin($query);
    }
}

and in your user model return a new CustomManyThrough

1 like

Please or to participate in this conversation.