如何在使用两个不同数据库的BD Query Laravel中使用Join? [英] How to use Join in BD Query Laravel using two different databases?

问题描述

我得到了下面的代码，可以在页面中显示一个表格，但这是行不通的.我收到此错误，提示该命令被拒绝.

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]:语法错误或访问冲突:1142 SELECT命令 拒绝表'users'的用户'lovel095_heaven'@'177.134.6.23' (SQL:从wp_rocketsciencebrposts中选择count(*)作为聚合 users.rsbwordpressid上的内部联接mysql.users = mysql2.wp_rocketsciencebrposts.post_author其中post_status 在(发布，私有)和post_type =发布)

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)

两个数据库都位于同一服务器上.

表"wp_rocketsciencebrposts" 来自"lovel095_rocketsciencebr" 数据库.
表用户" 来自"lovel095_centralrsb" 数据库.

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

在我的.env文件中，我对此进行了搅拌:

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)

在我的config> database.php文件中，我得到了:

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

请帮助！

推荐答案

您好，我不确定您需要配置2个数据库来执行这种查询. 只要确保用于连接到一个数据库的凭据能够访问这两个数据库，它就可以与MySql一起使用.

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.

我从这里建议您阅读其他stackoverflow答案

基本上，您需要为表加上数据库名称前缀才能使联接起作用. 我建议您首先从PhpMyAdmin尝试一下，然后将其翻译成Laravel查询生成器.

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'") );

这篇关于如何在使用两个不同数据库的BD Query Laravel中使用Join?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！