Combining Count and queryset.datetimes()

Using Django
1

Hi everyone :wave:,

I’ve been trying to combine a Count annotation with queryset.datetimes() but I can’t seem to make it work and my searches have come up empty.

I have an Article model with a published_on field (DateTimeField). Using Article.objects.datetimes('published_on', 'month') I can get a list of all the months (as 'date` objects) in which I’ve published an article, great.
But what I’d like to know is how many articles I’ve published in each month.

I tried Article.objects.datetimes('published_on', 'month').annotate(Count('pk')) but that doesn’t seem to have any effect: I get the exact same result as if I hadn’t annotated the queryset. Switching 'pk' to anything else doesn’t seem to change anything either.

I can work around the problem using ExtractYear and ExtractMonth but it takes a bit more code:

Article.objects.values(y=ExtractYear('published_on'), m=ExtractMonth('published_on')).annotate(c=Count('pk'))

(and then I have to construct the date objects myself out of the year and month integers).

Is there a way to do what I want using datetimes() so I can have date objects created automatically?

Thanks! :sparkles:

2

As is quite typical of me, I ended up finding the answer just after posting, oh well.

The trick is to use TruncMonth instead of ExtractYear and ExtractMonth:

Article.objects.values(m=TruncMonth('published_on')).annotate(c=Count('pk'))

I’ll leave the post up in case someone finds themselves with the same question as me.
Thanks for letting me use this space as a rubber-duck area :slight_smile:

2 Likes
3

Rubber ducks always welcome! :duck:

1 Like