SQLSTATE[42000]: Syntax error or access violation: 1055 'website.stories.user_id' isn't in GROUP BY

[therezor]

Looks like queries are not compatible with SQL strict mode.

Simple code will cause an issue: SQLSTATE[42000]: Syntax error or access violation: 1055 'website.stories.user_id' isn't in GROUP BY

 Story::published()->withAllTags($this->tag)->count()

[therezor]

Query that was produced by withAllTags:

SELECT
  count(*) AS aggregate
FROM
  (
    SELECT
      `stories`.*
    FROM
      `stories`
      INNER JOIN `taggables` AS `taggables_scopewithalltags_1` ON `stories`.`id` = `taggables_scopewithalltags_1`.`taggable_id`
      AND `taggables_scopewithalltags_1`.`taggable_type` = App \ Models \ Story
    WHERE
      `published_at` IS NOT NULL
      AND `published_at` <= 2024 -02 -06 11: 34: 35
      AND `taggables_scopewithalltags_1`.`tag_id` IN (1)
      AND `stories`.`deleted_at` IS NULL
    GROUP BY
      `stories`.`id`
    HAVING
      COUNT(DISTINCT taggables_scopewithalltags_1.tag_id) = 1
  ) AS `temp_table`

[cviebrock]

Yeah, this is a bit of a known issue. The fix (as far as I understand it to be), would be to change the code so that the generated SQL is changed from:

SELECT `stories`.*

to:

SELECT `stories`.`column1`, `stories`.`column2`, `stories`.`column3`,  ...

i.e. it needs to iterate and list all the columns from that table. That's pretty awkward, IMO.

If you can think of a better solution, I'm totally open to implementing it!

[therezor]

@cviebrock what do you thin about using subquery instead of join (whereHas)?

[Archanaub04]

$count = Story::published()
    ->withAllTags($this->tag)
    ->select(DB::raw('COUNT(*) as total_count'))
    ->first()
    ->total_count;
    ```
    

[cviebrock]

@Archanaub04 do you want to put together a PR for this?

[rachelies]

Encountered this issue too

[lorvent]

For me this is a strange problem, in windows system with laragon, php 8.3 it is working fine
same code on server with linux, php 8.3 throwing error

select count(*) as aggregate from (select `blogs`.* from `blogs` inner join `taggable_taggables` as `taggable_taggables_scopewithanytags_1` on `blogs`.`id` = `taggable_taggables_scopewithanytags_1`.`taggable_id` and `taggable_taggables_scopewithanytags_1`.`taggable_type` = App\Models\Blog where `taggable_taggables_scopewithanytags_1`.`tag_id` in (3) and `blogs`.`deleted_at` is null group by `blogs`.`id`) as `aggregate_table`

and code causing above error is

$blogs = Blog::withAnyTags($tag)->paginate(5);

[therezor]

@lorvent it depends on MYSQL settings. If strict mode is off, it would work fine

[lorvent]

@therezor unfortunately not working for me.

i have setting like sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES and i removed STRICT_TRANS_TABLES
so final one is like sql-mode=NO_ENGINE_SUBSTITUTION

but still it is throwing same error

using mariadb 10.11.10-MariaDB-

[lorvent]

I am able to solve this by moving from mariadb 10.xx to mysql 8.x, works even having STRICT_TRANS_TABLES