MySQL下Django查询customer去重对应的CfgXmppSessions对象objid

class CfgXmppSessions(models.Model):
    objid = models.AutoField(primary_key=True)
    customer_objid = models.ForeignKey('CstContact', blank=True, null=True, on_delete=models.DO_NOTHING,db_constraint=False, db_column='customer_objid', db_index=False)
    character_name = models.CharField(max_length=45)
    character_channel_type = models.CharField(max_length=45, blank=True, null=True)
    character_objid = models.ForeignKey('PerfCharacter', blank=True, null=True, on_delete=models.DO_NOTHING,db_constraint=False, db_column='character_objid', db_index=False)

需求为获取所有customer不重复的记录对应的objid值，提供两种常用实现方式：

方法1：Django ORM实现（推荐） #

无需编写原生SQL，通过分组查询实现，可自定义每组objid的选取规则：

from django.db.models import Min

# 按customer_objid分组，取每组最小的objid，替换为Max即可取每组最大的objid
objid_list = CfgXmppSessions.objects.values("customer_objid") \
            .annotate(target_objid=Min("objid")) \
            .values_list("target_objid", flat=True)

# 如需排除customer_objid为NULL的记录，加上filter条件即可：
# objid_list = CfgXmppSessions.objects.filter(customer_objid__isnull=False) \
#             .values("customer_objid") \
#             .annotate(target_objid=Min("objid")) \
#             .values_list("target_objid", flat=True)

方法2：原生SQL实现 #

适合逻辑更复杂的场景，直接执行SQL查询：

from django.db import connection

def get_distinct_customer_objids():
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT MIN(objid) FROM cfg_xmpp_sessions 
            -- 如需排除customer为NULL的记录，取消注释下行
            -- WHERE customer_objid IS NOT NULL
            GROUP BY customer_objid
        """)
        return [item[0] for item in cursor.fetchall()]

内容的提问来源于stack exchange，提问作者Sourav Singh Gehlot