CODEWITHSUNDEEP
mysqldjangodjango-models
world hello
world hello

Reputation: 41

Group By in django 1.8

I have the next structure in my django app:

class A(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=100)
    time = models.DateTimeField(auto_now_add=True)

I want select the earliest time of each name, in database like: 

 ╔═════╤══════╤════════════╗
 ║ id  │ name │    time    ║
 ╠═════╪══════╪════════════╣
 ║  1  │   a  │ 2018-09-10 ║
 ║  2  │   a  │ 2018-09-11 ║
 ║  3  │   b  │ 2018-09-10 ║
 ║  4  │   b  │ 2018-09-11 ║
 ╚═════╧══════╧════════════╝

Then the result should be:

 ╔═════╤══════╤════════════╗
 ║ id  │ name │    time    ║
 ╠═════╪══════╪════════════╣
 ║  1  │  a   │ 2018-09-10 ║
 ║  3  │  b   │ 2018-09-10 ║
 ╚═════╧══════╧════════════╝

I know raw-sql is a solution, but is it possible to use django query expressions? I'm using django 1.8. Any help please.

Upvotes: 1

Views: 100

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476624

We can do this with:

from django.db.models import Min

A.objects.values('name').annotate(
    min_time=Min('time')
).order_by('name')

This will result in a QuerySet object that contains dictionaries: in this case it will look like:

<QuerySet [{'name': 'a', 'min_time': date(2018,09,10)},
           {'name': 'b', 'min_time': date(2018,09,10)}]>

So you can iterate over the queryset, and thus process the dictionary elements.

Upvotes: 2

Related Questions