CODEWITHSUNDEEP
pythondjangoorm
Spoontech
Spoontech

Reputation: 143

Django - Retrieve unique rows by latest date/column

I have the following table:

class AccountData(models.Model):
    id = models.BigIntegerField(primary_key=True)
    date = models.DateField()
    last_update = models.DateTimeField()
    account = models.ForeignKey('Account', models.DO_NOTHING, db_column='account', related_name="values")
    value = models.DecimalField(max_digits=65535, decimal_places=65535, blank=True, null=True)

Given a queryset/list of ID's (unknown amount how many ID's, could be None/0)

Is there a way to get the latest row for each account, then sum the value?

The part I'm really struggling with is only retrieving the latest row per account.

I know I can get the accounts that are in the given queryset by doing:

accounts = [1, 4, 65]
AccountData.objects.filter(account__in=accounts)

Just need to figure out how I can only get the rows where "date" is as close to "now" as possible. Thus resulting in having unique rows (only 1 row per account)

Upvotes: 0

Views: 1244

Answers (1)

Sukhpreet Singh
Sukhpreet Singh

Reputation: 696

Use order by and distinct together to get solution to your problem 

AccountData.objects.order_by('-date').distinct('account_number')

to futher sum the value use aggregate

from django.db.models import Sum  
AccountData.objects.order_by('-date').distinct('account_number').aggregate(sum=Sum('value'))['sum'] or 0

Upvotes: 0

Related Questions