Query Builder and groupBy having issues

I'm trying to translate this query to query builder in Laravel 5.4:

SELECT
    oc.id,
    oc.name,
    oat.user_id,
    p.first_name,
    p.last_name
FROM
    oauth_clients oc
    LEFT JOIN oauth_access_tokens oat ON oc.id = oat.client_id
    JOIN users u on u.id = oat.user_id
    JOIN people p on p.id = u.person_id
WHERE oc.revoked = false AND oc.password_client = true
GROUP BY oc.id, oat.user_id

and getting the above error barf. This is my attempt at it :

$tokens = DB::select('oc.id','oc.name','oat.user_id','p.first_name','p.last_name')
                    ->from('oauth_clients as oc')
                    ->leftJoin('oauth_access_tokens as oat', 'oc.id', '=', 'oat.client_id')
                    ->join('users as u', 'u.id', '=', 'oat.user_id')
                    ->join('people as p', 'p.id', '=', 'u.person_id')
                    ->where('oc.revoked', '=', 'false')
                    ->where('oc.password_client', '=', 'true')
                    ->groupBy('oc.id')
                    ->groupBy('oat.user_id')
                    ->get();

Thyrosis

8 years ago

Level 17

I don't see an error in your post. But, seeing as you name it a GroupBy issue it might be due to Laravel 5.6 and MySQL 5.7 combination issue.

In config/database.php, try changing

'strict' => true,

'strict' => false,

jlrdw

8 years ago

Level 75

Or if that query works well in regular pdo, use as is with getPdo(). But the strict to false will probably work.

An older example just showing usage

public function getChecks($offset = "", $rowsperpage = "", $checksearch = "")
    {
        $checksearch = $checksearch . "%";
        $pagingQuery = " LIMIT $offset, $rowsperpage";
        $sql = "SELECT OD.checkid, OD.transdate, OD.transdescribe, OD.widthdraw, OD.deposit, OD.isclr,";
        $sql = $sql . " (SELECT (Sum(IFNULL(deposit, 0)) - Sum(IFNULL(widthdraw, 0))) FROM checks";
        $sql = $sql . " WHERE checkid<=OD.checkid) AS RunningSum";
        $sql = $sql . " FROM checks AS OD" . $pagingQuery;
        $sth = \Illuminate\Support\Facades\DB::connection()->getPdo()->prepare($sql);
        $sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);
        return $results;
    }

Just my 2 cents and showing use of getPdo which some folks don't know about.

Please or to participate in this conversation.