使用特定连接的 Laravel 原始查询

Laravel raw query using a specific connection

提问by w1n78

i've been trying to query a moodle database and need to use DB::raw(). but it keeps trying to use the default mysql connection. here's the query i'd like to run.

我一直在尝试查询一个moodle 数据库并且需要使用DB::raw()。但它一直尝试使用默认的 mysql 连接。这是我想运行的查询。

SELECT mdl_course.id, mdl_course.category, mdl_course.fullname, mdl_course_categories.name, mdl_enrol.cost, FROM_UNIXTIME(mdl_course.startdate, '%m/%d/%y') AS startdate
FROM mdl_course
LEFT JOIN mdl_course_categories ON mdl_course_categories.id = mdl_course.category
LEFT JOIN mdl_enrol ON mdl_enrol.courseid = mdl_course.id AND mdl_enrol.enrol = 'paypal';

here's what i've tried in my model

这是我在我的模型中尝试过的

$result = DB::connection('mysql2')->table('mdl_course')
->select(['mdl_course.id', 'mdl_course.category', 'mdl_course.fullname', 'mdl_course_categories.name', 'mdl_enrol.cost', DB::raw("FROM_UNIXTIME(mdl_course.startdate, '%m/%d/%y') AS startdate"])
->join('mdl_course_categories', 'mdl_course_categories.id', '=', 'mdl_course.category', 'left')
->join(DB::raw("LEFT JOIN mdl_enrol ON mdl_enrol.courseid = mdl_course.id AND mdl_enrol.enrol = 'paypal'"))
->where(function($query) use ($id) {
    if ($id)
    {
      $query->where('mdl_course.id', '=', $id);
    }
  })
  ->orderBy('mdl_course.fullname', 'ASC')
  ->get();

it keeps trying to use the default mysql connection, which is empty. from what i've read so far, db::raw() will use the default connection. is this true? how do i go about running this query?

它一直尝试使用默认的 mysql 连接，该连接为空。从我目前读到的内容来看， db::raw() 将使用默认连接。这是真的？我如何去运行这个查询？

回答by Hirendrasinh S. Rathod

For laravel 5.0 Initiate second database in <project path>\config\database.phplike bellow

对于laravel 5.0 Initiate second database in <project path>\config\database.php，如下所示

'connections' => [

    'mysql' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST', 'localhost'),
        'database'  => env('DB_DATABASE', 'forge'),
        'username'  => env('DB_USERNAME', 'forge'),
        'password'  => env('DB_PASSWORD', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ],

    'mysql2' => [
        'driver'    => 'mysql',
        'host'      => env('DB_HOST2', 'localhost'),
        'database'  => env('DB_DATABASE2', 'forge'),
        'username'  => env('DB_USERNAME2', 'forge'),
        'password'  => env('DB_PASSWORD2', ''),
        'charset'   => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix'    => '',
        'strict'    => false,
    ]
]

Do bellow code in model to execute raw query with different connection.

在模型中执行波纹管代码以执行具有不同连接的原始查询。

class DbLog extends Model{
    protected $connection = 'mysql2';
    protected $table = 'users';
    public function select_all_db2_users(){
        $sql = "
            SELECT *
            FROM $this->table
            LIMIT 0,1;
        ";
        $results=DB::connection($this->connection)
            ->select(DB::raw($sql));
        return $results;
    }
}

回答by Marcin Nabia?ek

You should probably use here:

您可能应该在这里使用：

DB::connection('mysql2')->raw("LEFT JOIN mdl_enrol ON mdl_enrol.courseid = mdl_course.id AND mdl_enrol.enrol = 'paypal'")

instead of

代替

DB::raw("LEFT JOIN mdl_enrol ON mdl_enrol.courseid = mdl_course.id AND mdl_enrol.enrol = 'paypal'")

回答by w1n78

ok i figured it out. here's what i ended up with.

好吧，我想通了。这就是我的结果。

$result = DB::connection('mysql2')->table('mdl_course')
  ->select([
      'mdl_course.id',
      'mdl_course.category',
      'mdl_course.fullname',
      'mdl_course_categories.name',
      'mdl_enrol.cost',
      DB::connection('mysql2')->raw("FROM_UNIXTIME(mdl_course.startdate, '%m/%d/%y') AS startdate")
  ])

  ->join('mdl_course_categories', 'mdl_course_categories.id', '=', 'mdl_course.category', 'left')
  ->leftJoin('mdl_enrol',function($join){
    $join->on('mdl_enrol.courseid', '=', 'mdl_course.id')
      ->where('mdl_enrol.enrol', '=', 'paypal');
  })

  ->where(function($query) use ($id) {
    if ($id)
    {
      $query->where('mdl_course.id', '=', $id);
    }
  })
  ->orderBy('mdl_course.fullname', 'ASC')
  ->get();

in my select statement where i used db::raw, i had to also add the custom connection. for the join, i found a way to not use db::raw().

在我使用 db::raw 的 select 语句中，我还必须添加自定义连接。对于加入，我找到了一种不使用 db::raw() 的方法。