在这里,您可以使用的一个技巧是将GROUP BY
应用于两个ID列中较小的那个和较大的那个:
SELECT LEAST(recipient_id, sender_id), GREATEST(recipient_id, sender_id), COUNT(*)
FROM messengers
WHERE recipient_id = 41 OR sender_id = 41
GROUP BY LEAST(recipient_id, sender_id), GREATEST(recipient_id, sender_id)
SELECT * FROM (SELECT * FROM messengers ORDER BY id desc) as A WHERE recipient_id = 41 OR sender_id = 41 GROUP BY LEAST(recipient_id, sender_id), GREATEST(recipient_id, sender_id)
但我现在遇到了困难,无法将其转换为 Laravel 查询构建器。 - user1294914SELECT
中的列要么也出现在GROUP BY
中,要么是聚合函数。因此,在这里不应使用SELECT *
。您可以提出一个新问题来解决您的 Laravel 问题。 - Tim Biegeleisen