How to use Join in BD Query Laravel using two different databases?

Question

I got this piece of code bellow to show a table in a page, but it doesn't work. I got this error where says that the command was denied.

$posts = DB::connection('mysql2')
->table('wp_rocketsciencebrposts')
->join('users', 'users.rsbwordpressid', '=', 'wp_rocketsciencebrposts.post_author')
->select('ID', 'post_title', 'post_status', 'post_author', 'post_date', 'users.name')
->whereIn('post_status', ['publish', 'private'])
->where('post_type', 'post')
->orderBy('id', 'desc')
->paginate(15, ['*'], 'posts');

SQLSTATE[42000]: Syntax error or access violation: 1142 SELECT command denied to user 'lovel095_heaven'@'177.134.6.23' for table 'users' (SQL: select count(*) as aggregate from wp_rocketsciencebrposts inner join mysql.users on users.rsbwordpressid = mysql2.wp_rocketsciencebrposts.post_author where post_status in (publish, private) and post_type = post)

Both databases are on the same server.

The table "wp_rocketsciencebrposts" comes from "lovel095_rocketsciencebr" database.
The table "users" comes from "lovel095_centralrsb" database.

In my .env file i git this:

DB_CONNECTION=mysql
DB_HOST=br862.hostgator.com.br
DB_PORT=3306
DB_DATABASE=lovel095_centralrsb
DB_USERNAME=(myusername)
DB_PASSWORD=(mypassword)

DB_CONNECTION_SECOND=mysql
DB_HOST_SECOND=br862.hostgator.com.br
DB_PORT_SECOND=3306
DB_DATABASE_SECOND=lovel095_rocketsciencebr
DB_USERNAME_SECOND=(myusername)
DB_PASSWORD_SECOND=(mypassword)

In my config>database.php file, i got this:

'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    '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,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],

'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'),
],

Pls, help!

score 0 · Answer 1 · answered Mar 07 '20 at 15:34

0

Most of database is not support a cross database join, I believe SQL server support it but it must be on the same server. Instead of forcing your application to joining two different database, just simply use the database separately.

In your case, simply select the posts first, then select the user name using foreach. Here's the workaround :

$posts = DB::connection('mysql2')
->table('wp_rocketsciencebrposts')
->select('ID', 'post_title', 'post_status', 'post_author', 'post_date')
->whereIn('post_status', ['publish', 'private'])
->where('post_type', 'post')
->orderBy('id', 'desc')
->paginate(15, ['*'], 'posts');

foreach ($posts as $key => $post){
    $user = DB::connection('mysql')
            ->table('lovel095_centralrsb')
            ->where('id', $post->post_author)
            ->first();
    $posts[$key]->post_author_name = $user->name;
}

answered Mar 07 '20 at 15:34

I Gusti Ngurah Arya Bawanta

76
6

Yes, the databases are on the same server. I already implemented your solution, but it generates another problem, a DB Query inside a foreach loop makes my page slow since it does make 15 database requests at once. My workaround was to implement a join, but as you can see, i got problems with that too. – Neyelson Alves Mar 07 '20 at 16:15
What database that you use ? – I Gusti Ngurah Arya Bawanta Mar 07 '20 at 16:19
I'm using MySQL with PhpMyAdmin. – Neyelson Alves Mar 07 '20 at 16:20
You can try this solution https://stackoverflow.com/questions/5698378/join-between-tables-in-two-different-databases – I Gusti Ngurah Arya Bawanta Mar 07 '20 at 16:30
If eloquent is a bit complicated, you can always use query builder – I Gusti Ngurah Arya Bawanta Mar 07 '20 at 16:32

score 0 · Answer 2 · answered Mar 07 '20 at 17:31

Hello I'm not sure you need to configure 2 databases to execute that kind of query. Just make sure the credentials you are using to connect to one database is able to access both, it works with MySql.

From here i suggest you read that other stackoverflow answer

Basically you need to prefix tables with database name for the join to work. I suggest you first try this from PhpMyAdmin then translate into Laravel Query Builder.

Remember that you can use entire raw queries in the Query builder, like :

DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = '$someVariable'") );

How to use Join in BD Query Laravel using two different databases?

2 Answers2

Linked