CODEWITHSUNDEEP
mysqllaraveldatabase-connectionrelationship
Stefano
Stefano

Reputation: 684

Laravel BelongsToMany on different databases

In my app (Laravel 9) users can belong to groups, and users can access resources "attached" to the group(s) they belong to.

However the resources are stored on a different database (we use two distinct RDS instances). Groups and users are managed on the default instance (where I also manage all the user/group permissions), where I also created the pivot table (group_item in this simplified case).

class Group extends Model
{
    // default connection

    public function addItem($item): void
    {
        $this->items()->attach($item);
    }

    public function items(): BelongsToMany
    {
        return $this->belongsToMany(Item::class);
    }
}

class Items extends Model
{
    protected $connection = 'external';

    protected $primaryKey = 'item_id';
}

public function test_group_items()
{
    $group = Group::factory()->create();

    $i = Item::first();

    $group->addItem($i);

    $this->assertTrue($group->items->contains($i));
}

In the above simple test, the "default" implementation has a DB reference issue, as it looks for the pivot table on the connection/DB of the passed model:

Illuminate\Database\QueryException : SQLSTATE[42S02]: Base table or view not found: 1146 Table 'external_db.group_item' doesn't exist (SQL: insert into `group_item` (`group_id`, `items_item_id`) values (49, 1))

Adjusting the belongsToMany instance does fix the issue when attaching an item to the items list, however it breaks when returning the collection

public function items(): BelongsToMany
{
    return $this->newBelongsToMany(
        $this->newQuery(),
        $this,
        'group_item',
        'item_id',
        'group_id',
        'id',
        'item_id',
        'items'
    );
}

Illuminate\Database\QueryException : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'groups.item_id' in 'on clause' (SQL: select `groups`.*, `group_item`.`item_id` as `pivot_item_id`, `group_item`.`group_id` as `pivot_group_id` from `groups` inner join `group_item` on `groups`.`item_id` = `group_item`.`group_id` where `group_item`.`item_id` = 74)

(groups.item_id should be groups.id here)

Even if the above worked, and the correct item IDs were defined from the pivot table, I wonder how could the code handle JOINs and fetching and instantiating records from the other database when calling the relation method ($group->items() in this case).

Any ideas?

Upvotes: 0

Views: 222

Answers (1)

Stefano
Stefano

Reputation: 684

This answer does solve my problem (not sure why initially it did not), and (magically) the relationship works just passing the database name!

public function items(): BelongsToMany
{
    return $this->belongsToMany(
        Item::class,
        "{$this->getConnection()->getDatabaseName()}.group_item",
        null,
        'item_id'
    );
}

Upvotes: 0

Related Questions