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

Multi tool use
Multi tool use












6















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: 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)




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:
SQL Query Output
Even So, I tested in mysql command directly and it does work just fine, look at mysql output:



enter image description here



Any idea?



Thanks










share|improve this question

























  • 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 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











  • @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
















6















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: 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)




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:
SQL Query Output
Even So, I tested in mysql command directly and it does work just fine, look at mysql output:



enter image description here



Any idea?



Thanks










share|improve this question

























  • 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 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











  • @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














6












6








6


4






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: 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)




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:
SQL Query Output
Even So, I tested in mysql command directly and it does work just fine, look at mysql output:



enter image description here



Any idea?



Thanks










share|improve this question
















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: 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)




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:
SQL Query Output
Even So, I tested in mysql command directly and it does work just fine, look at mysql output:



enter image description here



Any idea?



Thanks







php mysql laravel laravel-5 eloquent






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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











  • @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



















  • 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 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











  • @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

















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












2 Answers
2






active

oldest

votes


















9














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






share|improve this answer

































    8














    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;






    share|improve this answer
























    • 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 from permission table in the Frontend, every object has many permissions.

      – Abdelaziz Elrashed
      Aug 25 '16 at 10:09











    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    9














    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






    share|improve this answer






























      9














      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






      share|improve this answer




























        9












        9








        9







        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






        share|improve this 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







        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Jan 2 at 6:35

























        answered Sep 30 '16 at 14:05









        CybersupernovaCybersupernova

        1,24411231




        1,24411231

























            8














            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;






            share|improve this answer
























            • 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 from permission table in the Frontend, every object has many permissions.

              – Abdelaziz Elrashed
              Aug 25 '16 at 10:09
















            8














            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;






            share|improve this answer
























            • 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 from permission table in the Frontend, every object has many permissions.

              – Abdelaziz Elrashed
              Aug 25 '16 at 10:09














            8












            8








            8







            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;






            share|improve this answer













            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;







            share|improve this answer












            share|improve this answer



            share|improve this answer










            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 from permission table in the Frontend, every object 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








            • 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 from permission table in the Frontend, every object 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


















            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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
            3x ow,dfhwRe8seUh5DN4QjJhC,A54 6upD5 bYa7GmIRMoUqlFD,Taf7Ls89Zb17

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas