Laravel HasMany across multiple connections

Question

I want to retrieve Offers related to Offer on a second table offer_related because I can't change the schema of the Offer table.

I have two databases on different connections, offers on one, and offer_related on another.

For the sake of argument, I'm going to name databases as follows for clarity in my examples with regards to which can change and which can't.

• database containing offers henceforth known as immutable
• database containing offer_related henceforth known as mutable

Example schema is as follows

connection1.mutable.offer_related

offer_id | related_offer_id
---------------------------
1        | 2
1        | 3

connection2.immutable.offers

id | name
---------------------------
1  | foo
2  | bar
3  | baz

I'm assuming it'd be a belongsToMany relationship, but I can't seem to get it right.

return $this->belongsToMany(Offer::class, 'immutable.offer', 'id');

// Syntax error or access violation: 1066 Not unique table/alias: 'offer'

I've also tried to manually build out a belongsToMany relationship with a custom query with no success.

I'd like to be able to call

Offer::find(1)->related; // Offer(2), Offer(3)

Answer 1

i have this problem too, then solved with a trait:

<?php 

namespace App\Traits\Model;

use Illuminate\Support\Str;

trait HasCrossDatabaseRelation {

    public function getTable() {
        if (! isset($this->table)) {
            $this->table = str_replace(
                '\\', '', Str::snake(Str::plural(class_basename($this)))
            );
        }

        $configConnections = config('database.connections');

        $databaseName = $configConnections[$this->getConnectionName()]['database'];

        return $databaseName . "." . $this->table;
    }

}

then use that trait and set the $connection property on each models. hope this help

Answer 2

Change the relationship to:

    return $this->belongsToMany(Offer::class, 'mutable.offer_related', 
                                'offer_id', 'related_offer_id');

Your original query was trying to establish a relationship without using the relation table (offer_related). That is the problem.