2

So my model is like this:

class Blog(models.Model):
    title = models.CharField(max_length=100)
    publication_date = models.DateField()

And now I want to get the count of the blog posts by each month. The raw sql would look like:

SELECT COUNT (*), EXTRACT(MONTH FROM publication_date) AS month FROM blog GROUP BY month;

One solution I found is here, it suggests that getting a date list first then loop through it using filter for each iteration. I do not like it, I am looking for a way to get the result without using loop.

Community
  • 1
  • 1
tmaster
  • 9,345
  • 6
  • 24
  • 27
  • Querying by a range of dates should work, like this: http://stackoverflow.com/questions/4668619/django-database-query-how-to-filter-objects-by-date-range – dbr Nov 22 '12 at 10:46
  • The linked answer does not give me what I want. But thanks for the link anyway. – tmaster Nov 22 '12 at 10:50
  • You want one blog per month ? – jpic Nov 22 '12 at 10:51
  • I want to get the posts count for each month. – tmaster Nov 22 '12 at 10:52
  • Don't have Django handy, so OTTOMH: `Blog.objects.values('publication_date__month').order_by().annotate(Count('publication_date__month'))` – Jon Clements Nov 22 '12 at 10:58
  • 1
    `Blog.objects.filter(publication_date__month = 4)` works, but `Blog.objects.values('publication_date__month')` does not. Error message: FieldError: Cannot resolve keyword 'publication_date__month' into field – tmaster Nov 22 '12 at 11:21

1 Answers1

1

You could use something to the effect of Blog.objects.filter(publication_date__range=[start_of_month, end_of_month]) to get all items from between those two dates. See range for details.

colons
  • 269
  • 1
  • 8
  • But I do not want a range, I am looking for all the data in the database. The range only give me all the records between the dates, then I still have to use python to sort out the dates myself. – tmaster Nov 22 '12 at 10:49
  • You may have to look into [extra()](https://docs.djangoproject.com/en/dev/ref/models/querysets/#extra) if you're looking to do things the QuerySet API cannot natively handle. Especially if you already know what your SQL should look like. – colons Nov 22 '12 at 10:53
  • Ye, after all the searching, I guess I will go with either using `extra()` or just raw sql. Thanks. – tmaster Nov 22 '12 at 10:57