85552

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

Question:

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.

Answer1:

<strong>models.py</strong>

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',)

<strong>views.py</strong>

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

<strong>template</strong>

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

Answer2:

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.

<strong>What is it doing?</strong>

<ol><li>Queryset to get all items</li> <li>Grouping the items by their group</li> <li>Picking the first items from each group, and putting them into a list.</li> </ol>

Recommend

  • Haystack with Whoosh- Search Results Not Redirecting
  • Django “can't adapt type” error using mutliple databases (Postgresql 8.4 and sqlite3)
  • Can order by work with properties from model?
  • Python assertRaises on user-defined exceptions
  • What part of this Solr-Sunspot setup am I missing?
  • Django error 'unicode' object has no attribute 'objects'
  • NoReverseMatch at / Reverse error with urlresolvers, get_absolute_url()
  • Updating indexes on placeholderfields in real time with django/haystack/solr
  • django - can't see the uploaded media files from apache
  • Django pagination with next, previous, count
  • Django Celery - How to start a task with a delay of n - seconds - countdown flag is ignored
  • SQL duplicate rows with multiple left joins
  • JPA - getting distinct value from one column
  • How to translate SQL queries to cypher in the optimal way?
  • Add delivery info to query in SAP Crystal Reports
  • Taking mean across rows grouped by a variable in numpy
  • Grouping by blank nodes
  • Query to get the Top 2 from each group
  • Django: ORDER BY DESC on FloatField puts null values top?
  • How to implement limit with Nhibernate and Sybase
  • Single django queryset to get n adjacent items
  • Django model for a Postgres view
  • Django foreign key drop down
  • Grails calculated field in SQL
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • Debug.DrawLine not showing in the GameView
  • CSS Linear-gradient formatting issue accross different browsers
  • How to add date and time under each post in guestbook in google app engine
  • JSON with duplicate key names losing information when parsed
  • Return words with double consecutive letters
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • Django query for large number of relationships
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • Python/Django TangoWithDjango Models and Databases
  • How to push additional view controllers onto NavigationController but keep the TabBar?
  • How to load view controller without button in storyboard?