Group by FK field

Question

I have the following models:

class Website(models.Model):
    name = models.CharField(max_length=256, unique=True)

class Product(models.Model):
    website = models.ForeignKey('Website')
    name = models.CharField(max_length=512)

I'd like to run a query to get a list of lists of products grouped by their websites name.

An example:

Website 1 : name: 'ebay' Website 2: name: 'amazon'

Product 1 : name: 'pc' website: 'ebay' Product 2 : name: 'notebook' website: 'amazon' Product 3 : name: 'smartphone' website: 'amazon'

I'd like to get something like: [[Product1], [Product2, Product3]]

I've been looking here and in the django docs but I couldn't find a proper way to do it.

Possible duplicate: http://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django — skoll, Jan 14 '14 at 15:40
If you just want to apply this logic in your template you can always regroup things: https://docs.djangoproject.com/en/1.6/ref/templates/builtins/#regroup — petkostas, Jan 14 '14 at 15:45

Anentropic · Accepted Answer · 2014-01-14T15:51:13.770

You can do this in two steps...

First get a queryset ordered how you want, but essentially a 'flat' list:

qs = Product.objects.order_by('website__name')

Then you could use something like itertools.groupby to output the sub-lists:

from itertools import groupby
from operator import attrgetter

keyfunc = attrgetter('website')
qs = Product.objects.order_by('website__name')
groups = []
uniquekeys = []
for website_name, products in groupby(qs, keyfunc):
    groups.append(list(products))
    uniquekeys.append(website_name)

Alternatively you could just pass the queryset into the template and use the regroup templatetag

{{% regroup qs by website as product_list %}

<ul>
{% for group in product_list %}
    <li>{{ group.grouper.name }}
    <ul>
        {% for product in group.list %}
          <li>{{ product.name }}: {{ product.website }}</li>
        {% endfor %}
    </ul>
    </li>
{% endfor %}
</ul>

Group by FK field

1 Answers1