php - 我想将 SQL 查询转换为 Laravel Eloquent

Question

我有这个 SQL 查询:

SELECT * 
FROM posts 
RIGHT JOIN files 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')
                     ->having('posts.user_id', '=', $user->id)
                     ->get()->count();
  echo $postsHaveFileCount;

但是我有这个错误

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

我将感谢所有帮助我解决问题的人，在此先感谢。

Answer 1

由于您使用的是分组依据，因此您必须仅在“选择”部分中设置分组依据语句中使用的列。如果你没有在 'select' 处使用任何值，Laravel 将自动选择所有出现上述错误的列。检查下面修改后的代码

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