laravel query not working properly while working on sql database

Question

i run this query on phpmyadmin database table working fine:

select substring_index( prefix, '.', 3 ) as subip , count(prefix) as count, prefix 
from prefixes 
GROUP BY INET_ATON(subip)/256 
order by cidr desc

when i run raw query in laravel:

$selected_prefixes = DB::select("select substring_index( prefix, '.', 3 ) as subip , count(prefix) as count, prefix 
from prefixes 
GROUP BY INET_ATON(subip)/256 
order by cidr desc ");

giving this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'portal.prefixes.prefix' isn't in GROUP BY (SQL: select substring_index( prefix, '.', 3 ) as subip , count(prefix) as count, prefix from prefixes GROUP BY INET_ATON(subip)/256 order by cidr desc )

here example table file: https://www.mediafire.com/file/vwnply7ggtai52m/prefixes(5).sql/file

persian-theme · Accepted Answer · 2022-01-08 16:19:07Z

1

You can not retrieve the prefix column because some similar subip has different prefixes. I put the examples in the link below. You can check it out.

The following code works correctly.

select substring_index(prefix, '.', 3) as subip, count(*) as count 
from  prefixes
group by substring_index(prefix, '.', 3)

Demo in db<>fiddle

edited Jan 8, 2022 at 16:19

answered Jan 8, 2022 at 16:12

persian-theme

6,6483 gold badges9 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

thephper · Accepted Answer · 2022-01-08 15:35:34Z

0

Doing select on columns that is not part of group by is in most SQL implementations not valid as the actual row that should be represented for that column could be any of the grouped rows.

I suspect you use MySQL which allows this with certain settings, in which case it will output a random value from the group (not necessarily the first). When running through Laravel there must be other settings (more strict settings) in the database.php configuration.

You could loosen the strict settings in the database config, or you could rewrite the query, so that you dont select a column that is not part of the GROUP BY.

answered Jan 8, 2022 at 15:35

thephper

2,70028 silver badges23 bronze badges

2 Comments

fahdshaykh Over a year ago

thanks for detail. but after enable strict mode can cause issue on live server.

thephper Over a year ago

You should change your query then. The problem is that the "prefix" column can contain different values for the returned row, as it is not part of the group by. You should either add the column to the GROUP BY or disable strict mode in laravel. Alternatively temporary disable the "ONLY_FULL_GROUP_BY" sql mode as suggested here: stackoverflow.com/questions/48145384/…

Collectives™ on Stack Overflow

laravel query not working properly while working on sql database

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related