Laravel raw query using a specific connection

Question

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

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?

Answer 1

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

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