Django fetch related child with parent object

Question

I am using Django 2.2 and I have a model with two classes Product and ProductRevision. When I retrieve a Product, or a list of Products, I always fetch the corresponding ProductRevision. ProductRevision objects are incremented and only the last revision should be fetched with the Product.

class Product(models.Model):
    name = models.CharField(max_length=50, null=False, blank=False,
                            verbose_name=_("name"))
    product_code = models.CharField(max_length=10, null=False, blank=False,
                                    verbose_name=_("product code"))
    slug = models.SlugField(null=False, unique=True)

    @property
    def current_item(self):
        return ProductRevision.objects.filter(product=self, active=True).order_by('-version').first()


class ProductRevision(models.Model):
    product = models.ForeignKey(Product, null=True, on_delete=models.PROTECT)
    version = models.IntegerField(default=0,
                                  verbose_name=_("version"))
    active = models.BooleanField(default=False, null=False, blank=True,
                                 verbose_name=_("is active"))
    qty_close = models.IntegerField(default=0,
                                    verbose_name=_("qty of accounts to be closed"))
    price_eur = models.DecimalField(max_digits=6, decimal_places=2, default=0,
                                    verbose_name=_("price in EUR"))

I tried to add a property current_item to get the last revision of a given product. While this is working, it is very inefficient because when I use it in a template, it hits the database every time I display a field from the related ProductRevision.

I found this answer based on an old version of Django (Fetch latest related objects in django) and I'm wondering if there is no other ways to achieve the same result with current versions of Django? I'm particularly interrested in achieving this in my models.

Are you using postgres?

Iain Shelvington
– Iain Shelvington

2019-12-22 22:42:37 +00:00
Commented Dec 22, 2019 at 22:42 — Iain Shelvington
– Iain Shelvington, Commented Dec 22, 2019 at 22:42
Yes, postgres 11.6

cgaspoz
– cgaspoz

2019-12-22 22:45:49 +00:00
Commented Dec 22, 2019 at 22:45 — cgaspoz
– cgaspoz, Commented Dec 22, 2019 at 22:45

Iain Shelvington · Accepted Answer · 2019-12-22 22:58:22Z

3

I have managed to achieve something similar to this by using a custom prefetch related queryset

products = Product.objects.prefetch_related(Prefetch(
    'productrevision_set',
    queryset=ProductRevision.objects.order_by('product', '-version').distinct('product'),
    to_attr='latest_revision'
))

The queryset above will only return one ProductRevision per Product which in effect gives us the latest ProductRevision for all Products in only 2 queries

for product in products:
    for latest_revision in product.latest_revision:
        print(product, latest_revision)

answered Dec 22, 2019 at 22:58

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cgaspoz Over a year ago

Yeah, this was the way to go! You should replace product with product_id in the queryset: queryset=ProductRevision.objects.order_by('product_id', '-version').distinct('product') otherwise you get the SELECT DISTINCT ON expressions must match initial ORDER BY expressions error (see stackoverflow.com/questions/20582966/… for more information)

Collectives™ on Stack Overflow

Django fetch related child with parent object

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related