Laravel 5.4 query builder having issues with groupBy

Question

I'm trying to convert 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 this error barf: Argument 1 passed to Illuminate\Database\Connection::prepareBindings() must be of the type array, string given, called in /var/www/html/source/luniverse/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 648 and defined This is my attempt at it (one of many):

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

The database config is set to strict mode, but that doesn't exactly seem to explain that particular error. The raw query runs fine in a DB gui.

The sql or the query builder? The sql query runs fine and gets exactly what I want. — Matt Larsuma, Apr 03 '18 at 17:39
output the built laravel select query, and compare it to your original, to try and find where the error is. https://stackoverflow.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string or just add this immediately before your query gets run: DB::listen(function ($query) { var_dump($query->sql); var_dump($query->bindings); }); — Andrew, Apr 03 '18 at 17:43

score 1 · Accepted Answer · answered Apr 04 '18 at 07:36

Change the groupby code like

$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','oat.user_id')
            ->get();

Thanks! That did the trick, though I had to match the select exactly, so more like: `->groupBy('oc.id','oc.name','oat.user_id','p.first_name','p.last_name')` — Matt Larsuma, Apr 04 '18 at 12:51

score 0 · Answer 2 · answered Apr 03 '18 at 23:45

0

DB::select() executes a query, you have to use DB::table():

DB::table('oauth_clients as oc')
    ->select('oc.id','oc.name','oat.user_id','p.first_name','p.last_name')

answered Apr 03 '18 at 23:45

Jonas Staudenmeir

24,815
6
63
109

Laravel 5.4 query builder having issues with groupBy

2 Answers2