CODEWITHSUNDEEP
djangodjango-queryset
manuel
manuel

Reputation: 847

How can I select one object per each related object in django?

If I have two classes:

class Group(models.Model):
    name = models.CharField(...)

class Item(models.Model):
    group = models.ForeignKey(Group)
    published = models.DateTimeField(auto_now_add=True)

How can I make a QuerySet to select the latest published Item from each Group? I guess it should be something like

Item.objects.order_by('published').distinct('group')

but I can't make it work.

Upvotes: 0

Views: 1633

Answers (2)

Humphrey
Humphrey

Reputation: 4208

If you don't mind loading ALL of the Item's into memory from the database, and you want to do it in a single query, then you can do this. In my scenerio, there are about 8 "Items" and about 4 "Groups" so it's barely a performance hit to do this.

from itertools import groupby

items = [list(g)[0] for k, g in groupby(Item.objects.all().order_by('group', '-published'), lambda x: x.group_id)]

Add .select_related('group') to the inner queryset if you want to be able to access the group object.

What is it doing?

  1. Queryset to get all items
  2. Grouping the items by their group
  3. Picking the first items from each group, and putting them into a list.

Upvotes: 0

catherine
catherine

Reputation: 22808

models.py

class Group(models.Model):
    name = models.CharField(max_length=100)

    def __unicode__(self):
        return self.name

    def latest_published(self):
        items = Item.objects.filter(group=self)[:1]
        for item in items:
            return item.published
        return ''

class Item(models.Model):
    group = models.ForeignKey(Group)
    published = models.DateTimeField()

    def __unicode__(self):
        return "{0}".format(self.published)

    class Meta:
        ordering = ('-published',)

views.py 

def myview(request):
    groups = Group.objects.filter()

    [.........]

template

{% for group in groups %}
    {{group}} - {{group.latest_published}}<br/>
{% endfor %}

Upvotes: 2

Related Questions