Eloquent groupBy make “SQLSTATE[42000]” with valid SQL query in Laravel 5.3

Multi tool use
I have a strange problem with Eloquent which I'm trying to do the following:
$this->node = DB::table('permission')
->select('permission.id',
'object.name as object_name',
'permission.created_at',
'object.id as object_id')
->join('object', 'object.id', '=', 'permission.object_id')
->join('action', 'action.id', '=', 'permission.action_id')
->where('permission.person_id', $this->person['id'])
->groupBy('permission.object_id')
->orderBy('permission.created_at', 'desc')
->paginate(5);
Laravel Framework report an Error:
QueryException in Connection.php line 761: SQLSTATE[42000]: Syntax
error or access violation: 1055 'permission.id' isn't in GROUP
BY (SQL: selectpermission
.id
,object
.name
asobject_name
,
permission
.created_at
,object
.id
asobject_id
from
permission
inner joinobject
onobject
.id
=
permission
.object_id
inner joinaction
onaction
.id
=
permission
.action_id
wherepermission
.person_id
= 1 group by
permission
.object_id
order bypermission
.created_at
desc limit
5 offset 0)
I've added an Eloquent debugging function DB::listen in AppServiceProvider:
use IlluminateSupportFacadesDB;
use IlluminateSupportServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
//
DB::listen(function ($query) {
echo "<pre>";
print_r($query->sql);
echo "</pre>";
// $query->sql
// $query->bindings
// $query->time
});
}
...
And it does print this SQL query:
select `permission`.`id`,
`object`.`name` as `object_name`,
`permission`.`created_at`,
`object`.`id` as `object_id`
from `permission`
inner join `object` on `object`.`id` = `permission`.`object_id`
inner join `action` on `action`.`id` = `permission`.`action_id`
where `permission`.`person_id` = 1
group by `permission`.`object_id`
order by `permission`.`created_at` desc
limit 5 offset 0
Which is valid in MySQL through PhpMyAdmin and here is the output for the query:
Even So, I tested in mysql
command directly and it does work just fine, look at mysql output:
Any idea?
Thanks
php mysql laravel laravel-5 eloquent
|
show 3 more comments
I have a strange problem with Eloquent which I'm trying to do the following:
$this->node = DB::table('permission')
->select('permission.id',
'object.name as object_name',
'permission.created_at',
'object.id as object_id')
->join('object', 'object.id', '=', 'permission.object_id')
->join('action', 'action.id', '=', 'permission.action_id')
->where('permission.person_id', $this->person['id'])
->groupBy('permission.object_id')
->orderBy('permission.created_at', 'desc')
->paginate(5);
Laravel Framework report an Error:
QueryException in Connection.php line 761: SQLSTATE[42000]: Syntax
error or access violation: 1055 'permission.id' isn't in GROUP
BY (SQL: selectpermission
.id
,object
.name
asobject_name
,
permission
.created_at
,object
.id
asobject_id
from
permission
inner joinobject
onobject
.id
=
permission
.object_id
inner joinaction
onaction
.id
=
permission
.action_id
wherepermission
.person_id
= 1 group by
permission
.object_id
order bypermission
.created_at
desc limit
5 offset 0)
I've added an Eloquent debugging function DB::listen in AppServiceProvider:
use IlluminateSupportFacadesDB;
use IlluminateSupportServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
//
DB::listen(function ($query) {
echo "<pre>";
print_r($query->sql);
echo "</pre>";
// $query->sql
// $query->bindings
// $query->time
});
}
...
And it does print this SQL query:
select `permission`.`id`,
`object`.`name` as `object_name`,
`permission`.`created_at`,
`object`.`id` as `object_id`
from `permission`
inner join `object` on `object`.`id` = `permission`.`object_id`
inner join `action` on `action`.`id` = `permission`.`action_id`
where `permission`.`person_id` = 1
group by `permission`.`object_id`
order by `permission`.`created_at` desc
limit 5 offset 0
Which is valid in MySQL through PhpMyAdmin and here is the output for the query:
Even So, I tested in mysql
command directly and it does work just fine, look at mysql output:
Any idea?
Thanks
php mysql laravel laravel-5 eloquent
Does it do a count and then execute the real query? The quoted exception has a query in it that isn't a count.
– jedifans
Aug 25 '16 at 6:27
3
Laravel 5.3 features a'strict' => true
inconfig/database.php
which turns on ONLY_FULL_GROUP_BY
– mazedlx
Aug 25 '16 at 6:38
No, it just stopped at counting. But when I removed groupBy it does count and then made the query!
– Abdelaziz Elrashed
Aug 25 '16 at 6:39
@mazedlx I turnstrict option
from false into true and I made a clean cache for everything by artisan command, but still the same problem appears.
– Abdelaziz Elrashed
Aug 25 '16 at 7:00
1
No, @mazedlx did not suggest to turn this setting on.
– Shadow
Aug 25 '16 at 9:38
|
show 3 more comments
I have a strange problem with Eloquent which I'm trying to do the following:
$this->node = DB::table('permission')
->select('permission.id',
'object.name as object_name',
'permission.created_at',
'object.id as object_id')
->join('object', 'object.id', '=', 'permission.object_id')
->join('action', 'action.id', '=', 'permission.action_id')
->where('permission.person_id', $this->person['id'])
->groupBy('permission.object_id')
->orderBy('permission.created_at', 'desc')
->paginate(5);
Laravel Framework report an Error:
QueryException in Connection.php line 761: SQLSTATE[42000]: Syntax
error or access violation: 1055 'permission.id' isn't in GROUP
BY (SQL: selectpermission
.id
,object
.name
asobject_name
,
permission
.created_at
,object
.id
asobject_id
from
permission
inner joinobject
onobject
.id
=
permission
.object_id
inner joinaction
onaction
.id
=
permission
.action_id
wherepermission
.person_id
= 1 group by
permission
.object_id
order bypermission
.created_at
desc limit
5 offset 0)
I've added an Eloquent debugging function DB::listen in AppServiceProvider:
use IlluminateSupportFacadesDB;
use IlluminateSupportServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
//
DB::listen(function ($query) {
echo "<pre>";
print_r($query->sql);
echo "</pre>";
// $query->sql
// $query->bindings
// $query->time
});
}
...
And it does print this SQL query:
select `permission`.`id`,
`object`.`name` as `object_name`,
`permission`.`created_at`,
`object`.`id` as `object_id`
from `permission`
inner join `object` on `object`.`id` = `permission`.`object_id`
inner join `action` on `action`.`id` = `permission`.`action_id`
where `permission`.`person_id` = 1
group by `permission`.`object_id`
order by `permission`.`created_at` desc
limit 5 offset 0
Which is valid in MySQL through PhpMyAdmin and here is the output for the query:
Even So, I tested in mysql
command directly and it does work just fine, look at mysql output:
Any idea?
Thanks
php mysql laravel laravel-5 eloquent
I have a strange problem with Eloquent which I'm trying to do the following:
$this->node = DB::table('permission')
->select('permission.id',
'object.name as object_name',
'permission.created_at',
'object.id as object_id')
->join('object', 'object.id', '=', 'permission.object_id')
->join('action', 'action.id', '=', 'permission.action_id')
->where('permission.person_id', $this->person['id'])
->groupBy('permission.object_id')
->orderBy('permission.created_at', 'desc')
->paginate(5);
Laravel Framework report an Error:
QueryException in Connection.php line 761: SQLSTATE[42000]: Syntax
error or access violation: 1055 'permission.id' isn't in GROUP
BY (SQL: selectpermission
.id
,object
.name
asobject_name
,
permission
.created_at
,object
.id
asobject_id
from
permission
inner joinobject
onobject
.id
=
permission
.object_id
inner joinaction
onaction
.id
=
permission
.action_id
wherepermission
.person_id
= 1 group by
permission
.object_id
order bypermission
.created_at
desc limit
5 offset 0)
I've added an Eloquent debugging function DB::listen in AppServiceProvider:
use IlluminateSupportFacadesDB;
use IlluminateSupportServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
//
DB::listen(function ($query) {
echo "<pre>";
print_r($query->sql);
echo "</pre>";
// $query->sql
// $query->bindings
// $query->time
});
}
...
And it does print this SQL query:
select `permission`.`id`,
`object`.`name` as `object_name`,
`permission`.`created_at`,
`object`.`id` as `object_id`
from `permission`
inner join `object` on `object`.`id` = `permission`.`object_id`
inner join `action` on `action`.`id` = `permission`.`action_id`
where `permission`.`person_id` = 1
group by `permission`.`object_id`
order by `permission`.`created_at` desc
limit 5 offset 0
Which is valid in MySQL through PhpMyAdmin and here is the output for the query:
Even So, I tested in mysql
command directly and it does work just fine, look at mysql output:
Any idea?
Thanks
php mysql laravel laravel-5 eloquent
php mysql laravel laravel-5 eloquent
edited Aug 25 '16 at 13:48
Abdelaziz Elrashed
asked Aug 25 '16 at 6:25


