CODEWITHSUNDEEP
laravel
NiRR
NiRR

Reputation: 5012

laravel BelongsTo relationship with different databases not working

I've seen in several places to "stay away" from this, but alas - this is how my DB is built:

class Album extends Eloquent {

   // default connection

   public function genre() {
       return $this->belongsTo('genre');
   }

and the Genre table:

class Genre extends Eloquent {
    protected $connection = 'Resources';

}

My database.php: 

'Resources' => array(
                    'driver'    => 'mysql',
                    'host'      => 'localhost',
                    'database'  => 'resources',
                    'username'  => 'user',
                    'password'  => 'password',
                    'charset'   => 'utf8',
                    'collation' => 'utf8_unicode_ci',
                    'prefix'    => '',
            ),

 'mysql' => array(
                    'driver'    => 'mysql',
                    'host'      => 'localhost',
                    'database'  => 'my_data',
                    'username'  => 'user',
                    'password'  => 'password',
                    'charset'   => 'utf8',
                    'collation' => 'utf8_unicode_ci',
                    'prefix'    => '',
            ),

and when I try to run

Album::whereHas('genre', function ($q) {
   $q->where('genre', 'German HopScotch'); 
});

it doesn't select properly (doesn't add the database name to the table "genres"):

Next exception 'Illuminate\Database\QueryException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'my_data.genres' doesn't exist

Its important to note that this works perfectly: 

Album::first()->genre;

Update

The best I've found so far is to use the builder's "from" method to specifically name the correct connection. I've discovered that the builder inside the query can receive "from"

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

This is a decent solution but it requires me to dig in the database php and find a good way to get the proper table and database name from the relation 'genre'.

I will appreciate if anyone else can build on this solution and make it more general.

Upvotes: 41

Views: 46724

Answers (13)

Shah Rose
Shah Rose

Reputation: 13

1: This is my .env with two database connection

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=foreign_key_test
DB_USERNAME=root
DB_PASSWORD=

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=foreign_key_test_2
DB_USERNAME_SECOND=root
DB_PASSWORD_SECOND=

2: in my config/database.php

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

    'mysql2' => [
        'driver' => env('DB_CONNECTION_SECOND'),
        'host' => env('DB_HOST_SECOND', '127.0.0.1'),
        'port' => env('DB_PORT_SECOND', '3306'),
        'database' => env('DB_DATABASE_SECOND', 'forge'),
        'username' => env('DB_USERNAME_SECOND', 'forge'),
        'password' => env('DB_PASSWORD_SECOND', ''),
        'unix_socket' => '',
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => null,
    ],

3: State model from other database

class State extends Model
{
    use HasFactory;

    protected $table = 'foreign_key_test_2.states';
    protected $connection = 'mysql2';
    protected $primaryKey = 'id';
    protected $fillable = [
        'name'
    ];
}

4: Country model from 1st database where i made my relation with other database table

class Country extends Model
{
    use HasFactory;

    protected $connection = 'mysql';

    protected $table = 'countries';

    protected $primaryKey = 'id';

    protected $fillable = [
        'name',
        'state_id'
    ];


    public function state()
    {
        return $this->belongsTo(State::class);
    }
}

5: Final step is my route

Route::get('city', function(){
    return $country = Country::with('state')->get();
});

6: this is working fine for me. Let me know with your experience.

Upvotes: 1

ssi-anik
ssi-anik

Reputation: 3714

Just in case someone reaches here.

When you are fetching data using a relationship from different database connections, make sure that your related table has the $connection property defined even if it's the default connection.

Account Model:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Account extends Model {

    protected $connection = 'different_connection';

    public function verifiedBy()
    {
        return $this->belongsTo(User::class, 'verified_by');
    }
}

User Model:

<?php

namespace App\Models;

use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable {
    // this one uses the default - `database.default` connection
    public function approved()
    {
        return $this->hasMany(Account::class, 'verified_by');
    }
}

As soon as I do Account:find($id)->verifiedBy, it will throw error. Look at the database name and you'll find. But if you do, User::find($id)->approved, it will work fine, because the Account model has the connection defined. And not the vice-versa.

So, just to be safe, if you deal with multiple database connections, put the $connection property in the models.

Laravel's implementation

Upvotes: 7

Saeid Dadkhah
Saeid Dadkhah

Reputation: 411

I used the following method to use the default connection.

use Illuminate\Database\Eloquent\Model as MainModel;
use Illuminate\Support\Facades\Config;

class BaseModel extends MainModel
{
    function __construct(array $attributes = [])
    {
        // laravel bug
        // in belongsTo relationship, default connection not used
        $this->connection = Config::get('database.default');
        parent::__construct($attributes);
    }
}

Upvotes: 0

Alex
Alex

Reputation: 260

This is the way it worked for me:

In my .env and config/database.php i have defined my other connection => How to use multiple databases in Laravel

I updated my model this way:

class MyOtherDBModel extends Model
{
    protected $table = 'tablename';
    protected $connection = 'mysql2';

    public function __construct(array $attributes = [])
    {
        $this->table = env('DB_DATABASE_2').'.'.$this->table;
        parent::__construct($attributes);
    }
}

class MyModel extends Model
{
    public function myOtherModel()
    {
        return $this->belongsTo(MyOtherDBModel::class, 'field', 'field');
    }
}

