Django query to return distinct list + count + newest entry

Question

I am having trouble writing a Django query that will return a list of markets from the Post table, as well as a post_count for that specific market, and the datetime of the most recent post.

Query:

Post.objects.values('market').annotate(post_count=Count('pk')).annotate(recent_post=Max('id')).order_by('market')

Results:

<QuerySet [{'market': 29, 'post_count': 2, 'recent_post': 6}, {'market': 150, 'post_count': 1, 'recent_post': 5}]>

This is relatively close, but I would ultimately like to return the market and recent post as an object, instead of an id.

Any help would be amazing! TIA

question from:https://stackoverflow.com/questions/65911492/django-query-to-return-distinct-list-count-newest-entry

Answer 1

Waitting for answers