Django Query Get Last Record with a Group By

Question

I am trying to get all the last records based on the id, sorted by the month.

this gives me the last record,

qs = Cashflow.objects.filter ( acct_id = a.id ).order_by('-month')[:1]

And this groups the accounts,

qs = Cashflow.objects 
     .filter ( acct_id = a.id )
     .values ( 'acct_id' ) 
     .annotate ( count = Count ( 'acct_id' ) ) 
     .values ( 'acct_id', 'count' ) 
     .order_by ( )

How how can I combine the two queries into one?

Group by acct_id, sort by "month" and get last record.

is this even possible? thanks

EDIT:

this is the sql version of what I am trying to do.

    select * 
    from cashflow t
    inner join (
        select acct_id, max ( `month` ) as MaxDate
        from cashflow
          where acct_id  IN ( 1,2,3,... )
        group by acct_id
    ) tm on t.acct_id = tm.acct_id and t.month = tm.MaxDate
    order  by acct_id

Can this be done in pure Django of should I just do a Raw query?

cheers.

score 0 · Answer 1 · answered Feb 05 '19 at 20:12

0

Best solution I found online https://gist.github.com/ryanpitts/1304725

'''
given a Model with:

   category    = models.CharField(max_length=32, choices=CATEGORY_CHOICES)
   pubdate     = models.DateTimeField(default=datetime.now)
   <other fields>

Fetch the item from each category with the latest pubdate.

''' 

model_max_set = Model.objects.values('category').annotate(max_pubdate=Max('pubdate')).order_by()

q_statement = Q()
for pair in model_max_set:
    q_statement |= (Q(category__exact=pair['category']) & Q(pubdate=pair['max_pubdate']))

model_set = Model.objects.filter(q_statement)

answered Feb 05 '19 at 20:12

nael

1,441
19
36

Sorry, more efficient solution for this is using the Window SQL function or even the `distinct` statement for postgres https://stackoverflow.com/a/51142690/5157209 – Anton Shurashov Feb 05 '19 at 21:20
I understand. The user was asking about using the Django ORM though. – nael Feb 05 '19 at 21:21
I don't think we should be giving -1 for correct answers. Not the most efficient I agree. But if we put -1 for each non-efficient answer we won't have answers for a lot of questions. But I respect your answer as well. – nael Feb 05 '19 at 21:23

Anton Shurashov · Answer 2 · 2017-08-20T20:18:00.653

-1

Instead .order_by('-month')[:1] it's better to use .order_by('month').last() or .order_by('-month').first() (or earliest/latest for dates).

Of course when grouping you can use order_by:

last_record = Cashflow.objects \
    .values('acct_id') \
    .annotate(count=Count('id')) \
    .order_by('month') \
    .last()

edited Aug 20 '17 at 20:18

answered Aug 20 '17 at 13:09

Anton Shurashov

1,820
1
26
39

Usually it's better to explain a solution instead of just posting some rows of anonymous code. You can read [How do I write a good answer](https://stackoverflow.com/help/how-to-answer), and also [Explaining entirely code-based answers](https://meta.stackexchange.com/questions/114762/explaining-entirely-%E2%80%8C%E2%80%8Bcode-based-answers). – Massimiliano Kraus Aug 20 '17 at 19:59
@MassimilianoKraus thanks for the remark, I added an explanation. – Anton Shurashov Aug 20 '17 at 20:20
Much better. +1 for the effort. – Massimiliano Kraus Aug 20 '17 at 20:20
1

That only gives me one record. – diogenes Aug 24 '17 at 03:38
In the question you asked about a last record. If you want many last records don't use `last()` and change ordering to descending. – Anton Shurashov Aug 24 '17 at 07:25
The issue here though, that you can't get the last for each `acct_id`. It will only return the last record in the queryset. – nael Feb 05 '19 at 20:09
@nmusleh oh, it's possible also, see my comment below your answer – Anton Shurashov Feb 05 '19 at 21:22
1

@AntonShurashov your answer is wrong though. It doesn't return all the acct_id only the last one. – nael Feb 05 '19 at 21:25

Django Query Get Last Record with a Group By

2 Answers2