Eager loading with same values from other database-CodePudding

The case to be solved consists of two different applications, each with its own database.

Database A has the cars table.

In App B it is necessary to create a collection of countries, each with a list of cars available for use by the company.

For this, APP B has to communicate with APP A's Database to obtain the id of the cars (but not the name of the car which, over time, may undergo changes in its designation, which could bring problems of consistency between databases).

Maybe this is not be the best architecture, but it is a possible solution (I gladly accept other suggestions)

DATABASE A on APP A

Table cars
id_car   name
1        BMW
2        FORD
3        Renault
4        FIAT
5        JAGUAR

DATABASE B on APP B

Table countrys
id_c   name
1      Germany
2      USA
3      Italy
4      France

Table available
id_av   id_car
1       1
2       3
3       5

Table countrys_available
id    id_c   id_av
1      1      2
2      1      4
3      1      5
4      2      1
5      2      3
6      3      1

Model Country

class Country extends Model
{
    protected $table = 'countrys';
    protected  $primaryKey = 'id_c';
    
    protected $fillable = [
        'name' 
    ];
    
    /**
     * @return BelongsToMany
     */
    public function availables()
    {     
        return $this->belongsToMany(Available::class, 
                                    'countrys_availables', 
                                    'id_c', 
                                    'id_av');
    }
}

Model Available

class Available extends Model
{
    protected $table = 'availables';
    protected  $primaryKey = 'id_av';
    
    protected $fillable = [
        'id_car' 
    ];
    
    /**
     * @return BelongsToMany
     */
    public function countrys()
    {     
        return $this->belongsToMany(Country::class, 
                                    'countrys_availables', 
                                    'id_c', 
                                    'id_av');
    }
}

Problem to solve:

As I mentioned id_car from the table availables was obtained from DATABASE A.

Now I need to get the name of the corresponding car in DATABASE A.

Query

 $data = Country::with('availables')->get();

    foreach ($data->availables() as $item) { 
       //The objective is to return from Database A the id_car and the car name of each 'available'. Example: 'id_car':2, 'name': 'Ford'

    }

}

CodePudding user response：

Assuming that you used Database B as your main database in laravel.

Assumed .env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=databaseB
DB_USERNAME=databaseBUser
DB_PASSWORD=xxxxx

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=127.0.0.1
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=databaseB
DB_USERNAME_SECOND=databaseBUser
DB_PASSWORD_SECOND=xxxxx

Assumed config/database.php

'mysql' => [
    'driver'    => env('DB_CONNECTION'),
    'host'      => env('DB_HOST'),
    'port'      => env('DB_PORT'),
    'database'  => env('DB_DATABASE'),
    'username'  => env('DB_USERNAME'),
    'password'  => env('DB_PASSWORD'),
],

'mysql2' => [
    'driver'    => env('DB_CONNECTION_SECOND'),
    'host'      => env('DB_HOST_SECOND'),
    'port'      => env('DB_PORT_SECOND'),
    'database'  => env('DB_DATABASE_SECOND'),
    'username'  => env('DB_USERNAME_SECOND'),
    'password'  => env('DB_PASSWORD_SECOND'),
],

Since you want to query to another database. You can use Laravel multiple database connections: https://laravel.com/docs/9.x/database#using-multiple-database-connections

You can get the car name using these code (UNTESTED. FYI this is just a quick code I wrote in rush)

$countries = Country::with('availables')->get();

foreach ($countries as $country) {
    $car_ids = $country->availables->pluck('id_car');

    $cars = \DB::connection('mysql2')->table('cars')->whereIn('id_car', $car_ids)->get();

    foreach ($cars as $car) {
        echo $car->name;
    }
}