CODEWITHSUNDEEP
mysqllaraveleloquent
boomdrak
boomdrak

Reputation: 472

Laravel raw query to User Models

I have a raw SQL-query that i want to produce Laravel Eloquent models from.

Query:

SELECT * FROM user
LEFT JOIN (SELECT user_id, MAX(log_login_id) AS maxid
FROM log_login
GROUP BY user_id) 
AS T1 ON user.user_id = T1.user_id
LEFT JOIN log_login AS T2 ON T2.log_login_id = T1.maxid
WHERE user.map_id = 101;

Im trying something like this, but i doesnt quite work..

 $users = User::where('user.map_id', session('mapID'))
        ->leftJoin('(SELECT user_id, MAX(log_login_id) AS maxid
                   FROM log_login
                   GROUP BY user_id) 
                   AS T1 ON user.user_id = T1.user_id')
        ->leftJoin('log_login AS T2 ON T2.log_login_id = T1.maxid')
        ->orderBy('username', 'asc')->get();

How to i setup my query, so that i get a list of object models from Eloquent? This is a syntax issue, i guess.

Error: 

QueryException in Connection.php line 624:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`` left join `log_login` as `T2` on `` `` where `user`.`map_id` = ? order by `u' at line 1 (SQL: select * from `user` left join `(SELECT` as `MAX(log_login_id)` on `` `` left join `log_login` as `T2` on `` `` where `user`.`map_id` = 101 order by `username` asc)

in Connection.php line 624
at Connection->runQueryCallback('select * from `user` left join `(SELECT` as `MAX(log_login_id)` on `` `` left join `log_login` as `T2` on `` `` where `user`.`map_id` = ? order by `username` asc', array('101'), object(Closure)) in Connection.php line 580
at Connection->run('select * from `user` left join `(SELECT` as `MAX(log_login_id)` on `` `` left join `log_login` as `T2` on `` `` where `user`.`map_id` = ? order by `username` asc', array('101'), object(Closure)) in Connection.php line 297
at Connection->select('select * from `user` left join `(SELECT` as `MAX(log_login_id)` on `` `` left join `log_login` as `T2` on `` `` where `user`.`map_id` = ? order by `username` asc', array('101'), true) in Builder.php line 1377
at Builder->runSelect() in Builder.php line 1367
at Builder->getFresh(array('*')) in Builder.php line 1354
at Builder->get(array('*')) in Builder.php line 380

After alot of try and error, this did the trick:

$users = User::where('user.map_id', session('mapID'))
    ->leftJoin(DB::raw('(SELECT user_id,  MAX(log_login_id) AS maxid
       FROM log_login
       GROUP BY user_id) 
       AS T1'), function ($join) {
        $join->on("user.user_id", "=", "T1.user_id");
    })
    ->leftJoin(DB::raw('log_login AS T2'), function ($join) {
        $join->on('T2.log_login_id', '=', 'T1.maxid');
    })->get();

Upvotes: 0

Views: 302

Answers (1)

oseintow
oseintow

Reputation: 7391

try this

$users = User::where('user.map_id', session('mapID'))
    ->leftJoin(\DB::raw('(SELECT user_id, MAX(log_login_id) AS maxid
               FROM log_login
               GROUP BY user_id) 
               AS T1 ON user.user_id = T1.user_id'))
    ->leftJoin('log_login AS T2 ON T2.log_login_id = T1.maxid')
    ->orderBy('username', 'asc')->get();

or this (not tested but guess it should work)

$users = User::where('user.map_id', session('mapID'))
    ->leftJoin(function($query){
       $query->select("user_id", "MAX(log_login_id) as maxid")
           ->from('log_login as T1')
           ->groupBy('user_id')
           ->on('user.user_id','=','T1.user_id')
    })
    ->leftJoin('log_login AS T2 ON T2.log_login_id = T1.maxid')
    ->orderBy('username', 'asc')->get();

hope it helps

Upvotes: 2

Related Questions