能否在Django QuerySet中添加子查询表？需实现指定SQL并支持链式过滤

要实现你要的SQL逻辑同时保留QuerySet的链式过滤能力，可以分三步处理：创建两个子查询、合并结果、用Window函数去重取权重最高的记录。以下是具体实现（假设你的模型名为Course，且使用PostgreSQL数据库，因为ts_rank_cd和similarity是PG专属函数）：

1. 构建两个子查询QuerySet #

分别处理全文搜索匹配和相似度匹配，并计算对应的weight字段：

全文搜索子查询 #

from django.db.models import RawSQL

search_term = "sales"

# 匹配tsvector并计算weight
qs_ts = Course.objects.annotate(
    weight=RawSQL("3 + ts_rank_cd(to_tsvector(title), to_tsquery(%s), 32)", [search_term])
).filter(
    RawSQL("to_tsvector(title) @@ to_tsquery(%s)", [search_term])
)

相似度匹配子查询 #

qs_sim = Course.objects.annotate(
    weight=RawSQL("2 + similarity(title, %s)", [search_term])
).filter(
    RawSQL("title %% %s", [search_term])
)

2. 合并两个子查询 #

使用union(all=True)合并结果，保留重复记录（对应SQL的union all）：

combined_qs = qs_ts.union(qs_sim, all=True)

3. 按ID去重，保留权重最高的记录 #

用Django的Window函数+RowNumber实现类似distinct on (id)的效果，筛选每个ID下权重最高的第一条记录：

from django.db.models import Window
from django.db.models.functions import RowNumber

final_qs = combined_qs.annotate(
    row_num=Window(
        expression=RowNumber(),
        partition_by="id",
        order_by="-weight"
    )
).filter(row_num=1)

关键说明 #

• 这个final_qs是标准的Django QuerySet，支持后续链式调用filter()、exclude()、order_by()等方法，完全满足你的需求。
• 确保你的Course模型的title字段已添加GIN索引（优化全文搜索和相似度查询性能）：

  from django.contrib.postgres.indexes import GinIndex
  
  class Course(models.Model):
      title = models.CharField(max_length=255)
      # 其他字段...
  
      class Meta:
          indexes = [
              GinIndex(fields=["title"], name="course_title_gin_idx")
          ]
  

• 所有SQL片段都使用了参数化查询，避免SQL注入风险。

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