GraphQL icontains过滤关联产品时返回重复订单结果问题

• 已搭建返回订单及所属关联商品的GraphQL Schema，添加了基于icontains的过滤逻辑，目标是仅返回包含用户搜索关键词对应商品的订单。
• 测试发现异常：搜索关键词比如angel时，若单个订单下存在2个及以上商品名称包含angel字符串，该订单会按匹配商品的数量重复返回，前端接收到重复记录。

复现用查询语句 #

{
  dateFilterList
  Purchases(first: 15, after: "", username_Icontains: "", PurchaseOrdersProductsOrderId_ProductName: "angel") {
    pageCursors {
      previous {
        cursor
      }
      first {
        cursor
        page
      }
      around {
        cursor
        isCurrent
        page
      }
      last {
        cursor
        page
      }
      next {
        cursor
      }
    }
    edges {
      node {
        id
        cmOrderId
        username
        date
        articles
        merchandiseValue
        shippingValue
        trusteeValue
        totalValue
        products {
          edges {
            node {
              id
              productId
              productName
              productNumber
            }
          }
        }
      }
    }
  }
}

异常响应示例 #

可以看到cmOrderId为12345的同一订单在edges中重复出现：

{
  "data": {
  ...
  "purchases": {
    ...
      "edges": [
        {
          "node": {
            "id": "",
            "cmOrderId": 12345,
            "username": "UserA",
            "date": "2022-06-29T16:38:51",
            "articles": 40,
            "merchandiseValue": "",
            "shippingValue": "",
            "trusteeValue": "",
            "totalValue": "",
            "products": {
              "edges": [
                {
                  "node": {
                    "id": "",
                    "productId": "",
                    "productName": "Angel of Grace",
                    "productNumber": "1"
                  }
                },
                {
                  "node": {
                    "id": "",
                    "productId": "",
                    "productName": "Angel of Sanctions",
                    "productNumber": "1"
                  }
                },
                ...
              ]
            }
          },
          "node": {
            "id": "",
            "cmOrderId": 12345,
            "username": "UserA",
            "date": "2022-06-29T16:38:51",
            "articles": 40,
            "merchandiseValue": "",
            "shippingValue": "",
            "trusteeValue": "",
            "totalValue": "",
            "products": {
              "edges": [
                {
                  "node": {
                    "id": "",
                    "productId": "",
                    "productName": "Angel of Grace",
                    "productNumber": "1"
                  }
                },
                {
                  "node": {
                    "id": "",
                    "productId": "",
                    "productName": "Angel of Sanctions",
                    "productNumber": "1"
                  }
                },
                ...
              ]
            }
          },
          ...

原有Schema定义代码 #

# region Integration Purchase Orders
class PurchaseOrderFilter(FilterSet):
    class Meta:
        model = purchase_orders
        fields = {'date': ['gt', 'lt', 'isnull'], 'username': ['icontains'],}

    purchase_orders_products_order_id__product_name = CharFilter(lookup_expr="icontains")


class PurchaseOrderProductFilter(FilterSet):
    class Meta:
        model = purchase_orders_products
        fields = {"product_name": ["icontains"]}


class PurchasesProducts(DjangoObjectType):
    id = graphene.ID(source='pk', required=True)

    class Meta:
        model = purchase_orders_products
        interfaces = (graphene.relay.Node,)
        filterset_class = PurchaseOrderProductFilter


class Purchases(DjangoObjectType):
    id = graphene.ID(source='pk', required=True)
    products = DjangoFilterConnectionField(PurchasesProducts)

    class Meta:
        model = purchase_orders
        interfaces = (graphene.relay.Node,)
        filterset_class = PurchaseOrderFilter
        connection_class = ArtsyConnection

    @staticmethod
    def resolve_products(self, info, **kwargs):
        return purchase_orders_products.objects.filter(order_id=self.id).order_by('product_name').all()


class PurchasesQuery(ObjectType):
    date_filter_list = graphene.List(graphene.List(graphene.String))
    purchases = ArtsyConnectionField(Purchases)

    @staticmethod
    def resolve_date_filter_list(self, info, **kwargs):
        years = purchase_orders.objects.filter(user_id=info.context.user.id).annotate(year=ExtractYear('date'), month=ExtractMonth('date'), ).order_by().values_list('year', 'month').order_by('-year', '-month').distinct()
        return years

    @staticmethod
    def resolve_purchases(self, info, **kwargs):
        return purchase_orders.objects.filter(user_id=info.context.user.id).all().order_by("-date")


PurchasesSchema = graphene.Schema(query=PurchasesQuery)
# endregion

跨关联表过滤产生重复结果是Django ORM连接查询的默认行为：purchase_orders_products_order_id__product_name这个跨表过滤条件会让ORM生成purchase_orders和purchase_orders_products的内连接SQL，只要关联商品表中有N条匹配关键词的记录，主表订单就会在连接结果中重复出现N次。Graphene-Django的过滤器默认不会对查询结果做去重，最终返回重复的订单节点。

二选一即可，推荐方案2，性能更优。

方案1：查询集添加distinct()去重 #

修改resolve_purchases方法，给返回的查询集加上去重逻辑，改动最小：

@staticmethod
def resolve_purchases(self, info, **kwargs):
    return purchase_orders.objects.filter(user_id=info.context.user.id).order_by("-date").distinct()

注意：如果使用PostgreSQL数据库，且后续排序字段包含关联表字段，可以明确指定按主键去重避免排序字段影响去重效果：.distinct("id")。

方案2：用EXISTS子查询替代跨表JOIN过滤 #

自定义过滤方法，用EXISTS半连接查询判断订单是否存在匹配商品，从根源上避免JOIN产生的重复行，数据量大时性能比方案1更好：

from django.db.models import Exists, OuterRef

class PurchaseOrderFilter(FilterSet):
    # 把默认的跨表过滤改成自定义方法
    purchase_orders_products_order_id__product_name = CharFilter(method="filter_match_product")

    class Meta:
        model = purchase_orders
        fields = {'date': ['gt', 'lt', 'isnull'], 'username': ['icontains'],}

    def filter_match_product(self, queryset, name, value):
        if not value:
            return queryset
        # 构造子查询：匹配当前订单下符合关键词的商品
        match_product = purchase_orders_products.objects.filter(
            order_id=OuterRef("pk"),
            product_name__icontains=value
        )
        return queryset.filter(Exists(match_product))

额外优化点 #

当前resolve_products方法默认返回订单下所有商品，不会跟随搜索关键词过滤商品列表，如果需要让订单下的商品节点也仅返回匹配关键词的商品，可以修改resolve方法：

@staticmethod
def resolve_products(self, info, **kwargs):
    qs = purchase_orders_products.objects.filter(order_id=self.id).order_by('product_name')
    # 提取商品名过滤参数
    product_keyword = kwargs.get("productName_Icontains") or kwargs.get("product_name__icontains")
    if product_keyword:
        qs = qs.filter(product_name__icontains=product_keyword)
    return qs

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