BelongToThrough does not respect Model's connection

[tklie]

BelongToThrough does not respect Model's connection

Database structure

Let's assume a database structure with three models in two different database schemas.

DB: dev_user_db

sellers
- id

DB: dev_shop_db

categories
- id
- seller_id

items
- id
- category_id

Database config

And the following config/database.php which let's us switch between a development and produciton database using the .env file:

return [
    'user_db' => [
        // Some other things here
        'database' => env('USER_DATABASE')
    ],
    'shop_db' => [
        // Some other things here
        'database' => env('SHOP_DATABASE')
    ],
];

Relationships

The relationships are defined as follows:

class Seller extends Model
{
    protected $connection = 'user_db';
    protected $table = 'sellers';

    // Some hasMany here ...
}

class Category extends Model
{
    protected $connection = 'shop_db';
    protected $table = 'categories';

    public function seller()
    {
        return $this->belongsTo(Seller::class);
    }
}

class Item extends Model
{
    protected $connection = 'shop_db';
    protected $table = 'items';

    public function category()
    {
        return $this->belongsTo(Category::class);
    }

    public function seller()
    {
        return $this->belongsToThrough(Seller::class, Category::class);
    }
}

Issue

When trying to retrieve an Item with it's Seller:

public function getItems()
{
    return Item::with(['seller'])->get();
}

The following SQL Exception is thrown:

"SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dev_user_db.categories' doesn't exist 
(SQL: select `seller`.*, `categories`.`id` as `laravel_through_key` from `seller` inner join `categories` on `categories`.`seller_id` = `seller`.`id` where `categories`.`id` in (1, 2))"

As we can see, the query tries to find dev_user_db.categories even though $connection = 'shop_db' was defined on the Category model.

A possible suggested solution to this was to also define the schema's name in the table parameter like $table = 'dev_shop_db.categories. Unfortunately this is not possible here, since the schema's name changes depending on the .env file - and expressions are not allowed as field default values.

So it would be great if the belongsToThrough relationship used the defined $connection attributes to determine the correct database schemas to query.

[staudenmeir]

I'll look into it.

[staudenmeir]

A possible suggested solution to this was to also define the schema's name in the table parameter like $table = 'dev_shop_db.categories. Unfortunately this is not possible here, since the schema's name changes depending on the .env file - and expressions are not allowed as field default values.

Have you tried overriding the model's getTable() method? This allows you to use expressions.

[tklie]

You are correct. Overriding the getTable() method like so does solve the SQL error (the relationship is still returning null, but that's probably on me somehow).

public function getTable()
{
    return env('SHOP_DATABASE', $this->connection) . '.' . $this->table;
}

Still feels like the framework should do this anyway. Otherwise, defining the $connection property seems only 50% useful. But I'm guessing that's then more of an Eloquent issue rather than this package's?

[staudenmeir]

Yeah, this is native Laravel behavior.

The whole topic is quite complex and I don't think Laravel is going to handle this case anytime soon.

[tklie]

Alright, thanks for looking into it though.