How to group models month by month.

[Yaşar Arabacı]

I have a model with datetime field. I want to get a table with three columns as, year, month and number of items in time span. And I also want to order them from newest to oldest. What I want to get is something like this:

2011 August 4

2011 March 7 

How do you suggest I should do that?

I am trying to add post archives to my front page in my blog. Here is link to same question on StackOverflow: http://stackoverflow.com/q/7320662/886669

--
http://yasar.serveblog.net/

[Andre Terra]

http://django.me/aggregation


Cheers,
AT

2011/9/6 Yaşar Arabacı <yasar...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[Yaşar Arabacı]

I already read that, but I can figure out it is something to do with my question, bu can't figure out what exactly should I do with aggreates. I ended up doing something like this:

    query_set = Post.objects.all()

    years = query_set.dates("pub_date","year")

    date_hierarchy = {}

    for year in years:

        date_hierarchy[year] = {}

        months = query_set.filter(pub_date__year=year.year).dates("pub_date","month")

        for month in months:

            date_hierarchy[year][month] = query_set.filter(pub_date__year=month.year,pub_date__month=month.month).count()

Then in template:

{% for year, month_dict in date_hierarchy.items %}

{% for month,post_count in month_dict.items %}

<li><a href="{% url arsiv_month month.year month.month %}">{{ month|date:"Y E" }} [{{ post_count }}]</a></li>

{% endfor %}

{% endfor %}

2011/9/6 Andre Terra <andre...@gmail.com>

--
http://yasar.serveblog.net/

[Mengu]

this is overkill. the query you actually need is this (there even can
be a better way):

SELECT DATE_FORMAT(pub_date, "%Y %M") as pub_date, COUNT(*) as count
FROM app_posts
GROUP BY pub_date
ORDER BY count DESC

i have no idea how to pull this query by the Django ORM but you can
run this as a raw query like this:

Post.objects.raw("SELECT DATE_FORMAT(pub_date, "%Y %M") as pub_date,
COUNT(*) as count FROM app_posts GROUP BY pub_date ORDER BY count
DESC")

kolay gelsin.

On Sep 6, 5:30 pm, Yaşar Arabacı <yasar11...@gmail.com> wrote:
> I already read that, but I can figure out it is something to do with my
> question, bu can't figure out what exactly should I do with aggreates. I
> ended up doing something like this:
>     query_set = Post.objects.all()
>     years = query_set.dates("pub_date","year")
>     date_hierarchy = {}
>     for year in years:
>         date_hierarchy[year] = {}
>         months =
> query_set.filter(pub_date__year=year.year).dates("pub_date","month")
>         for month in months:
>             date_hierarchy[year][month] =
> query_set.filter(pub_date__year=month.year,pub_date__month=month.month).count()
>
> Then in template:
>
> {% for year, month_dict in date_hierarchy.items %}
> {% for month,post_count in month_dict.items %}
> <li><a href="{% url arsiv_month month.year month.month %}">{{ month|date:"Y
> E" }} [{{ post_count }}]</a></li>
> {% endfor %}
> {% endfor %}
>

> 2011/9/6 Andre Terra <andrete...@gmail.com>
>
>
>
>
>
>
>
>
>
> >http://django.me/aggregation
>
> > Cheers,
> > AT
>
> > 2011/9/6 Yaşar Arabacı <yasar11...@gmail.com>

[Matteius]

I would suggest trying not to break up the date and simply use a
DateField and a separate IntegerField (or BigIntegerField). Of course
you avoid using a DateTimeField since the time would be irrelevant,
and if you think the day is irrelevant think again. Assign a meaning
to the date and your logic can be sound--perhaps the first date of the
month, or the start day then on a second DateField you can have the
end date. Having a start/stop is probably more complex than is
required for what you are trying to do--if you want it by months I
would always just put the 1st of the month down.

# Inside your Post model and in the file you will need:
from django.db import models
class Post(models.Model):

pub_date = models.DateField(_('Start Date'), help_text=_('Date the
month begins for this dataset.'))
count = models.IntegerField()


# Now in your view you can simply use the Django ORM:
Post.objects.all().order_by('-pub_date')
# Or add a month filter
Post.objects.all().filter(pub_date__year=2005).order_by('-pub_date')
Post.objects.all().filter(pub_date__gt=datetime.date(2005, 1,
3)).order_by('-pub_date')
# IF you had a foreign key, get that data also with select_related
Post.objects.all().filter(pub_date__year=2005).select_related('model_name').order_by('-
pub_date')
# You could also order by count
Post.objects.all().filter(pub_date__year=2011).order_by('count')


Hope this helps,
Matteius

On Sep 6, 9:30 am, Yaşar Arabacı <yasar11...@gmail.com> wrote:
> I already read that, but I can figure out it is something to do with my
> question, bu can't figure out what exactly should I do with aggreates. I
> ended up doing something like this:
>     query_set = Post.objects.all()
>     years = query_set.dates("pub_date","year")
>     date_hierarchy = {}
>     for year in years:
>         date_hierarchy[year] = {}
>         months =
> query_set.filter(pub_date__year=year.year).dates("pub_date","month")
>         for month in months:
>             date_hierarchy[year][month] =

> query_set.filter(pub_date__year=month.year,pub_date__month=month.month).cou­nt()

>
> Then in template:
>
> {% for year, month_dict in date_hierarchy.items %}
> {% for month,post_count in month_dict.items %}
> <li><a href="{% url arsiv_month month.year month.month %}">{{ month|date:"Y
> E" }} [{{ post_count }}]</a></li>
> {% endfor %}
> {% endfor %}
>

> 2011/9/6 Andre Terra <andrete...@gmail.com>
>
>
>
>
>
> >http://django.me/aggregation
>
> > Cheers,
> > AT
>
> > 2011/9/6 Yaşar Arabacı <yasar11...@gmail.com>