What makes "group by 1" and why it causes errors in Laravel

Asked

Viewed 583 times

4

I wonder what the group by 1 does in the MySQL ?

I’m curious because I have a very complex query on MySQL and it works great, only I’m trying to use it in Laravel and I get an error in the return. If I run the same query in an editor MySQL no mistakes.

Follow the query:

select posicao_id, distancia, max(created_at) created_at
from dispositivos
group by 1

Follow the error:

"errorInfo": [
    "42000",
    1055,
    "'laravel.dispositivos.distancia' isn't in GROUP BY"
]

Remarks: if I disable the option strict => false in the archive config/database.php it works again but what the consequences of that ?

mysql laravel

2 answers

6


GROUP BY

The club GROUP BY accepts two types of information: text and whole. When an integer value is informed, you will be informing - implicitly - the position of the column that will be used in the above clause.

Example:

SELECT `id`, `name`, `gender` FROM `users` GROUP BY 3

Amounts to the code:

SELECT `id`, `name`, `gender` FROM `users` GROUP BY `gender`

Erro no Laravel

By default, all information passed in the methods: table, select, groupBy, orderBy etc. They are placed between severe accents. This will ensure that when using column names such as date and order (which are native functions of the SQL), the code does not fail, as names between severe accents will not be interpreted by Mysql, for example.

Example in Laravel:

$test = \DB::table('users')->select('id')->groupBy('1');    
dd($test->toSQL());

"select `id` from `users` group by `1`"

Note that in the above output, the method groupBy automatically puts the numeric value between low accents. Causing the Mysql interpret it as a column and not her position.

To fix this fault, you can use the method DB::raw, for example:

$test = \DB::table('users')->select('id')->groupBy(\DB::raw('1'));    
dd($test->toSQL());

"select id from users group by 1"

With the DB::raw, you will inform Laravel that it is not to change or escape that code.

Strict Mode

According to the documentation, the Strict mode controls how Mysql handles invalid or missing values in data change instructions.

Although it does not generate an error, a warning is generated when using the select with the strict mode set as false.

With the strict mode active, the Mysql will generate errors when trying to use invalid values (as in the example of group by). With the above idle mode, the Database Manager System (SGDB) will try to adjust the value so that such errors are not displayed, however the result of the query may be affected.

  • Sincerity, this is not a flaw of Eloquent (Laravel é o framework Web) and Eloquent which is used within the Laravel and can also be used outside, ie it is independent of the Laravel even if it’s done by the same guy. 'Cause that’s how it is? because it is not all banks that work in the same way and the standard placed by it is used in all banks. It is worth remembering that there is in all its methods the way to circumvent SQL Raw and that you explained. Don’t get me wrong, but I need to challenge your answer on this topic because Error is what doesn’t work.

3

Serves to group in the first column, regardless of what is called. You can do the same with ORDER BY.

Example: 

SELECT account_id, open_emp_id
          /\          /\
       coluna 1      coluna 2

FROM account
GROUP BY 1;

In the above consultation, GROUP BY 1 refers to the first column in select statement which is account_id.

Original response from Stackoverflow english here.

Browser other questions tagged mysql laravel

You are not signed in. Login or sign up in order to post.