Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

user154's avatar

Query not working in laravel query builder

i created a query in query builder app and returning result like i want

SELECT
	answers.name,
	COUNT(CASE WHEN survey.village_id = 3272010006 THEN 1 ELSE NULL END ) AS result,
FROM
	results
	LEFT JOIN survey ON results.survey_number = survey.survey_number
	LEFT JOIN answers ON results.answer_id = answers.id
WHERE
	results.question_id = 1 AND
	results.answer_id IS NOT NULL AND
	YEAR(survey.survey_date) = 2021
GROUP BY
	results.answer_id

but when i convert into laravel query builder it return error violation

$query = DB::table('results')
 ->select(
      'answers.name as answer_name',
      DB::raw('COUNT(CASE WHEN survey.village_id = 3272010006 THEN 1 ELSE NULL END ) AS result')
 )->leftJoin('survey', 'results.survey_number', '=', 'survey.survey_number')
 ->leftJoin('answers', 'results.answer_id', '=', 'answers.id')
 ->where('results.question_id', $question)
 ->where('results.answer_id', '!=', null)
 ->whereYear('survey.survey_date', 2021)
 ->groupBy('results.answer_id')
 ->get();

return $query;

the error

Syntax error or access violation: 1055 'answers.name' isn't in GROUP BY

it work when i delete the answers.name

0 likes
3 replies
jlrdw's avatar

Lookup GROUP BY in the Mysql manual, it explains all of this.

psrz's avatar
psrz
Best Answer
Level 10

Check "select @@sql_mode" (using laravel) If it shows, amgonst other things, ONLY_FULL_GROUP_BY, then that's your problem

PDO will connect to mysql in a more "strict" manner than whatever client you're using to run the query directly

https://stackoverflow.com/questions/6975351/how-can-i-set-the-sql-mode-while-using-pdo

You might get rid of that restriction, but you should realy try to write the query with query builder in a way you don't need "hack" it

Please or to participate in this conversation.