24

I'm trying to spit out a django page which lists all entries by the year they were created. So, for example:

2010:

  • Note 4

  • Note 5

  • Note 6

2009:

  • Note 1

  • Note 2

  • Note 3

It's proving more difficult than I would have expected. The model from which the data comes is below:

class Note(models.Model):
    business = models.ForeignKey(Business)
    note = models.TextField()
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)
    class Meta:
        db_table = 'client_note'

    @property
    def note_year(self):
        return self.created.strftime('%Y')

    def __unicode__(self):
        return '%s' % self.note

I've tried a few different ways, but seem to run into hurdles down every path. I'm guessing an effective 'group by' method would do the trick (PostGres DB Backend), but I can't seem to find any Django functionality that supports it. I tried getting individual years from the database but I struggled to find a way of filtering datetime fields by just the year value. Finally, I tried adding the note_year @property but because it's derived, I can't filter those values.

Any suggestions for an elegant way to do this? I figure it should be pretty straightforward, but I'm having a heckuva time with it. Any ideas much appreciated.

PlankTon
  • 12,443
  • 16
  • 84
  • 153

3 Answers3

44

Either construct custom SQL or use

date_list = Note.objects.all().dates('created', 'year')

for years in date_list:
    Note.objects.filter(created__year = years.year)

This is the way it is done in date based generic views.

zovision
  • 902
  • 6
  • 6
  • 5
    *laughs* Can't believe it was this easy; spent hours looking for this sort of functionality. Many thanks. – PlankTon Jun 08 '10 at 13:56
3

You can use django.views.generic.date_based.archive_year or use year field lookup.

Davor Lucic
  • 28,970
  • 8
  • 66
  • 76
1

We can improve the filter using a list of years, then check if the year of creation is on that.

With that list, the database will be called once one time, because the Note.objects.filter will runs outside the for loop.

Something like this:

notes = Note.objects.all().dates('created', 'year')
years = [note.year for note in notes]

Note.objects.filter(created__year__in = years)
Mateus Alves de Oliveira
  • 791
  • 6
  • 12
  • 1
    It's the same as this answer (https://stackoverflow.com/a/2997735/2745495), with the for loop turned into a list comprehension. – Gino Mempin Jan 22 '23 at 04:49
  • 1
    While this code snippet may be the solution, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Shawn Hemelstrand Jan 23 '23 at 05:34
  • @ShawnHemelstrand Thanks for the suggestions, I made that. – Mateus Alves de Oliveira Feb 02 '23 at 19:59
  • @GinoMempin You're wrong sir, That could be similar but is different, cause my suggestion calls the database once one time outside the for loop. In answer that you mentioned the for loop calls the database for each iteration. ;) – Mateus Alves de Oliveira Feb 02 '23 at 20:02