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

Question

我一直在尝试查询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';

以下是我在我的模型中尝试的内容

$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();

它一直尝试使用默认的mysql连接，该连接为空。到目前为止，我所读到的db::raw()将使用缺省连接。这是真的吗？如何开始运行这个查询？

Answer 1

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

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

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

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;
    }
}

Answer 2

您可能应该在这里使用：

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

而不是

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

Answer 3

好了，我想通了。这就是我最终得到的结果。

$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();

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