Django Orm - Get Latest Record for Group

Django orm get latest for each group

This should work on Django 1.2+ and MySQL:

Score.objects.annotate(
  max_date=Max('student__score__date')
).filter(
  date=F('max_date')
)

Django ORM - Get latest record for group

Put your values clause before the annotate.

From the aggregation docs:

If the values() clause precedes the annotate(), the annotation will be computed using the grouping described by the values() clause.

However, if the annotate() clause precedes the values() clause, the annotations will be generated over the entire query set. In this case, the values() clause only constrains the fields that are generated on output.

So this should do it:

Meetup.objects.values('language').annotate(latest_date=Max('date'))

Django: Get latest record before certain date for all given users

If your database backend is PostgreSQL, Django supports specifying fields in .distinct

UserData.objects.filter(user_id__in=user_list, date__created__lte=start_date)\
    .order_by('user_id', '-date').distinct('user_id')

If it is not, please refer to @Patrick H.

How to get latest n record for per group in Django Orm

The first thing to try in this situation is a select_related to fetch the phone_number for each dude.

dudes = Dude.objects.select_related('phone_number', 'phone_number__business').all()
for dude in dudes:
    do_the_thing()  

(note the select_related on the other object you're querying too, phone_number.business)

In this case, if you have lots of phone_numbers per Dude, then this might perform worse than your original query, as it will grab every Dude.phone_number.

Unfortunately, as the comments have suggested, there's no ORM way to limit the select_related. You'll need to write some SQL. You can get a head-start by observing what SQL Django generated for the select_related query by turning up DB kogging, and then running your own custom SQL query.

How can I get latest record by queryset. like group by

Yes, It is possible. You can make use of distinct and order_by together in your django orm query to get your wish_data. The query would be something like this:

distict_avengers = Model.objects.filter(**conditions).distinct("name").order_by("name", "-create_date").values("name", "data1", "data2", "create_date")

If you will print(distict_avengers) then you will see the following result:

[
  {"name": "captain", "data1": "ce", "data2": "cf", "create_date": 1630590000},
  {"name": "hulk", "data1": "hc", "data2": "hd", "create_date": 1630500000},
  {"name": "iron man", "data1": "ia", "data2": "ib", "create_date": 1630000000},
]

Please note that using distinct on fields is not supported on SQLite and also when using distinct and order_by together the fields mentioned in the order_by must always begin with the fields mentioned in the distinct.

Select latest record in the group with ordering

Option 1: Order on the Django/Python layer

The items are first sorted by user_id, and only in case of a tie, it takes the one with the latest date. But that means that you eventually get for each user a Chats object, ordered by the user_id.

I think here your only option is to sort it at the Django/Python level, so wrap it into a list, and sort by the date:

from operator import attrgetter
items = list(Chats.objects.order_by('user_id', '-date').distinct('user_id'))
items.sort(key=attrgetter('date'), reverse=True)
# work with items

and then render the items in the template.

---

Option 2: Annotate the User model instead

Another option is to annotate the User model and thus work with a QuerySet of User objects:

from django.db.models import Max, OuterRef, Subquery
User.objects.filter(
    chats__isnull=False
).annotate(
    last_date=Max('chats__date'),
    last_message=Subquery(
        Chat.objects.filter(user_id=OuterRef('pk')).order_by('-date').value('chat')[:1]
    )
).order_by('-last_date')

Here the User objects will have an extra attribute .last_date with the latest date time of the object, and .last_message with that message.

---

Note: It is normally better to make use of the settings.AUTH_USER_MODEL [Django-doc] to refer to the user model, than to use the User model [Django-doc] directly. For more information you can see the referencing the User model section of the documentation.

Django ORM: Get latest record for distinct field

This is somewhat untested, and relies on Django 1.11 for Subqueries, but perhaps something like:

latest_visits = Subquery(ShopVisit.objects.filter(id=OuterRef('id')).order_by('-date_in_shop').values('id')[:1])

ShopVisit.objects.filter(id__in=latest_visits)

I had a similar model, so went to test it but got an error of:
"This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery"

The SQL it generated looked reasonably like what you want, so I think the idea is sound. If you use PostGres, perhaps it has support for that type of subquery.

Here's the SQL it produced (trimmed up a bit and replaced actual names with fake ones):

SELECT `mymodel_activity`.* FROM `mymodel_activity` WHERE `mymodel_activity`.`id` IN (SELECT U0.`id` FROM `mymodel_activity` U0 WHERE U0.`id` = (`mymodel_activity`.`id`) ORDER BY U0.`date_in_shop` DESC LIMIT 1)

---

Related Topics