Abdelaziz ElrashedAbdelaziz Elrashed
618
618
Does it do a count and then execute the real query? The quoted exception has a query in it that isn't a count.
– jedifans
Aug 25 '16 at 6:27
3
Laravel 5.3 features a'strict' => true
inconfig/database.php
which turns on ONLY_FULL_GROUP_BY
– mazedlx
Aug 25 '16 at 6:38
No, it just stopped at counting. But when I removed groupBy it does count and then made the query!
– Abdelaziz Elrashed
Aug 25 '16 at 6:39
@mazedlx I turnstrict option
from false into true and I made a clean cache for everything by artisan command, but still the same problem appears.
– Abdelaziz Elrashed
Aug 25 '16 at 7:00
1
No, @mazedlx did not suggest to turn this setting on.
– Shadow
Aug 25 '16 at 9:38
|
show 3 more comments
Does it do a count and then execute the real query? The quoted exception has a query in it that isn't a count.
– jedifans
Aug 25 '16 at 6:27
3
Laravel 5.3 features a'strict' => true
inconfig/database.php
which turns on ONLY_FULL_GROUP_BY
– mazedlx
Aug 25 '16 at 6:38
No, it just stopped at counting. But when I removed groupBy it does count and then made the query!
– Abdelaziz Elrashed
Aug 25 '16 at 6:39
@mazedlx I turnstrict option
from false into true and I made a clean cache for everything by artisan command, but still the same problem appears.
– Abdelaziz Elrashed
Aug 25 '16 at 7:00
1
No, @mazedlx did not suggest to turn this setting on.
– Shadow
Aug 25 '16 at 9:38
Does it do a count and then execute the real query? The quoted exception has a query in it that isn't a count.
– jedifans
Aug 25 '16 at 6:27
Does it do a count and then execute the real query? The quoted exception has a query in it that isn't a count.
– jedifans
Aug 25 '16 at 6:27
3
3
Laravel 5.3 features a
'strict' => true
in config/database.php
which turns on ONLY_FULL_GROUP_BY– mazedlx
Aug 25 '16 at 6:38
Laravel 5.3 features a
'strict' => true
in config/database.php
which turns on ONLY_FULL_GROUP_BY– mazedlx
Aug 25 '16 at 6:38
No, it just stopped at counting. But when I removed groupBy it does count and then made the query!
– Abdelaziz Elrashed
Aug 25 '16 at 6:39
No, it just stopped at counting. But when I removed groupBy it does count and then made the query!
– Abdelaziz Elrashed
Aug 25 '16 at 6:39
@mazedlx I turn
strict option
from false into true and I made a clean cache for everything by artisan command, but still the same problem appears.– Abdelaziz Elrashed
Aug 25 '16 at 7:00
@mazedlx I turn
strict option
from false into true and I made a clean cache for everything by artisan command, but still the same problem appears.– Abdelaziz Elrashed
Aug 25 '16 at 7:00
1
1
No, @mazedlx did not suggest to turn this setting on.
– Shadow
Aug 25 '16 at 9:38
No, @mazedlx did not suggest to turn this setting on.
– Shadow
Aug 25 '16 at 9:38
|
show 3 more comments
2 Answers
2
active
oldest
votes
Faced same problem with laravel 5.3
They are trying to enforce strict query writing came with mysql-5.7
However to disabled this just go to config/database.php
and change strict
flag
'mysql' => [
.
.
.
'strict' => false,
//'strict' => true,
.
.
],
Hope this will solve your problem too.
PS - For details on strict query writing refer to @Shadow's answer
add a comment |
This query is against the sql standard and is only valid in mysql under certain sql mode settings. See mysql documentation on MySQL Handling of GROUP BY:
SQL92 and earlier does not permit queries for which the select list,
HAVING condition, or ORDER BY list refer to nonaggregated columns that
are neither named in the GROUP BY clause nor are functionally
dependent on (uniquely determined by) GROUP BY columns. For example,
this query is illegal in standard SQL92 because the nonaggregated name
column in the select list does not appear in the GROUP BY:
SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers
AS c WHERE o.custid = c.custid GROUP BY o.custid; For the query to
be legal in SQL92, the name column must be omitted from the select
list or named in the GROUP BY clause.
SQL99 and later permits such nonaggregates per optional feature T301
if they are functionally dependent on GROUP BY columns: If such a
relationship exists between name and custid, the query is legal. This
would be the case, for example, were custid a primary key of
customers.
You either need to disable the only_full_group_by sql mode (it is part of strict sql mode as well), or use any_value() function in the select list for non-aggregated fields that are not in the group by clause.
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
I have test SQL query in mysql directly and it doesn't compliant. I did update my question with mysql output.
– Abdelaziz Elrashed
Aug 25 '16 at 9:28
1
Your test query differs from the query that reports the error message. You are comparing apples with pears.
– Shadow
Aug 25 '16 at 9:34
Ah, indeed ... you're right! I think I need to dig more inside Laravel Framework itself, it looks like DB::listen didn't report all SQL queries. I will update my question if I found the main Bug in Eloquent Library.
– Abdelaziz Elrashed
Aug 25 '16 at 9:46
1
The query you tested is probably used by eloquent do determine the size of the resultset, but it is not the same query that produces the error message. In your query there is no count() used. I do not even understand why you use group by in that query in the first place.
– Shadow
Aug 25 '16 at 9:46
Yes, it is. I used groupBy in this query because I want to show the current existing permissions for all objects frompermission
table in the Frontend, everyobject
has many permissions.
– Abdelaziz Elrashed
Aug 25 '16 at 10:09
|
show 3 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f39138090%2feloquent-groupby-make-sqlstate42000-with-valid-sql-query-in-laravel-5-3%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Faced same problem with laravel 5.3
They are trying to enforce strict query writing came with mysql-5.7
However to disabled this just go to config/database.php
and change strict
flag
'mysql' => [
.
.
.
'strict' => false,
//'strict' => true,
.
.
],
Hope this will solve your problem too.
PS - For details on strict query writing refer to @Shadow's answer
add a comment |
Faced same problem with laravel 5.3
They are trying to enforce strict query writing came with mysql-5.7
However to disabled this just go to config/database.php
and change strict
flag
'mysql' => [
.
.
.
'strict' => false,
//'strict' => true,
.
.
],
Hope this will solve your problem too.
PS - For details on strict query writing refer to @Shadow's answer
add a comment |
Faced same problem with laravel 5.3
They are trying to enforce strict query writing came with mysql-5.7
However to disabled this just go to config/database.php
and change strict
flag
'mysql' => [
.
.
.
'strict' => false,
//'strict' => true,
.
.
],
Hope this will solve your problem too.
PS - For details on strict query writing refer to @Shadow's answer
Faced same problem with laravel 5.3
They are trying to enforce strict query writing came with mysql-5.7
However to disabled this just go to config/database.php
and change strict
flag
'mysql' => [
.
.
.
'strict' => false,
//'strict' => true,
.
.
],
Hope this will solve your problem too.
PS - For details on strict query writing refer to @Shadow's answer
edited Jan 2 at 6:35
answered Sep 30 '16 at 14:05
CybersupernovaCybersupernova
1,24411231
1,24411231
add a comment |
add a comment |
This query is against the sql standard and is only valid in mysql under certain sql mode settings. See mysql documentation on MySQL Handling of GROUP BY:
SQL92 and earlier does not permit queries for which the select list,
HAVING condition, or ORDER BY list refer to nonaggregated columns that
are neither named in the GROUP BY clause nor are functionally
dependent on (uniquely determined by) GROUP BY columns. For example,
this query is illegal in standard SQL92 because the nonaggregated name
column in the select list does not appear in the GROUP BY:
SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers
AS c WHERE o.custid = c.custid GROUP BY o.custid; For the query to
be legal in SQL92, the name column must be omitted from the select
list or named in the GROUP BY clause.
SQL99 and later permits such nonaggregates per optional feature T301
if they are functionally dependent on GROUP BY columns: If such a
relationship exists between name and custid, the query is legal. This
would be the case, for example, were custid a primary key of
customers.
You either need to disable the only_full_group_by sql mode (it is part of strict sql mode as well), or use any_value() function in the select list for non-aggregated fields that are not in the group by clause.
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
I have test SQL query in mysql directly and it doesn't compliant. I did update my question with mysql output.
– Abdelaziz Elrashed
Aug 25 '16 at 9:28
1
Your test query differs from the query that reports the error message. You are comparing apples with pears.
– Shadow
Aug 25 '16 at 9:34
Ah, indeed ... you're right! I think I need to dig more inside Laravel Framework itself, it looks like DB::listen didn't report all SQL queries. I will update my question if I found the main Bug in Eloquent Library.
– Abdelaziz Elrashed
Aug 25 '16 at 9:46
1
The query you tested is probably used by eloquent do determine the size of the resultset, but it is not the same query that produces the error message. In your query there is no count() used. I do not even understand why you use group by in that query in the first place.
– Shadow
Aug 25 '16 at 9:46
Yes, it is. I used groupBy in this query because I want to show the current existing permissions for all objects frompermission
table in the Frontend, everyobject
has many permissions.
– Abdelaziz Elrashed
Aug 25 '16 at 10:09
|
show 3 more comments
This query is against the sql standard and is only valid in mysql under certain sql mode settings. See mysql documentation on MySQL Handling of GROUP BY:
SQL92 and earlier does not permit queries for which the select list,
HAVING condition, or ORDER BY list refer to nonaggregated columns that
are neither named in the GROUP BY clause nor are functionally
dependent on (uniquely determined by) GROUP BY columns. For example,
this query is illegal in standard SQL92 because the nonaggregated name
column in the select list does not appear in the GROUP BY:
SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers
AS c WHERE o.custid = c.custid GROUP BY o.custid; For the query to
be legal in SQL92, the name column must be omitted from the select
list or named in the GROUP BY clause.
SQL99 and later permits such nonaggregates per optional feature T301
if they are functionally dependent on GROUP BY columns: If such a
relationship exists between name and custid, the query is legal. This
would be the case, for example, were custid a primary key of
customers.
You either need to disable the only_full_group_by sql mode (it is part of strict sql mode as well), or use any_value() function in the select list for non-aggregated fields that are not in the group by clause.
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
I have test SQL query in mysql directly and it doesn't compliant. I did update my question with mysql output.
– Abdelaziz Elrashed
Aug 25 '16 at 9:28
1
Your test query differs from the query that reports the error message. You are comparing apples with pears.
– Shadow
Aug 25 '16 at 9:34
Ah, indeed ... you're right! I think I need to dig more inside Laravel Framework itself, it looks like DB::listen didn't report all SQL queries. I will update my question if I found the main Bug in Eloquent Library.
– Abdelaziz Elrashed
Aug 25 '16 at 9:46
1
The query you tested is probably used by eloquent do determine the size of the resultset, but it is not the same query that produces the error message. In your query there is no count() used. I do not even understand why you use group by in that query in the first place.
– Shadow
Aug 25 '16 at 9:46
Yes, it is. I used groupBy in this query because I want to show the current existing permissions for all objects frompermission
table in the Frontend, everyobject
has many permissions.
– Abdelaziz Elrashed
Aug 25 '16 at 10:09
|
show 3 more comments
This query is against the sql standard and is only valid in mysql under certain sql mode settings. See mysql documentation on MySQL Handling of GROUP BY:
SQL92 and earlier does not permit queries for which the select list,
HAVING condition, or ORDER BY list refer to nonaggregated columns that
are neither named in the GROUP BY clause nor are functionally
dependent on (uniquely determined by) GROUP BY columns. For example,
this query is illegal in standard SQL92 because the nonaggregated name
column in the select list does not appear in the GROUP BY:
SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers
AS c WHERE o.custid = c.custid GROUP BY o.custid; For the query to
be legal in SQL92, the name column must be omitted from the select
list or named in the GROUP BY clause.
SQL99 and later permits such nonaggregates per optional feature T301
if they are functionally dependent on GROUP BY columns: If such a
relationship exists between name and custid, the query is legal. This
would be the case, for example, were custid a primary key of
customers.
You either need to disable the only_full_group_by sql mode (it is part of strict sql mode as well), or use any_value() function in the select list for non-aggregated fields that are not in the group by clause.
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
This query is against the sql standard and is only valid in mysql under certain sql mode settings. See mysql documentation on MySQL Handling of GROUP BY:
SQL92 and earlier does not permit queries for which the select list,
HAVING condition, or ORDER BY list refer to nonaggregated columns that
are neither named in the GROUP BY clause nor are functionally
dependent on (uniquely determined by) GROUP BY columns. For example,
this query is illegal in standard SQL92 because the nonaggregated name
column in the select list does not appear in the GROUP BY:
SELECT o.custid, c.name, MAX(o.payment) FROM orders AS o, customers
AS c WHERE o.custid = c.custid GROUP BY o.custid; For the query to
be legal in SQL92, the name column must be omitted from the select
list or named in the GROUP BY clause.
SQL99 and later permits such nonaggregates per optional feature T301
if they are functionally dependent on GROUP BY columns: If such a
relationship exists between name and custid, the query is legal. This
would be the case, for example, were custid a primary key of
customers.
You either need to disable the only_full_group_by sql mode (it is part of strict sql mode as well), or use any_value() function in the select list for non-aggregated fields that are not in the group by clause.
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
answered Aug 25 '16 at 6:38
ShadowShadow
25.8k92844
25.8k92844
I have test SQL query in mysql directly and it doesn't compliant. I did update my question with mysql output.
– Abdelaziz Elrashed
Aug 25 '16 at 9:28
1
Your test query differs from the query that reports the error message. You are comparing apples with pears.
– Shadow
Aug 25 '16 at 9:34
Ah, indeed ... you're right! I think I need to dig more inside Laravel Framework itself, it looks like DB::listen didn't report all SQL queries. I will update my question if I found the main Bug in Eloquent Library.
– Abdelaziz Elrashed
Aug 25 '16 at 9:46
1
The query you tested is probably used by eloquent do determine the size of the resultset, but it is not the same query that produces the error message. In your query there is no count() used. I do not even understand why you use group by in that query in the first place.
– Shadow
Aug 25 '16 at 9:46
Yes, it is. I used groupBy in this query because I want to show the current existing permissions for all objects frompermission
table in the Frontend, everyobject
has many permissions.
– Abdelaziz Elrashed
Aug 25 '16 at 10:09
|
show 3 more comments
I have test SQL query in mysql directly and it doesn't compliant. I did update my question with mysql output.
– Abdelaziz Elrashed
Aug 25 '16 at 9:28
1
Your test query differs from the query that reports the error message. You are comparing apples with pears.
– Shadow
Aug 25 '16 at 9:34
Ah, indeed ... you're right! I think I need to dig more inside Laravel Framework itself, it looks like DB::listen didn't report all SQL queries. I will update my question if I found the main Bug in Eloquent Library.
– Abdelaziz Elrashed
Aug 25 '16 at 9:46
1
The query you tested is probably used by eloquent do determine the size of the resultset, but it is not the same query that produces the error message. In your query there is no count() used. I do not even understand why you use group by in that query in the first place.
– Shadow
Aug 25 '16 at 9:46
Yes, it is. I used groupBy in this query because I want to show the current existing permissions for all objects frompermission
table in the Frontend, everyobject
has many permissions.
– Abdelaziz Elrashed
Aug 25 '16 at 10:09
I have test SQL query in mysql directly and it doesn't compliant. I did update my question with mysql output.
– Abdelaziz Elrashed
Aug 25 '16 at 9:28
I have test SQL query in mysql directly and it doesn't compliant. I did update my question with mysql output.
– Abdelaziz Elrashed
Aug 25 '16 at 9:28
1
1
Your test query differs from the query that reports the error message. You are comparing apples with pears.
– Shadow
Aug 25 '16 at 9:34
Your test query differs from the query that reports the error message. You are comparing apples with pears.
– Shadow
Aug 25 '16 at 9:34
Ah, indeed ... you're right! I think I need to dig more inside Laravel Framework itself, it looks like DB::listen didn't report all SQL queries. I will update my question if I found the main Bug in Eloquent Library.
– Abdelaziz Elrashed
Aug 25 '16 at 9:46
Ah, indeed ... you're right! I think I need to dig more inside Laravel Framework itself, it looks like DB::listen didn't report all SQL queries. I will update my question if I found the main Bug in Eloquent Library.
– Abdelaziz Elrashed
Aug 25 '16 at 9:46
1
1
The query you tested is probably used by eloquent do determine the size of the resultset, but it is not the same query that produces the error message. In your query there is no count() used. I do not even understand why you use group by in that query in the first place.
– Shadow
Aug 25 '16 at 9:46
The query you tested is probably used by eloquent do determine the size of the resultset, but it is not the same query that produces the error message. In your query there is no count() used. I do not even understand why you use group by in that query in the first place.
– Shadow
Aug 25 '16 at 9:46
Yes, it is. I used groupBy in this query because I want to show the current existing permissions for all objects from
permission
table in the Frontend, every object
has many permissions.– Abdelaziz Elrashed
Aug 25 '16 at 10:09
Yes, it is. I used groupBy in this query because I want to show the current existing permissions for all objects from
permission
table in the Frontend, every object
has many permissions.– Abdelaziz Elrashed
Aug 25 '16 at 10:09
|
show 3 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f39138090%2feloquent-groupby-make-sqlstate42000-with-valid-sql-query-in-laravel-5-3%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2COwXWV Uq0mGDABLSQdiI,Kf1HFb7Tyrz
Does it do a count and then execute the real query? The quoted exception has a query in it that isn't a count.
– jedifans
Aug 25 '16 at 6:27
3
Laravel 5.3 features a
'strict' => true
inconfig/database.php
which turns on ONLY_FULL_GROUP_BY– mazedlx
Aug 25 '16 at 6:38
No, it just stopped at counting. But when I removed groupBy it does count and then made the query!
– Abdelaziz Elrashed
Aug 25 '16 at 6:39
@mazedlx I turn
strict option
from false into true and I made a clean cache for everything by artisan command, but still the same problem appears.– Abdelaziz Elrashed
Aug 25 '16 at 7:00
1
No, @mazedlx did not suggest to turn this setting on.
– Shadow
Aug 25 '16 at 9:38