Laravel Query builder - query not working but works in SQL console

Question

I've become extremely confused I'm trying to run a query through laravels query builder which should work but it's throwing odd errors.

I'm trying to count records from the tips table where the status is 'Won' between a daterange and then join the users table on the user_id to retrieve the users username.

This is my query builder

    $topfiveTipsters = DB::table('tips')
      ->select(DB::raw('count(status) as wincount, users.name'))
      ->join('users', 'users.id', '=', 'tips.user_id')
      ->whereBetween('tips.created_at',[$start,$end])
      ->where('status','Won')
      ->groupBy('users.id')
      ->orderBy('wincount', 'desc')
      ->get();

However it's throwing the error

QLSTATE[42000]: Syntax error or access violation: 1055 'digthetip.users.name' isn't in GROUP BY (SQL: select count(status) as wincount, users.name from tips inner join users on users.id = tips.user_id where tips.created_at between 2016-11-01 00:00:00 and 2016-11-02 10:39:02 and status = Won group by users.id order by wincount desc)

but if I run the exact query the error has outputted into the SQL console within PHPMyAdmin the query runs fine and returns the results I require.

Am I missing something? I'm relatively new to Laravel and I'm massively confused how it's still throwing an error.

Can you post both SQL query's that you are comparing please? — UP.
– UP., Commented Nov 2, 2016 at 10:49
You can use addSelect laravel.com/docs/5.3/queries#selects after join — Vahe Galstyan
– Vahe Galstyan, Commented Nov 2, 2016 at 10:50
The query I'm attempting to run is this select count(status) as wincount, users.name from tips inner join users on users.id = tips.user_id where tips.created_at between '2016-11-01 00:00:00' and '2016-11-02 10:39:02' and status = 'Won' group by users.id order by wincount desc — Paradigm
– Paradigm, Commented Nov 2, 2016 at 10:50

Paradigm · Accepted Answer · 2016-11-02 11:01:57Z

4

Ok so it turns out you can turn of SQL strict mode in /app/database.php which will prevent the error from happening.

'strict' => false,

answered Nov 2, 2016 at 11:01

Paradigm

3712 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

UP. Over a year ago

It seems to me that is a bit of an overkill, when (by reading the docs dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict, seems like a helpful thing to have activated). As a last try, more as a curiosity now, what happens if you add 'IGNORE' on the raw sql DB::raw('IGNORE count(status) as wincount ?

Paradigm Over a year ago

This just throws the below error IGNORE count(status) as wincount, users.name, users.id from tips inner join u' at line 1 (SQL: select IGNORE count(status) as wincount, users.name, users.id from tips` inner join users on users.id = tips.user_id where tips.created_at between 2016-11-01 00:00:00 and 2016-11-02 11:12:53 and status = Won group by users.id order by wincount desc

UP. Over a year ago

Thanks. I'll try to remember this for future wierd problems with my selects. Glad you solved it.

Paradigm Over a year ago

Yeah it's definitely inconvenient but I cant see any other way around it.

jlos Over a year ago

damn, if it wasn't for this thread i could've been searching for that one for a long time, thanks!

Collectives™ on Stack Overflow

Laravel Query builder - query not working but works in SQL console

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related