Union between databases in laravel 8

user_8411150 0
Viewed 50

Hello I am making union between 2 databases, they are with same structure table:

use App\map1_weather;
use App\map2_weather;

$weather2 = map2_weather::whereBetween('recordtime', $dateScope)
->selectRaw('recordtime,tempout as temp_map2_weather,hitemp');
$weather = map1_weather::whereBetween('recordtime', $dateScope)
->selectRaw('recordtime,tempout,hitemp')
->orderBy('recordtime', 'ASC')
->union($weather2)
->get();

The error that I have is:

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "map2_weather" does not exist LINE 1

Is it the right aproach? The goal is to display 2 columns "teomput" in one table from different DBs

laravel postgresql
2 Answers

This error is due to multiple databases. You need to specify database connection on both models if you are using multiple database collection, the error is due to your default db connection is not able to located some table used in query.

Specify connection property in both models map1_weather and map2_weather

Just add this line protected $connection = "connection_name";

user_59217770

Union across different database connections is not really allowed. If your table is on the same database you can probably just refer to the table using its full DB.table name instead of using a different connection e.g.

class map1_weather extends Model {
    protected $table = 'db.map1_weathers';
    // Don't specify a different connection. It might not be needed

}

Of course you can keep the connection variable as well if you want to but it will be ignored when doing operations like joins and unions since they don't generally work across different database connections (which might be on different databases using different technologies)

user_4878130
Related