2

I'm currently using groupBy to group concatenated (book name and book_author). I'm intentionally using groupBy because I have other columns to get their average, and sum.

I initially used this code below. But it returning me an error.

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated

Reference::select('*')
    ->selectRaw("CONCAT(book_name, ' ', book_author) AS book")
    ->groupBy('type');

If I add an id column inside groupBy, it does not returning an error. But it does not order, I still getting duplicate book.

Reference::select('*')
    ->selectRaw("CONCAT(book_name, ' ', book_author) AS book")
    ->groupBy(['id', 'type']);

Someone, how to achieve this properly?

schutte
  • 1,949
  • 7
  • 25
  • 45
  • Some relational databases require that when using "group by" each selected column must be either part of "group by" or aggregated using some aggregation function (like max, min, sum etc.). Think about what should storage engine do with column 'id' containing unique values, when you want to group by type? It cannot simply merge these values in one row. – Ilia Yatsenko Jul 13 '21 at 09:37

2 Answers2

1

TLDR; When you use group by in strict mode, your selected fields must be aggregated fields.

Let's say you have two books with the same type.

book_name book_name type
First author 1
Second editor 1

When you group by type, those having the same type will be merged into one. So wich result will it be for CONCAT(book_name, ' ', book_author) ?

  • First author ?
  • Second editor ?
  • First editor ?
  • Second author?

The query doesnt gamble when strict mode is active, you need to aggregate the fields, for example LISTAGG(book_name , ',') is an aggregationg of the field book_name and result in 'first,second'.

If you disable the strict mode ('strict' => false,) in the file config/database.php, the result might be any of the 4 listed above as it is kinda random.

Other example for aggregation functions: SUM(), AVG(), COUNT()...

N69S
  • 16,110
  • 3
  • 22
  • 36
  • would there any future serious problem if I'll set it to false? – schutte Jul 13 '21 at 10:08
  • @gecko Of course, using random data can lead to issues. plus you will never be able to return back to strict mode the more you use it? – N69S Jul 13 '21 at 10:16
0

Please use the below link, as the solution is already available.

Solution Link.

Please check and you will find the solution.

Vishal
  • 61
  • 7
  • i'm using eloquent sir. – schutte Jul 13 '21 at 09:39
  • The problem is not related to eloquent. It lays on the level of SQL storage – Ilia Yatsenko Jul 13 '21 at 09:41
  • Try to replace `select *` with list of columns you really need. – Ilia Yatsenko Jul 13 '21 at 09:42
  • I tried executing similar Query, I got the below Exception `Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myshop.front_users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `id`, `first_name`, `last_name`, `city_id`, CONCAT(first_name, ' ', last_name) AS name from `front_users` group by `city_id`)'` Hence I suggest you try solution, it will work – Vishal Jul 13 '21 at 09:52