CODEWITHSUNDEEP
python-3.xdjangodjango-modelsorm
Thibault Bacqueyrisses
Thibault Bacqueyrisses

Reputation: 2331

Get last record by date for multiple table with many-to-one relationship

I have 3 tables :

class Item(models.Model):
    def __str__(self):
        return self.name

    name = models.CharField(max_length=200, unique=True)
    image = models.URLField()

class Prix_x1(models.Model):
    def __str__(self):
        return self.item.name

    prix = models.IntegerField(null=True)
    saved_at = models.DateTimeField()
    item = models.ForeignKey(Item, on_delete=models.CASCADE)

class Prix_x10(models.Model):
    def __str__(self):
        return self.item.name

    prix = models.IntegerField(null=True)
    saved_at = models.DateTimeField()
    item = models.ForeignKey(Item, on_delete=models.CASCADE)

What i want is to retrive the last price by 1 and by 10 for each Item in my BDD.

I have already tried many things, but either it's not gives me what i want or either the request is like 20seconds

I want to show the result in a table like :

results table

Thanks for your help !

Upvotes: 1

Views: 462

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477533

You can work with subqueries:

from django.db.models import OuterRef, Subquery

Item.objects.annotate(
    prix_x1=Subquery(
        Prix_x1.objects.filter(
            item=OuterRef('pk')
        ).values('prix').order_by('-saved_at')[:1]
    ),
    prix_x10=Subquery(
        Prix_x10.objects.filter(
            item=OuterRef('pk')
        ).values('prix').order_by('-saved_at')[:1]
    )
)

The Items that arise from this queryset will have two extra attribute .prix_x1 and .prix_x10 that contain the prix for the last Prix_x1 and Prix_x10 records related to that item.

Upvotes: 1

Related Questions