Question 63882 in PhpMyAdmin Question posted in
The official documentation can be found here.

laravel query not working properly while working on sql database – Phpmyadmin

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

Tags:

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search