使用特定连接的Laravel原始查询 [英] Laravel raw query using a specific connection

问题描述

我一直在尝试查询穆德尔数据库，并且需要使用DB :: raw().但它一直尝试使用默认的mysql连接.这是我要运行的查询.

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.

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

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

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?

推荐答案

对于laravel 5.0 Initiate second database in <project path>\config\database.php像波纹管一样

For laravel 5.0 Initiate second database in <project path>\config\database.php like 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;
    }
}

这篇关于使用特定连接的Laravel原始查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！