Django Query and Grouping

Question

I'm creating an app where people will be able to make posts for cities. I'd like to create a view for the country, which would show a list of cities that have posters, and a count of how many posters have been made for each city.

I have a Country table, and a City table with a foreign key for a country. I also have a Poster table with a foreign key to the City table.

This successfully gives me a list of posters, but how do I group them into Cities and pass that? In the template I'd like to be able to show the City Name, poster.image that are in that city, and a total count of posters in that city, for every city in the country that posters have been made for.

def country_page(request, country_name):
    country = get_object_or_404(Country, name__iexact=country_name)    
    posters = Poster.objects.filter(city__country=country)        
    variables = RequestContext(request, {
        'location' : country,
        'posters' : posters,
    })
    return render_to_response('country_page.html', variables)

Any help would be appreciated.

Vannen · Answer 1 · 2012-02-03T08:54:09.663

0

In raw sql, you would have done it using "group by" and such, but for django, you should look into the aggregation functions for models.

https://docs.djangoproject.com/en/dev/topics/db/aggregation/

I ran into a similar problem some days ago, and this how I have solved it. You first filter the objects from a specific criterion, then specify the fields you want, and finally do a count, to know the total number of rows.

Your solution might look something like that:

posters = Poster.objects.filter(city__country=country).values('city_name','poster_image',..).annotate(total=Count('city'))

edited Feb 03 '12 at 08:54

answered Feb 03 '12 at 08:37

Vannen

712
2
7
17

Shouldn't you take a count from cities? – Raekkeri Feb 03 '12 at 08:40
Oh yeah, sorry. I took this code from my own project, and I have wrongly changed the parameters. – Vannen Feb 03 '12 at 08:53

score 0 · Answer 2 · answered Feb 03 '12 at 08:42

This could also be achieved in code rather than all SQL, but taking into account the nature of your app -- that there might be lots of posters in lots of cities -- this solution could easily take up too much system resources. But here's a quick snippet:

key = lambda poster: poster.city
posters = Poster.objects.filter(city__country=country)
posters = sorted(posters, key=key)
di = {}
for k, g in itertools.groupby(posters, key):
  di[k] = list(g)

Now di contains each city as keys, with a list of all posters as values.

score 0 · Accepted Answer · edited May 23 '17 at 12:20

What you're looking for is a 'group by' in sql land.

The below thread has some interesting insights, bottom line is you need raw sql to do a true group by, but I suspect for your needs you can just do a little post processing to do the grouping:

posters = Poster.objects.filter(city__country=country)
poster_cities = {}
for poster in posters:
    if city not in poster_cities:
        poster_cities[city] = []
    poster_cities[city].append(poster)

Now you can get the posters by city like this to use from the template:

for city in sorted(poster_cities.keys()):
    city_posters = poster_cities[city]
    city_poster_count = len(poster_cities[city])

background:

How to query as GROUP BY in django?

score 0 · Answer 4 · answered Feb 03 '12 at 09:07

This returns Cities, each with the number of Posters for that city:

cities = City.objects.filter(country=my_country).annotate(num_posters=Count('posters'))

Then you can loop in the template:

<h1>{{ country }}</h1>
{% for city in cities %}
<p>
    {{ city.name }} - {{ city.num_posters }} posters
    {% for poster in city.posters.all %}
        <img src="{{ poster.image.url }}">
    {% endfor %} 
</p>
{% endfor %}

Now, the above code fetches Posters separately for each City, which might be not efficient. To fix that, you can either use prefetch_related (if you can afford using Django trunk), or do it manually yourself, like here

score 0 · Answer 5 · answered Feb 03 '12 at 09:29

With the code you already have you could use regroup in the template, something like:

{% regroup posters by city as city_list %}

<ul>
{% for city in city_list %}
    <li>{{ city.grouper }} ({{city.list|length}})
    <ul>
        {% for poster in city.list %}
        <li>{{ poster.image}}</li>
        {% endfor %}
    </ul>
    </li>
{% endfor %}
</ul>

note you would need to order the posters query by city in your view.

Django Query and Grouping

5 Answers5