Laravel Eloquent: How to pass connection name to relationship model

Question

I have two models, one mongo model extending Jenssegers\Model and other sql model extending Illuminate\Model. This sql model doesn't have connection name defined as we're having multiple database connectivity have same table in each database.

Mongo Model Comment.php

<?php
namespace App\Models\Mongo;

use Jenssegers\Mongodb\Eloquent\Model as Eloquent;

class Comment extends Eloquent
{
    /** @var string Mongo Connection Name */
    protected $connection = 'mongodb';

    /** @var string Mongo Collection Name */
    protected $collection = 'comments';

    /** @var bool Enable/Disable Timestamp */
    public $timestamps = true;

    /** @var date Date format */
    protected $dateFormat = 'Y-m-d H:i:s';

    public function userProfile()
    {
        return $this->belongsTo('\\App\\Models\\Sql\\UserDefaultProfile', 'created_by', 'user_code');
    }
}

Sql Model UserProfile.php

<?php
namespace App\Models\Sql;

use Illuminate\Database\Eloquent\Model;
use Jenssegers\Mongodb\Eloquent\HybridRelations;

class UserDefaultProfile extends Model
{
    use HybridRelations;

    /** @var string Table Name */
    protected $table = 'user_default_profile';

    /** @var bool Enable/Disable Timestamp */
    public $timestamps = false;
}

I have multiple database connection add to Capsule

try {
    $getDatabaseList = StoreDatabaseCredential::all();
} catch (Exception $exception) {

}

foreach ($getDatabaseList as $database) {
    if (strtolower($database->database_type) == 'mysql') {
        $db->addConnection([
            'driver'    => 'mysql',
            'host'      => $database->database_router_read_host,
            'port' => $database->database_router_read_port,
            'database'  => $database->database_name,
            'username'  => $database->database_user,
            'password'  => $database->database_password,
            'charset'   => 'utf8',
            'collation' => 'utf8_general_ci',
            'prefix'    => '',
        ], $database->connection_name);
    }
}

so multiple database connection are available

Now problem is when I called the eloquent with relation, am getting Database [Default] not configured. I'm getting this error because UserProfile Model doesn't have any connection defined. So, please someone who can tell how to pass connection name to relation model.

try {    
    $comments = Comment::where([
        'in_reply_to_content_id' => $contentId,
        'super_store_id' => $superStoreId,
        'is_deleted' => 0
    ])->with([
        'userProfile' => function ($query)  use ($dbConnectionName) {   
            $query->select('id', 'user_code', 'mobile', 'name', 'profile_pic_url');
        }
    ])->skip($offset)->take($itemsPerPage)->orderBy('created_at', 'desc')->get();

    Utils::printData($contentComments);
    exit();
} catch (\Throwable $exception) {
    Utils::printData($exception->getMessage());
    exit();
}

so, is it possible to do something like this with relation

with([
    'userProfile' => function ($query)  use ($dbConnectionName) {   
        $query->setConnection($dbConnectionName)->select(
            'id', 'user_code', 'mobile', 'name', 'profile_pic_url'
        );
    }
])->skip($offset)->take($itemsPerPage)->orderBy('created_at', 'desc')->get();

Answer 1

You can extend the base Model class for your UserProfile model, override getConnectionName method and get connection name from a static class variable that you set with static method

for example:

use Illuminate\Database\Eloquent\Model;

class RelationWithChangingConnection extends Model
{
    static $connectionName;

    static public function setConnectionNameForRelation($connectionName)
    {
        static::$connectionName = $connectionName;
    }

    public function getConnectionName()
    {
        $this->connection = static::$connectionName;
        return parent::getConnectionName();
    }
}

class UserDefaultProfile extends RelationWithChangingConnection
{
    //...
}

and then

with([
    'userProfile' => function ($query)  use ($dbConnectionName) {              
        UserDefaultProfile::setConnectionNameForRelation($dbConnectionName);

        $query->setConnection($dbConnectionName)->select(
            'id', 'user_code', 'mobile', 'name', 'profile_pic_url'
        );
    }
])...

However, this looks ugly. Also you can switch database connections through switching configuration files as suggested here

UPD: Here I came across another possible solution, but haven't checked it yet - you can write your relation subquery like this:

...
->with([
    'userProfile' => function ($query)  use ($dbConnectionName) {  
        $query->from('[yourdatabase].[dbtable]')->select('id','user_code', 'mobile', 'name', 'profile_pic_url');
}
...

and it will connect to database and db table that you set.