BelongsToMany with different DB connections

You need to setup a custom model for the pivot relation and then you should be able to use it to store it on the right connection.

Look under "Defining Custom Intermediate Table Models" https://laravel.com/docs/5.8/eloquent-relationships

Example:

// Action class
class Action extends Model
{
    public function repair()
    {
    // use ActiolnRepair model for the pivot table
        return $this->belongsToMany('App\Repair')->using('App\ActionRepair'); 
    }
}

// Repair class
class Repair extends Model
{
    public function action()
    {
    // use ActiolnRepair model for the pivot table
        return $this->belongsToMany('App\Action')->using('App\ActionRepair'); 
    }
}

And then the ActionRepair model needs to extend Illuminate\Database\Eloquent\Relations\Pivot

<?php

namespace App;

use Illuminate\Database\Eloquent\Relations\Pivot;

class ActionRepair extends Pivot
{
    // setup db connections on this model
}

Hi and thanks for your reply,

In the meantime I tried exactly this; with no luck :(

I tried the following approaches:

// App/Action.php

public function repairs()
{
    return $this->belongsToMany(Repair::class)->using(ActionRepair::class);
}

with this Pivot class:

// App/ActionRepair.php

use Illuminate\Database\Eloquent\Relations\Pivot;

class ActionRepair extends Pivot
{
    protected $connection = 'mysql'; // tested 'default' as well...

    // and setting it in the constructor also doesn't work
    public function __construct(array $attributes = [])
    {
        $this->setConnection('mysql'); // tested 'default' as well...
        parent::__construct($attributes);
    }
}

The connection attributes seems to be ignored, even if I write gibberish in there like asdf, it doesn't do anything :(

Oh, that seems a bit weird, since

Illuminate\Database\Eloquent\Relations\Pivot;

in turn extends the

Illuminate\Database\Eloquent\Model;

So I would assume it would use the connection as usual. The only thing in the Pivot class that I can see that maybe affects this is the:

    // This function in 5.8 comes from the trait AsPivot which the extending Pivot relation is using.
    // /src/Illuminate/Database/Eloquent/Relations/Concerns/AsPivot.php
    public static function fromAttributes(Model $parent, $attributes, $table, $exists = false)
    {
        $instance = new static;
        $instance->setConnection($parent->getConnectionName())
                ->setTable($table)
                ->forceFill($attributes)
                ->syncOriginal();
    // more code
    }

Here it seems it sets the connection to the parent model. Maybe this causes the problem? You could try to override this function and see if that changes anything.

Edit: It might break other stuffs, I've no idea how this is used in conjunction with everything else, but that should be easy to find out, if it's the culprit or not.

Hmm... no, this also doesn't help... I just wanted to create an GitHub issue and found this one $connection of custom pivot model ignored #25140.

So I guess, I need to to some "manual" work around :/

Thanks for your help!

Alrighty then, interesting that this isn't possible already to be honest. Well I guess you just need to find the right function to ovverride. Good luck!

I'm trying now... If there are interesting results (instead of work arounds...) I'll post an update.

Meanwhile, I also opened an issue on the https://github.com/laravel/ideas repo

@toby any luck on a solution? I am in the same scenario.

I am able to get the relationship from MS SQL to MySQL with my pivot table on the MySQL side, but I cannot get the relationship from MySQL to MS SQL working.

I found this https://gist.github.com/rodrigopedra/6d8b6c2e4be5e1cce66549fc079b3d26 which seemed like it would resolve the issue with no luck.