Level 14
As far as I know joins will only work if it is in the same database, right?
Feb 4, 2019
3
Level 14
How could I join 2 collections from 2 different databases?
I have 2 tables in 2 different databases - users and posts. I want to retrieve all users AND all user's posts (count) in ONE collection. So that the end result would look like this.
$model->username // john
$model->fullname // John Smith
$model->user_avatar // jsm2m.jpg
$model->user_posts_count // 3 <-- from different database
Of course I could first retrieve all users and then in another collection get all his posts count like this...
// I don't want 2 different collection like this....
$users = Users::all();
$user_posts_count = Posts::where('user_id', 1)->count();
But I want it all to be in one collection (variable) for convenience.
Users table
id | username | fullname | user_avatar
1 john John Smith jsm2m.jpg
2 martin Bob Martin jk3i302.jpg
Posts table (different database)
id | user_id | post_body
1 1 hello world
2 1 howdy yall
3 1 what is up?
Level 31
In your config/database.php file, you can define new connections. Then, in you model, you can use this connection via the protected $connection = 'another-mysql-connection-name'; property.
Example:
// config/database.php
return [
'default' => env('DB_CONNECTION', 'mysql'),
'connections' => [
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
],
'another-mysql-connection-name' => [
'driver' => 'mysql',
'host' => '192.168.0.200',
'port' => '3306',
'database' => 'forge2',
'username' => 'forge2',
'password' => 'secret2',
'unix_socket' => '',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
'engine' => null,
],
],
//...
];
See: https://laravel.com/docs/5.7/eloquent#defining-models under Database Connection
Level 31
You can't join, but you can use the regular relationship methods
Please or to participate in this conversation.