dpjw67160
2018-09-26 11:56
浏览 56
已采纳

我想将SQL查询转换为Laravel eloquent

I have this SQL query:

SELECT * 
FROM posts 
RIGHT JOIN files ON posts.id = files.fileable_id 
GROUP BY posts.id 
HAVING posts.user_id = 3125

It works, but I need to convert it to Laravel eloquent I tried this code

 $postsHaveFileCount = DB::table('posts')
                     ->rightJoin('files', 'posts.id', '=', 'files.fileable_id')
                     ->groupBy('posts.id')
                     ->having('posts.user_id', '=', $user->id)
                     ->get()->count();
  echo $postsHaveFileCount;

But i have this error

(2/2) QueryException SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #17 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'staff.files.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from posts right join files on posts.id = files.fileable_id group by posts.id having posts.user_id = 3125)

I will be thanks for all to help me to solve the problem, thanks in advance.

图片转代码服务由CSDN问答提供 功能建议

我有这个SQL查询:

  SELECT * 
FROM  posts 
RIGHT JOIN文件ON posts.id = files.fileable_id 
GROUP BY posts.id 
HAVING posts.user_id = 3125 
   
 
 

它有效,但我需要 将它转换为Laravel eloquent我试过这段代码

  $ postsHaveFileCount = DB :: table('posts')
  - > rightJoin('files','posts.id  ','=','files.fileable_id')
  - > groupBy('posts.id')
  - >有('posts.user_id','=',$ user-> id)\  n  - > get() - > count(); 
 echo $ postsHaveFileCount; 
   
 
 

但是我有这个错误

(2/2)QueryException SQLSTATE [42000]:语法错误或访问 违规:1055 SELECT列表的表达式#17不在GROUP BY 子句中,并包含非聚合列'staff.files .id'在功能上不依赖于GROUP BY子句中的列; 这与\ sql_mode = only_full_group_by(SQL:select * from posts 右连接文件 posts id <不兼容 / code> = files fileable_id group by posts id posts user_id = 3125)

我将感谢所有人帮助我解决问题,提前谢谢。

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • douhe8981 2018-09-26 12:19
    已采纳

    Since you are using group by, you have to set ONLY the columns which are used in the group by statement in the 'select' section. If you are not using any values at 'select', Laravel will automatically pick all the columns which gives the above error. Check the modified code below

        $postsHaveFileCount = DB::table('posts')
                        ->select('posts.id')
                        ->rightJoin('files', 'posts.id', '=', 'files.fileable_id')
                        ->groupBy('posts.id', 'posts.user_id')
                        ->having('posts.user_id', '=', $user->id)
                        ->get()->count();
        echo $postsHaveFileCount;
    
    已采纳该答案
    打赏 评论
  • dongyuxin5353 2018-09-26 12:16

    Try this. Copy and paste following statement in your code and run it.

    SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
    
    打赏 评论
  • duanhui7329 2018-09-26 12:22

    Just Run the

    $ sudo mysql -u root -p
    

    change the SQL Mode for Your MySQL Server Instance

    mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
    

    another way would be using the mysql configs go to /etc/mysql/my.cnf

    **add a section for [mysqld] and right below it add the statement sql_mode = ""
    restart the mysql service $ sudo systemctl restart mysql**
    
    打赏 评论

相关推荐 更多相似问题