以雄辩的方式构建SQL查询 [英] Building SQL query in eloquent

问题描述

how would I go about writing this SQL query in Laravel 4.2 using eloquent?

SELECT *
FROM participants
WHERE user_id IN (1, 2) 
GROUP BY convo_id
HAVING count(DISTINCT user_id) = 2
AND count(DISTINCT convo_id) = 1

我已经尝试过了

Participant::whereIn('user_id', $participantIds)
                    ->groupBy('convo_id')
                    ->havingRaw('count(DISTINCT user_id) = '. sizeof($participantIds))
                    ->whereRaw('count(DISTINCT convo_id) = 1')
                    ->get();

但是我收到这个错误

SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select * from `participants` where `user_id` in (1, 2, 4) and count(DISTINCT convo_id) = 1 group by `convo_id` having count(DISTINCT user_id) = 3)

推荐答案

我的错误whereRaw也应该是有的。

My mistake. whereRaw should also be havingRaw.

Participant::whereIn('user_id', $participantIds)
                    ->groupBy('convo_id')
                    ->havingRaw('count(DISTINCT user_id) = '. sizeof($participantIds))
                    ->havingRaw('count(DISTINCT convo_id) = 1')
                    ->get();

这篇关于以雄辩的方式构建SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！