I have a GraphQL schema that returns orders and the products that are part of that order. I have added a filter to return only orders that contains products searched for by the user. This filter is done on a icontains
.
However I have found that if a user searches for angel
for example, and an order contains two or more products that contain the string angel
it returns the order that many times duplicating the records returned to the frontend.
Query:
{
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
}
}
}
}
}
}
}
Json Response:
{
"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
Your query is likely returning duplicate results. It's sometimes not enough to just call .distinct()
.
You must specify DISTINCT ON <field>
as it seems your id
field is returning an empty string. Consider a adding .distinct('cmOrderId')
to your query.
I also notice that most of your purchase_order_product
queries do not include distinct
—consider adding the .distinct('cmOrderId')
to all of these queries.
Furthermore, I notice that you call all()
on your filter()
queries—it has no effect on the resulting object.
https://docs.djangoproject.com/en/4.0/ref/models/querysets/#filter
https://docs.djangoproject.com/en/4.0/ref/models/querysets/#django.db.models.query.QuerySet.distinct