CODEWITHSUNDEEP
phplaravelgroup-byeloquent
Okechukwu Eze
Okechukwu Eze

Reputation: 149

GroupBy Error in Laravel Eloquent Builder

I dont want to believe there is a bug in laravel 5.5 cos i am amazed how i keep getting errors on the Eloquent Query Builder when ever i use the groupBy in my query.

Tried converting this SQL that works seamlessly to Eloquent and i keep getting Errors on the groupBy..

Had a similar issue with an SQL which i complained here and till now i have not gotten a perfect answer for it open issue here

SELECT * FROM arm_articles
    INNER JOIN arm_interest ON arm_articles.article_category = arm_interest.category_id 
    WHERE arm_articles.article_contributor_id='1322' 
    GROUP BY arm_articles.article_id

ELOQUENT VERSION THAT RETURNS ERROR

$contributor_id =1322;
DB::table('arm_articles')
->join('arm_interest', function($join) {
    $join->on('arm_articles.article_category','=','arm_interest.category_id');
})
->having('arm_articles.article_contributor_id', '=', $contributor_id)
->groupBy('arm_articles.article_id')->get()

ERROR MESSAGE

SQLSTATE[42000]: Syntax error or access violation: 1055 'knowledge_db.arm_articles.id' isn't in GROUP BY (SQL: select * from 'arm_articles' inner join 'arm_interest' on 'arm_articles'.'article_category' = 'arm_interest'.'category_id' group by 'arm_articles'.'article_id' having 'arm_articles'.'article_contributor_id' = 1322)

So like i said b4 its really weared why i will get the error in the first place as adding ->toSql to the query returns the right SQL.

Upvotes: 0

Views: 905

Answers (1)

vpalade
vpalade

Reputation: 1437

  • Add this configuration to your mysql database config in config/database.php: 

       'mysql' => [
        ....
        'strict' => false,
        //'strict' => true,
    ],

  • Edit sql_mode in your mysql config file (my.ini or my.cnf) [mysqld]

    sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  • Restart your MySQL

Upvotes: 1

Related Questions