Django raises set-returning functions are not allowed in WHERE with annotated value

Question

I need to query orders where the shipping address's postcode is between a certain range. In order to do this, I have to cast the postcode (charfield) to a integer by removing the non numeric values.

I have written this database function for that which works:

class NumericPostcode(models.Func):
    """
    Removes characters from field and converts to integer
    """
    function = "REGEXP_MATCHES"
    template = "CAST( (%(function)s(%(expressions)s, '\d+'))[1] as INTEGER )"
    output_field = models.IntegerField()

Then I do the following query:

orders_qs = Order.objects.annotate(
    numeric_postcode=NumericPostcode(models.F("shipping_address__postcode"))
).filter(self.get_range_query())

The get_range_query is a method that builds a dynamic query:

    def get_range_query(self):
        """
        Build the range query dynamically based on all postcode ranges linked to this transporter
        https://docs.djangoproject.com/en/dev/ref/models/querysets/#range
        """
        query = models.Q()
        for postcode_range in self.postcode_ranges.all():
            query |= models.Q(numeric_postcode__range=(postcode_range.min_postcode, postcode_range.max_postcode))
        return query

However, this query raises the error:

NotSupportedError at /admin/shop/reports/
set-returning functions are not allowed in WHERE

Without the .filter(self.get_range_query()), I can print the annotated field fine and it also works.

The raw SQL query is as follows:

SELECT          "order_order"."id",
                "order_order"."number",
                "order_order"."site_id",
                "order_order"."basket_id",
                "order_order"."user_id",
                "order_order"."billing_address_id",
                "order_order"."currency",
                "order_order"."total_incl_tax",
                "order_order"."total_excl_tax",
                "order_order"."shipping_incl_tax",
                "order_order"."shipping_excl_tax",
                "order_order"."shipping_address_id",
                "order_order"."shipping_method",
                "order_order"."shipping_code",
                "order_order"."status",
                "order_order"."guest_email",
                "order_order"."date_placed",
                "order_order"."delivery_time_text",
                "order_order"."delivery_time_days",
                cast( (Regexp_matches("order_shippingaddress"."postcode", '\d+'))[1] AS INTEGER ) AS "numeric_postcode"
FROM            "order_order"
LEFT OUTER JOIN "order_shippingaddress"
ON              (
                                "order_order"."shipping_address_id" = "order_shippingaddress"."id")
WHERE           (
                                cast( (regexp_matches("order_shippingaddress"."postcode", '\d+'))[1] AS INTEGER ) BETWEEN 1000 AND             1789
                OR              cast( (regexp_matches("order_shippingaddress"."postcode", '\d+'))[1] AS INTEGER ) BETWEEN 1798 AND             2099
                OR              cast( (regexp_matches("order_shippingaddress"."postcode", '\d+'))[1] AS INTEGER ) BETWEEN 3700 AND             3899
                OR              cast( (regexp_matches("order_shippingaddress"."postcode", '\d+'))[1] AS INTEGER ) BETWEEN 7200 AND             8799
                OR              cast( (regexp_matches("order_shippingaddress"."postcode", '\d+'))[1] AS INTEGER ) BETWEEN 8898 AND             8898
                OR              cast( (regexp_matches("order_shippingaddress"."postcode", '\d+'))[1] AS INTEGER ) BETWEEN 9000 AND             9159
                OR              cast( (regexp_matches("order_shippingaddress"."postcode", '\d+'))[1] AS INTEGER ) BETWEEN 9167 AND             9999)
ORDER BY        "order_order"."date_placed" DESC

My knowledge about SQL queries isn't that great & I've spend a couple of hours trying to figure out if I can somehow query this value but with no luck.

If anyone can help me out, that'd be great!

userXs2d1 · Answer

I ended up using "regexp_replace" which works perfect for what I wanted to do. I guess I missed this function when going through the postgres docs.

The solution in my case ended up like this:

    def get_order_qs(self, qs):
        return qs.annotate(
            numeric_postcode=models.functions.Cast(models.Func(
                models.F("shipping_address__postcode"),
                models.Value("[^0-9.]+"),
                models.Value(""), function="regexp_replace"
            ), output_field=models.IntegerField())
        ).filter(self.get_range_query())

Note that this isn't the solution for the error I got, but it is a solution for what I tried to solve.

Django raises set-returning functions are not allowed in WHERE with annotated value

1 Answers