How To Get Latest Unique Entries From Sqlite Db With The Counter Of Entries Via Django Orm

I have a SQLite db which looks like this: |ID|DateTime|Lang|Details| |1 |16 Oct | GB | GB1 | |2 |15 Oct | GB | GB2 | |3 |17 Oct | ES | ES1 | |4 |13 Oct | ES | ES2 | |5

Solution 1:

You can use Django annotate() and value() together: link.

when a values() clause is used to constrain the columns that are returned in the result set, the method for evaluating annotations is slightly different. Instead of returning an annotated result for each result in the original QuerySet, the original results are grouped according to the unique combinations of the fields specified in the values() clause. An annotation is then provided for each unique group; the annotation is computed over all members of the group.

Your ORM query should looks like this:

queryset = Model.objects.values("Lang").annotate(
    max_datetime=Max("DateTime"),
    count=Count("ID")
).values(
    "ID", "max_datetime", "Lang", "Details", "count"
)

Solution 2:

As you want distinct entries by "Lang" and latest entries by "DateTime", below query will help you,

queryset = Model.objects.distinct("Lang").order_by("-DateTime")