General error: 20018 Column "COLUMN" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[eliacimdavila]

Hi, I am using the extension and is working whithout problem on mysql. But, when I change to SQL Server connection, it throws me this error:

Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 20018 Column 'product.presentation_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. [20018] (severity 16) [(null)]

My code is something like this:

$productsList   = Product::search($seachQuery)
                                           ->select(
                                             'product.*',
                                             'store.price',
                                             'store.bar_code',
                                             'store.pos_department',
                                             'pres.name as presentation_name',
                                             'dep.name as department_name',
                                             'unit.name as sale_unit_name',
                                             'bra.name as brand_name'
                                           )
                                           ->join('store_product as store', 'store.product_id', 'product.id')
                                           ->leftJoin('presentation as pres', 'pres.id', 'product.presentation_id')
                                           ->leftJoin('department as dep', 'dep.id', 'product.department_id')
                                           ->leftJoin('sale_unit as unit', 'unit.id', 'product.sale_unit_id')
                                           ->leftJoin('brand as bra', 'bra.id', 'product.brand_id')
                                           ->where('store.store_id', $storeModel->id)
                                           ->where('store.status', Product::PRODUCT_ENABLE);

The query that is generated is:

select [product]., [store].[price], [store].[bar_code], [store].[pos_department], [pres].[name] as [presentation_name], [dep].[name] as [department_name], [unit].[name] as [sale_unit_name], [bra].[name] as [brand_name] from (select [product]., max(case when [product].[name] = bafar then 15 else 0 end + case when [product].[name] like bafar% then 5 else 0 end + case when [product].[name] like %bafar% then 1 else 0 end + case when [product].[sku] = bafar then 15 else 0 end + case when [product].[sku] like bafar% then 5 else 0 end + case when [product].[sku] like %bafar% then 1 else 0 end) as relevance from [product] where ([product].[name] like %bafar% or [product].[sku] like %bafar%) group by [product].[id]) as [product] inner join [store_product] as [store] on [store].[product_id] = [product].[id] left join [presentation] as [pres] on [pres].[id] = [product].[presentation_id] left join [department] as [dep] on [dep].[id] = [product].[department_id] left join [sale_unit] as [unit] on [unit].[id] = [product].[sale_unit_id] left join [brand] as [bra] on [bra].[id] = [product].[brand_id] where [store].[store_id] = 5 and [store].[status] = 1 and [relevance] >= 0.50 order by [relevance] desc

I think the problem is this part:

select [product].*, max(case when [product].[name] = bafar then 15 else 0 end + case when [product].[name] like bafar% then 5 else 0 end + case when [product].[name] like %bafar% then 1 else 0 end + case when [product].[sku] = bafar then 15 else 0 end + case when [product].[sku] like bafar% then 5 else 0 end + case when [product].[sku] like %bafar% then 1 else 0 end) as relevance from [product] where ([product].[name] like %bafar% or [product].[sku] like %bafar%) group by [product].[id]

Because is the only part with GROUP BY statement.

Is something I can do to fix this?

Thanks.

[incraigulous]

The issue is that MySQL has ONLY_FULL_GROUP_BY on my default in newer versions. Not sure what to suggest to fix this other than to rewrite the query.

[incraigulous]

I added this to get past it, but this probably needs to be fixed the right way. I'm not that strong in the ways of SQL, or I would make a PR:

DB::statement("SET SESSION sql_mode = ''");

[vaishnavmhetre]

I added this to get past it, but this probably needs to be fixed the right way. I'm not that strong in the ways of SQL, or I would make a PR:

DB::statement("SET SESSION sql_mode = ''");

Hello @incraigulous ,
This totally works by default for the latest systems. Create some tests for the older systems and or Database Switching (aka Mysql, Mongo, etc). Create a config for that utility statement to be executed.

Please have your worth credit and shoot a PR to make necessary changes and configurations.

Thanks for the workaround.

[Kenny417]

It should be worth noting that ONLY_FULL_GROUP_BY makes MySQL better comply with SQL standards. It's probably better to re-write your query.

sql_mode = '' actually clears all the SQL modes currently enabled. That may not necessarily be what you want. See this Stack Overflow question for more options.

I added my fields to an array and referenced that array in my select and groupBy which seems to have fixed the problem for me.

[sahamilton]

In Laravel I have set the db config strict value to false.