How to group by events by year and month in django ORM?

Question

I tried multiple approach, but could not come to solution.

I have event model like this:

class Event(models.Model):
    start_date = models.DateTimeField()
    end_date = models.DateTimeField()
    location = models.CharField(max_length=250)

I want to group events by year and then months:

2013

Jan

Event 1
Event 2

Feb

Event 1
Event 2

2014

Jan

Event 1
Event 2

I am only taking start_date while doing sorting.

At last I was thinking this would work, but it doesn't:

Event.objects.values_list('start_date__year').annotate(dcount=Count('start_date__year'))

Answered your question. I hope it was enough to solve your problem. — Games Brainiac, Jul 18 '13 at 20:54

Games Brainiac · Accepted Answer · 2013-07-18T21:09:22.893

2

I can show you how to group them separately. This is how I would go about it, but please note that my knowledge of django is not the best in the world.

Event.objects.filter(end_date__year=2013)

This way you will get all the events in 2013.

You can further filter them to get the month.

Event.objects.filter(end_date__year=2013).filter(end_date__month='01')

This would get you all the dates in February. You could simply use a ModelManager to manage these custom queries, since you will be doing them a lot, and then you can just call them in your views.

You can make a ModelManager like so:

class EventManager(models.Manager):

    def get_by_year(self, year):
        Event.objects.filter(date__year=year)

And you can simply call it by, Event.objects.get_by_year(2013). You can do this after you change your objects property in your Model, like so:

class Event(models.Model):
    start_date = models.DateTimeField()
    end_date = models.DateTimeField()
    location = models.CharField(max_length=250)
    objects = EventManager

edited Jul 18 '13 at 21:09

answered Jul 18 '13 at 20:48

Games Brainiac

80,178
33
141
199

Ah thanks. Well, I am aware of that. It gets a bit messy and becomes long to go that way. Like, I will have to do same for all the months etc. Will be probably last resort. – chhantyal Jul 18 '13 at 20:53
@neokya: Actually it wont, just abstract that away in a `ModelManager`, and that will make like a lot easier. – Games Brainiac Jul 18 '13 at 20:56
I actually wanted grouped output in single query itself. Looks that's not really ideal thing to do. I will query and then group by year and month. I think above answer is way to go in django. So, I am accepting it. – chhantyal Jul 19 '13 at 12:18
Also, please Event.objects.filter to just self.get_query_set() – chhantyal Jul 19 '13 at 21:06

score 1 · Answer 2 · edited May 23 '17 at 12:29

1

You can use python's groupby() function after you have queried the data.

Here is an example. Instead of date() you would use month instead. Django - Group By with Date part alone

edited May 23 '17 at 12:29

Community

1
1

answered Jul 18 '13 at 21:14

nickromano

918
8
16

How to group by events by year and month in django ORM?

2013

2014

2 Answers2