MySQL/Eloquent：统计多对多关联中各偏好的启用用户数

表结构说明 #

• preferences表：包含id（偏好唯一ID）、code（偏好编码）
• user_preferences表：包含user_id（用户ID）、preference_id（关联偏好ID）、value（布尔型，标记偏好是否启用）
• users表：存储用户基础信息，核心字段为user_id

需求规则 #

统计每个偏好的启用用户数量，满足以下任一条件即视为用户启用该偏好：

1. 用户在user_preferences中对该偏好的value为true
2. 用户未在user_preferences中添加该偏好的任何记录（默认启用）

解决方案SQL #

SELECT
    p.id AS preference_id,
    p.code AS preference_code,
    COUNT(CASE
        WHEN up.value IS NULL OR up.value = true THEN 1
        ELSE NULL
    END) AS enabled_user_count
FROM
    preferences p
CROSS JOIN
    users u
LEFT JOIN
    user_preferences up 
        ON u.user_id = up.user_id 
        AND p.id = up.preference_id
GROUP BY
    p.id, p.code;

逻辑说明 #

1. 生成全量用户-偏好组合：通过CROSS JOIN users得到所有偏好与所有用户的配对，确保每个用户对每个偏好都有一条待判定的记录
2. 关联偏好设置记录：使用LEFT JOIN关联user_preferences，未设置该偏好的用户会返回NULL值
3. 判定启用状态并计数：用CASE语句筛选满足启用条件的记录，通过COUNT统计有效数量
4. 按偏好分组汇总：最终按偏好ID和编码分组，得到每个偏好的启用用户总数

示例验证 #

给定示例数据：

• users表：2个用户（user_id=1、2）
• preferences表：2个偏好（id=1, code=1_pref；id=2, code=2_pref）
• user_preferences表：1条记录（user_id=1, preference_id=2, value=false）

执行SQL后结果：

完全符合预期结果：1_pref的启用用户数为2，2_pref的启用用户数为1

内容的提问来源于stack exchange，提问作者Bipa