Belongstomany Relationship in Laravel Across Multiple Databases

laravel BelongsTo relationship with different databases not working

This is my own solution and it works in general for me but its mega-complicated.

I'm using the builder "from" method to set the table and database correctly inside the subquery. I just need to pass the correct information inside.

Assume the subquery can be as complicated as "genres.sample" or even deeper (which means albums has a relation to genres, and genres has a relation to samples)
this is how

$subQuery = 'genres.samples';
$goDeep = (with (new Album));

$tableBreakdown =  preg_split('/\./', $subQuery); //  = ['genres', 'samples']

// I recurse to find the innermost table $album->genres()->getRelated()->sample()->getRelated()
foreach ($tableBreakdown as $table)
    $goDeep = $goDeep->$table()->getRelated();

// now I have the innermost, get table name and database name

$alternativeConnection =  Config::get("database.connections." . $goDeep->getConnectionName() . ".database"); // should be equal to the correct database name

$tableName = $goDeep->getTable(); // I have to use the table name in the "from" method below

Album::whereHas($subQuery, function ($q) use ($alternativeConnection, $tableName) {
$q->from("$alternativeConnection.$tableName"); 
$q->where(....... yadda yadda);
    });

tl:dr;

Album::whereHas('genres', function ($q) { 
    $q->from('resources.genres')->where(....); 
});

Laravel Many To Many Pivot Table different Database

One way to work around this is using the query builder to create the relationship. In your User model try:

public function conversations()
{
    return DB::connection('db1_connection_name')->table('conversation_user')->where('user_id', $this->id)->get();
}

Apparently you can prefix the connection in a relationship also:

public function conversations() {
    return $this->belongsToMany(User::class, env('DB_CONNECTION_1').'.conversation_user', 'user_id', 'conversation_id');
 }

Access relational data of many to many relationship with multiple database connection with different server

Okay. I got the answer from themsaid on github. He said that many to many relationships on different connections would work in 1 direction only but not the other. Here you can find github issue.

Laravel relations in multiple model with belongsToMany and hasMany

I am also working on an e-commerce project and I have designed my database like below.

A product has multiple product colors but a product color belongs to only one product. product id and color together a compound key(unique) of the product_colors table.

If you have a product T-shirt which id is 101 which product color is red then together 101 and red are a unique product_color.

Product_image belongs to the product and product_colors table. Together product_id and product_color_id are composite key. In case if you have multiple images for the same product id and color then you can save the image as JSON data. which I did for my application.

Although I have designed my database like fig 01. But if we want to separate the colors table then we could design the database like this.

---

Related Topics