Automatically append database name to database table names

Question

Is it possible to automatically append database name to a table name in laravel?

The issue is that I have to join data from multiple databases in single queries and sometime I am having to manually replace template names, which is a lot of hassle.

The only solution that I found is that I can append database name to the table name within a model, i.e.

class User extends Model
{
   protected $table = 'database_name.table_name';
}

But with above we are losing support for table prefixes.

Example when database name is not applied:

$userQuery = User::where('id', 1)
   ->with('settings')
   ->select('some data');

DB::connection('x')
   ->table('table-on-different-connection')
   ->insertUsing(['some columns'], $userQuery);

$userQuery is on a different connection and database_name was not applied to the tables within that part of the query. Hence why insertUsing is trying to perform joins on connection x.

Answer 1

Laravel is not appending database name when generating SQL statements. To resolve that, you need to create your own MySQL wrapper and append the database name to the table name that way.

This is where the issue takes place: vendor\laravel\framework\src\Illuminate\Database\Query\Grammar.php

    public function wrapTable($table)
    {
        if (! $this->isExpression($table)) {
            return $this->wrap($this->tablePrefix.$table, true);
        }

        return $this->getValue($table);
    }

You need to override wrapTable method and append database name to the table that way.

i.e.

    public function wrapTable($table)
    {
        $databaseName = $this->wrap('my_database'); // dynamically defined name here

        if (! $this->isExpression($table)) {
            $tableName = $this->wrap($this->tablePrefix.$table, true);

            return "{$databaseName}.{$tableName}";
        }

        return $this->getValue("{$databaseName}.{$table}");
    }

How you go about extending Grammar and override this method depends on your application and your needs. This can be done globally (i.e. via AppProvider) or for an individual query.