Django按用户ID匹配两个查询集 缺失配置补0返回指定结果

问题场景 #

现有两个查询结果集：

• setting：仅返回符合筛选条件的用户配置得分，包含用户ID、A类配置值、B类配置值、C类配置值
• uploader：包含全量目标用户ID列表，需要保留原有排序
  需求为遍历全量用户列表，匹配对应配置值：用户ID存在于setting中时取对应三类配置值，不存在时三类配置值统一补0，最终输出嵌套列表格式，所有值转为字符串类型，浮点型整数（如50.0）需转为整数形式的字符串。

现有代码 #

# 配置得分查询
setting = Subject.objects.annotate(
    A_setup=Count('id', filter=Q(type='A'), distinct=True) * Value(50),
    B_setup=Count('id', filter=Q(type='B'), distinct=True) * Value(30),
    C_setup=Count('id', filter=(~Q(type='A') & ~Q(type='B') & ~Q(type__isnull=True) & Q(id__in=workers.filter(worker=1).values('id')))) * Value(10)
).values('setting__user_id', 'A_setup', 'B_setup', 'C_setup')

# setting示例返回值
setting = [
    {'setting__user_id': 4, 'A_setting': 50.0, 'B_setting': 120, 'C_setting': 10.0},
    {'setting__user_id': 34, 'A_setting': 0.0, 'B_setting': 0, 'C_setting': 0.0},
    {'setting__user_id': 33, 'A_setting': 0.0, 'B_setting': 150, 'C_setting': 0.0},
    {'setting__user_id': 30, 'A_setting': 0.0, 'B_setting': 150, 'C_setting': 0.0},
    {'setting__user_id': 74, 'A_setting': 50.0, 'B_setting': 120, 'C_setting': 10.0}
]

# 全量用户查询
uploader = Feedback.objects.values('uploader_id').distinct()
# uploader示例返回值
uploader = [
    {'uploader_id': 25}, {'uploader_id': 20}, {'uploader_id': 74}, {'uploader_id': 34},
    {'uploader_id': 93}, {'uploader_id': 88}, {'uploader_id': 73}, {'uploader_id': 89},
    {'uploader_id': 30}, {'uploader_id': 33}, {'uploader_id': 85}, {'uploader_id': 4},
    {'uploader_id': 46}
]

预期输出格式 #

[
    ['25', '0', '0', '0'], ['20', '0', '0', '0'], ['74', '50', '120', '10'],
    ['34', '0', '0', '0'], ['93', '0', '0', '0'], ['88', '0', '0', '0'],
    ['73', '0', '0', '0'], ['89', '0', '0', '0'], ['30', '0', '150', '0'],
    ['33', '0', '150', '0'], ['85', '0', '0', '0'], ['4', '50', '120', '10'],
    ['46', '0', '0', '0']
]

实现方法 #

优先将setting结果转换为以用户ID为键的字典做映射，避免双层循环匹配，时间复杂度为O(n)，数据量较大时性能优势明显。

# 1. 构建用户ID-配置值的映射字典，兼容字段名笔误（annotate定义为A_setup，示例返回为A_setting）
setting_map = {}
for conf_item in setting:
    uid = conf_item['setting__user_id']
    setting_map[uid] = (
        conf_item.get('A_setup', conf_item.get('A_setting', 0)),
        conf_item.get('B_setup', conf_item.get('B_setting', 0)),
        conf_item.get('C_setup', conf_item.get('C_setting', 0))
    )

# 2. 遍历全量用户列表，组装结果
result = []
for user in uploader:
    user_id = user['uploader_id']
    # 匹配不到配置时默认三个值为0
    a_val, b_val, c_val = setting_map.get(user_id, (0, 0, 0))
    
    # 格式化值：浮点型整数转整数，再统一转字符串
    def format_val(val):
        if isinstance(val, float) and val.is_integer():
            val = int(val)
        return str(val)
    
    result.append([
        str(user_id),
        format_val(a_val),
        format_val(b_val),
        format_val(c_val)
    ])

注意点 #

• 代码保留了uploader原有的返回顺序，不会打乱用户排列
• 自动对齐格式：自动将50.0这类浮点数整数值转为'50'格式，和预期输出完全一致
• 做了字段名兼容处理，避免因为annotate字段名和实际返回字段名不一致导致的KeyError

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