我想将SQL查询转换为Laravel eloquent

Question

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.

Answer 1

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;