Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am working on a package sorting system in Django. I need to look up the "sort code" of a set of "barcodes" This code works:

class Order(models.Model):
    Zip = CharField(max_length=128, null=True, blank=True)
class Barcode(models.Model):
    barcode = CharField(max_length=50, unique=True)
    Order = ForeignKey(Order, on_delete=models.SET_NULL)
class SortCode(models.Model):
    name = CharField(max_length=50, unique=True)
class SortZip(models.Model):
    zipcode = CharField(max_length=5, unique=True)
    sortcode = ForeignKey('SortCode', null=True, default=None, blank=True, on_delete=models.PROTECT)
sortzip = SortZip.objects.filter(zipcode=OuterRef('Order__Zip'))
barcodes = Barcode.objects.annotate(sortcode_value=Subquery(sortzip.values('sortcode__name')))
However, SortZip.zipcode only stores 5-digit zip codes, and Order.Zip sometimes contains zip+4, so I need to look up the SortZip from only the first 5 digits of Order.Zip:
sortzip = SortZip.objects.filter(zipcode=OuterRef('Order__Zip')[:5])
barcodes = Barcode.objects.annotate(sortcode_value=Subquery(sortzip.values('sortcode__name')))
This causes the following error:
TypeError: 'OuterRef' object is not subscriptable
I have tried adding the following property to the Order model and using the property:
class Order(models.Model):
    Zip = CharField(max_length=128, null=True, blank=True)
    @property
    def Zip5(self):
       return self.Zip[:5]
sortzip = SortZip.objects.filter(zipcode=OuterRef('Order__Zip5'))
barcodes = Barcode.objects.annotate(sortcode_value=Subquery(sortzip.values('sortcode__name')))
However, this gives a different error:
django.core.exceptions.FieldError: Unsupported lookup 'Zip5' for BigAutoField or join on the field not permitted.
The correct way of truncate string is using Left function.
from django.db.models.functions import Left
sortzip = SortZip.objects.filter(zipcode=OuterRef(Left('order__zip', 5)))
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.