How to use Django ORM to get a list by year of all articles with an article score

Question

I am trying to use ORM django to get a list of years of all my articles with an article counter next to it, for example:

2010 (5 entries)
2009 (4 entries)
2008 (9 entries)

I tried things like:

archive = Articles.objects.dates ('created', 'year'). annotate (archive_count = Count ('created'))

or

archive = Articles.objects.values ​​('created'). annotate (archive_count = Count ('created'))

or

archive = Articles.objects.values ​​('created'). aggregate (archive_count = Count ('created'))

The latter gave me the correct calculation, but did not give me any values ​​of the year, others give a combination of neither anything, nor the value of archive_count equal to 1 for each row.

Any ideas I'm wrong about?

Answer 1

Here is a way to do this in one request:

Article.objects.extra(select={'year':"strftime('%%Y',created)"}).values('year').order_by().annotate(Count('id')) 

Note that you will need to replace strftime('%%Y',created) according to your database (I used sqlite).

Answer 2

I'm not sure that keeping the database to a minimum is your main goal. If so, there may be another way that I have not considered. But at first glance it looks the way you want it:

 archive={} years = Article.objects.dates('created', 'year') for year in years: archive[year.year] = Article.objects.filter(created__year=year.year).count() 

Then you will have a dictionary with {2010: 5, 2009: 4, 2008: 9}.

Answer 3

In an ideal world, you can write:

 archive = Articles.objects.values('created__year').annotate(archive_count=Count('created')).order_by() 

to get the desired results. Unfortunately, django does not support anything other than exact field names as argument values ​​()

 archive = Articles.objects.values('created').aggregate(archive_count=Count('created')) 

may not work because the values ​​("created") give you a dictionary of all unique values ​​for "created", which consists of more than a "year".

If you really want to do this with a single ORM call, you will have to use an extra function and write your own custom SQL. Otherwise, Justin's answer should work well.