Django - Annotating, Filtering and Sorting on a none to many relationships doesn't work

Question

I am working on a table that display a list of orders with the option to filter and to sort depending either to direct attribute of the order or indirectly to attribute of related models. The two models in question are Order and Block. An Order can have none too many Blocks associated to it and Block always have one unique order.

class Order(CustomBaseModel):

    date_of_order = models.DateField(default=timezone.now, verbose_name="Date of order"
    ...

class Block(CustomBaseModel):

    ...
    order = models.ForeignKey(Order, on_delete=models.CASCADE)
    ...

To be able to filter orders with or without block, I annotate my queryset using the following:

       order_queryset = Order.objects.all().annotate(
            is_material_available=Case(
                    When(block__isnull=False, then=Value(True)),
                    default=Value(False),
                    output_field=BooleanField()
                ),
        )

and then use the filter option on the new annotation:

        is_material_available = self.data["is_material_available"]

        if is_material_available == "True":
            order_queryset = order_queryset.filter(is_material_available=True)

        elif is_material_available == "False":
            order_queryset = order_queryset.filter(is_material_available=False)

Using this method result in those behaviors:

• is_material_available=="True": Only fetch orders which have an orders, which is great, but completly disrupts the pagination. Let's say the number of order per page is 8, it will create page with only one order, or more. Also some orders are present in different pages.
• is_material_available=="False": Fetch orders with and without blocks associated to it, but the pagination works fine.

I tried to change the filtering using:

        order_queryset = order_queryset.filter(Exists(Block.objects.filter(order=OuterRef("pk")))

or

        order_queryset = order_queryset.filter(block__isnull=False)

or using this variant:

        order_queryset = Order.objects.all().annotate(
            is_material_available=Count('block', distinct=True)
        )

        order_queryset = order_queryset.filter(is_material_available__gt=0)

but the result is the same. The sorting using the annotation "is_material_available" doesn't work and it sorts randomly the orders, even though the SQL query looks fine.

Do someone know what is happening?

Answer 1

I found out what was wrong. It has nothing to do with django's queryset framework but how the orders and blocks were updated from two different databases. The different methods of filtering works fine.