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

我想将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.

  • 写回答

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;
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题