Laravel query builder, having issue

Question

i have an issue, and don't know how fix it. I need to get an specific object from select such as { "iso":"UA", "stories":122, "title":Ukraine }

so i have an sql query

SELECT
c.iso,
locale.title as title,
(SELECT COUNT(DISTINCT s.id) FROM stories AS s WHERE s.country_id = c.id) AS stories
FROM `countries` AS c
LEFT JOIN countries_locale AS c_l ON c.id=c_l.country_id
LEFT JOIN locales AS locale ON c_l.locale_id=locale.id
WHERE locale.locale = 'en'
GROUP BY c.id
HAVING stories>0

and it works fine, so i try to rewrite this query to Laravel QB:

DB::table($this->getTable())
->select(
'countries.iso as iso',
'locales.title as title',
DB::raw('(SELECT COUNT(s.id) FROM stories AS s WHERE  s.country_id = countries.id) AS stories')
)
->leftJoin('countries_locale', 'countries.id', '=', 'countries_locale.country_id')
->leftJoin('locales', 'countries_locale.locale_id', '=', 'locales.id')
->where('locales.locale', \App::getLocale())
->groupBy('iso')
->having('stories', '>', 0)
->get();

And then i get an error

Syntax error or access violation: 1055 'way.countries.id' isn't in GROUP BY

and in shows me an sql string, that i can successfully exec in mysql

post it as answer, i will mark it! you save my night) thanks — mikrafizik, Mar 21 '17 at 19:21

score 1 · Accepted Answer · answered Mar 21 '17 at 19:22

1

If you want laravel to accept not very strict queries,use

'strict' => false

in the database configuration. Or,in your case you could put both those column in the select in the group by.

answered Mar 21 '17 at 19:22

Mihai

26,325
7
66
81

but i it wants all columns from table countries, why i need to put all of this columns, if only i want remove duplicates? Strict mode works, thanks! – mikrafizik Mar 21 '17 at 19:28

Laravel query builder, having issue

1 Answers1