belongsToMany relationship in Laravel across multiple databases

Question

I have model A and model B which lie in two different databases.

Now I have a pivot_table called a_bs in the same database as model A.

I've setup the belongsToMany relatinoship like this in model A

public function bs()
{
    return $this->belongsToMany('B', 'a_bs', 'a_id', 'b_id');
}

When I try to access this relationship like so:

$a = A::find($id);
print_r($a->bs->lists('id'));

I get an error that my pivot table doesn't exist in model B's database. Which is obviously correct since the pivot table is in model A's database. How can I let Laravel know that?

Do not suggest to put the pivot table in model B's database

Answer 1

Very simply:

public function bs()
{
    $database = $this->getConnection()->getDatabaseName();
    return $this->belongsToMany('B', "$database.a_bs", 'a_id', 'b_id');
}

I'm obtaining the database name dynamically because my connection is configured based off an environment variable. Laravel seems to assume the pivot table to exist in the same database as the target relation, so this will force it to look instead to the database corresponding to the model that this method is in, your 'A' realm.

---

If you're not worried about SQLite databases, i.e. in the scope of a unit-test, that's all you need. But if you are, keep reading.

---

Firstly, the previous example isn't sufficient on its own. The value of $database would end up being a file-path, so you need to alias it to something that won't break an SQL statement, and make it accessible to the current connection. "ATTACH DATABASE '$database' AS $name" is how you do that:

public function bs()
{
    $database = $this->getConnection()->getDatabaseName();
    if (is_file($database)) {
        $connection = app('B')->getConnection()->getName();
        $name = $this->getConnection()->getName();
        \Illuminate\Support\Facades\DB::connection($connection)->statement("ATTACH DATABASE '$database' AS $name");
        $database = $name;
    }
    return $this->belongsToMany('B', "$database.a_bs", 'a_id', 'b_id');
}

Warning: Transactions muck this up: If the current connection is using transactions, the ATTACH DATABASE statement will fail. You can use transactions on it after executing that statement though.

Whereas, if the related connection uses transactions, the resulting data will be silently rendered invisible to the current one. This drove me nuts for longer than I'd care to admit, because my queries ran without error, but kept coming up empty. It seems only data truly written to the attached database is actually accessible to the one it's attached to.

So, after being forced to write to your attached database, you may still want your test to clean up after itself. A simple solution there would be to just use $this->artisan('migrate:rollback', ['--database' => $attachedConnectionName]);. But if you have multiple tests that need the same tables, this is not very efficient, as it forces them to have to rebuild them each time.

A better option would be to truncate the tables, but leave their structure in tact:

//Get all tables within the attached database
collect(DB::connection($database)->select("SELECT name FROM sqlite_master WHERE type = 'table'"))->each(function ($table) use ($name) {
        //Clear all entries for the table
        DB::connection($database)->delete("DELETE FROM '$table->name'");
        //Reset any auto-incremented index value
        DB::connection($database)->delete("DELETE FROM sqlite_sequence WHERE name = '$table->name'");
    });
}

This will wipe all data from that connection, but there's no reason you couldn't apply some kind filter to that however you see fit. Alternatively, you could take advantage of the fact that SQLite DBs are easily-accessible files, and just copy the attached one to a temp file, and use it to overwrite the source after the test is done executing. The result would be functionally identical to a transaction.