Django ORM实现Self Join查询及结果去重方法咨询

问题1：自连接查询的Django ORM实现 #

首先你需要先定义和test_users表对应的模型，示例如下：

from django.db import models

class TestUser(models.Model):
    email = models.EmailField()
    
    class Meta:
        db_table = "test_users"

要实现你给出的自连接SQL，最直观的写法是用ORM的extra方法（该方法官方不推荐，但这种无外键的自连接场景下写法最简单，生成的SQL和你示例的几乎完全一致）：

from django.db.models import F

result = TestUser.objects.extra(
    select={'u2id': 'u2.id'},
    tables=['test_users as u2'],
    where=[
        'test_users.email = u2.email',
        'test_users.id != u2.id'
    ]
).values_list('id', 'u2id')

如果你不想用extra，可以用窗口函数的原生ORM写法实现：

from django.db.models import Count, Window, F
from django.db.models.functions import RowNumber

# 给同邮箱的用户按id排序打行号
row_num = Window(
    expression=RowNumber(),
    partition_by=F('email'),
    order_by=F('id').asc()
)
# 筛选出有重复邮箱的所有用户，按邮箱、行号排序
user_list = TestUser.objects.annotate(
    rn=row_num
).filter(
    email__in=TestUser.objects.values('email').annotate(cnt=Count('id')).filter(cnt__gte=2).values('email')
).order_by('email', 'rn')

# 拼接得到所有两两配对
result = []
for i in range(0, len(user_list), 2):
    if i+1 < len(user_list) and user_list[i].email == user_list[i+1].email:
        result.append((user_list[i].id, user_list[i+1].id))
        result.append((user_list[i+1].id, user_list[i].id))

问题2：去重仅保留单条记录的实现 #

要去除(1,2)和(2,1)这类重复配对，只需要加一个id大小比较的条件，让配对始终是小id在前大 id 在后即可：

基于extra的写法 #

result = TestUser.objects.extra(
    select={'u2id': 'u2.id'},
    tables=['test_users as u2'],
    where=[
        'test_users.email = u2.email',
        'test_users.id < u2.id'
    ]
).values_list('id', 'u2id')

原生ORM写法 #

from django.db.models import Count, Window, F
from django.db.models.functions import RowNumber

row_num = Window(
    expression=RowNumber(),
    partition_by=F('email'),
    order_by=F('id').asc()
)
# 筛选重复邮箱用户，取每个邮箱的前两个用户即可
duplicate_users = TestUser.objects.annotate(
    rn=row_num
).filter(
    email__in=TestUser.objects.values('email').annotate(cnt=Count('id')).filter(cnt__gte=2).values('email'),
    rn__lte=2
).order_by('email', 'rn').values_list('id', flat=True)

# 两两组合得到无重复的配对
result = list(zip(duplicate_users[::2], duplicate_users[1::2]))

上面两种写法最终得到的结果都只会保留(1, 2)这一条符合要求的记录。

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