Now i can call

$query = MyModel::whereHas('myOtherModel');

Upvotes: 18

Manu
Manu

Reputation: 351

you should clone before otherwise you're changing the default connection of the model. it creates side effect.

class Album extends Eloquent {
    public function genre() {
        $newResource = clone $this;
        return $newResource->setConnection('Resources')->belongsTo('genre');
    }
}

Upvotes: 5

Sabyasachi Ghosh
Sabyasachi Ghosh

Reputation: 1500

Solution for laravel v5.7 and above

class Album extends Eloquent {

   // default connection

   public function genre() {
       return $this->setConnection('Resources')->belongsTo('genre');
   }
...
}

Upvotes: 45

AnhellO
AnhellO

Reputation: 1069

I was facing the same issue on Laravel 5.6. On a Many-to-Many scenario, and supposing the connection from my ModelA was the default one, what I did was the following:

1.- Prefix the schema name in the relationships:

// From ModelA and default connection (a.k.a connection1)
$this->belongsToMany('ModelB', 'schema.pivot-table-name');

// From ModelB and connection2
$this->belongsToMany('ModelA', 'schema.pivot-table-name');

2.- Overwrite connection parameter within the ModelB class and also specify the schema as a prefix in the overwritten $table attribute e.g.

protected $connection = 'connection2';

protected $table = 'connection2-schema-name.table';

3.- In case of requiring a custom behavior for the pivot table, what I did was just to implement the required model and specify it via the ->using('PivotModel'); function on the models relationships (as stated in the documentation). Finally I did the same as in the point 2 above, but on the pivot model

I haven't tried it yet, but I guess the same can be done for other kind of relationships, at least for the basic ones (One-to-One, One-to-Many, etc)

Upvotes: 4

amflare
amflare

Reputation: 4113

It looks like Eager Loading will do what you want to do

Album::with(['genre' => function ($q) {
   $q->connection('Resources')
     ->where('genre', 'German HopScotch'); 
}]);

Upvotes: 7

Carlos Escobar
Carlos Escobar

Reputation: 101

Add the connection variable with the default connection on the genre model:

protected $connection = 'mysql';

I had some problems with the relationships by not adding this.

Upvotes: 8

hawx
hawx

Reputation: 1669

I had the same issue when relationship wasn't working off the model connection.

My solution was to override the belongsToMany method on the model trying to establish. See example below.

<?php

namespace App\Model;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;

class ConnectionModel extends Model
{
    /**
     * Override method to allow inheriting connection of parent
     *
     * Define a many-to-many relationship.
     *
     * @param  string  $related
     * @param  string  $table
     * @param  string  $foreignKey
     * @param  string  $otherKey
     * @param  string  $relation
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany|BelongsToMany
     */
    public function belongsToMany($related, $table = null, $foreignKey = null, $otherKey = null, $relation = null)
    {
        // If no relationship name was passed, we will pull backtraces to get the
        // name of the calling function. We will use that function name as the
        // title of this relation since that is a great convention to apply.
        if (is_null($relation)) {
            $relation = $this->getBelongsToManyCaller();
        }

        // First, we'll need to determine the foreign key and "other key" for the
        // relationship. Once we have determined the keys we'll make the query
        // instances as well as the relationship instances we need for this.
        $foreignKey = $foreignKey ?: $this->getForeignKey();

        $instance = new $related;

        // get connection from parent
        $instance->setConnection(parent::getConnectionName());

        $otherKey = $otherKey ?: $instance->getForeignKey();

        // If no table name was provided, we can guess it by concatenating the two
        // models using underscores in alphabetical order. The two model names
        // are transformed to snake case from their default CamelCase also.
        if (is_null($table)) {
            $table = $this->joiningTable($related);
        }

        // Now we're ready to create a new query builder for the related model and
        // the relationship instances for the relation. The relations will set
        // appropriate query constraint and entirely manages the hydrations.
        $query = $instance->newQuery();

        return new BelongsToMany($query, $this, $table, $foreignKey, $otherKey, $relation);
    }
}

Upvotes: 1

NiRR
NiRR

Reputation: 5012

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(....); 
});

Upvotes: 8

Jeremie Ges
Jeremie Ges

Reputation: 2745

To start change 'Resources' in database.php by 'resources', will be better !

I'm curious, can you try that ?

Album::whereHas('genre', function ($q) {
   $q->setConnection('resources')->where('genre', 'German HopScotch'); 
});

Upvotes: -2

Jamesking56
Jamesking56

Reputation: 3901

I found a really good article for this here: http://fideloper.com/laravel-multiple-database-connections

You basically have to specify your two connections in your config file like so:

<?php
return array(

    'default' => 'mysql',

    'connections' => array(

        # Our primary database connection
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'host1',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        # Our secondary database connection
        'mysql2' => array(
            'driver'    => 'mysql',
            'host'      => 'host2',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),
);

So your two connections are aliased to mysql and mysql2.

Then you can tell eloquent which 'alias' to use like so:

<?php

class SomeModel extends Eloquent {

    protected $connection = 'mysql2';

}

Then you can setup your relationships like normal.

tl;dr: Basically instead of specifying the table name as $connection in eloquent, specify the connection alias in your configuration and it should work.

Upvotes: -1

Related Questions