Query for items using filter for most recent entry in a related table

Hello I seem be having a problem with querying. I have a list of items. Any Item can have a status set to it (In, out, Collected, Destroyed, etc.). Here is my views.

def client_summary(request, client_id):
    client = None
    items = None
try:
    client = models.Client.objects.get(pk = client_id)
    items = client.storageitem_set.all()
    total_items = items.count()
    except:
        return HttpResponse(reverse(return_clients))
    return render_to_response('client_summary.html', {'items':items, 'total_items':total_items, 'client':client}, context_instance = RequestContext(request))

If I have in my template

{%for item in items%}
        {{item.itemstatushistory_set.latest}}
{%endfor%}

This will display the all the latest status. Now I want to only to print out 开发者_如何学JAVAall items that their status is Destroyed only. For some reason I can't seem to do this.

Here is some more information from my models as well.

class StorageItem(models.Model):
    type = models.ForeignKey(StorageObject)
    client = models.ForeignKey(Client)
    company_id = models.PositiveIntegerField(unique = True, blank = True, null = True)
    content = models.TextField(blank = True)
    alternative_id = models.CharField(verbose_name = 'Client no.', max_length = 60, blank = True)
    title = models.CharField(max_length = 100)
    format = models.ForeignKey(Format, blank = True, null = True)
    location = models.CharField(max_length = 20, blank = True)
    item_class = models.TextField(blank = True)

    def __unicode__(self):
        return self.title

class Status(models.Model):
    description = models.CharField(max_length = 60)
    notes = models.TextField(blank = True)
    def __unicode__(self):
        return self.description

    class Meta:
        verbose_name_plural = 'Status'
        get_latest_by = 'date'
        ordering = ['date']

class ItemStatusHistory(models.Model):
    date = models.DateTimeField(auto_now = True)
    contact = models.ForeignKey(Contact)
    item = models.ForeignKey(StorageItem)
    status = models.ForeignKey(Status)
    user = models.ForeignKey(User)

    def __unicode__(self):
        return str(self.status

EDIT: There are still some problems because the relation between an item could have many statuses. But I want to only list the most recent status only for destroyed items.

Example: Supposing there are 3 items and they have sets item1 = [in, out, destroyed], item2 = [destroyed, in], item3 = [destroyed, collected, destroyed], item4 = [in] where [1st status, 2nd status, 3rd status, etc]. I only want to display the latest status for that item.

Both Mike and kriegar will get a result like [item1, item2, item3, item3]. Because Yuji used the distinct function, he will get [item1, item2, item3].

The answer I need to get at the end should be [item1, item3].

kriegar's solution will work. There's also this one, which searches by Status id instead of text matching on description:

destroyedStatus = Status.objects.get(description="destroyed")
clients_destroyed_items = StorageItem.objects.filter(client=client, 
    itemstatushistory__status=destroyedStatus)

This assumes descriptions are unique, but you have no such constraint in your model. I have no idea which implementation is faster.

EDIT: By the way, if you've got some crazy system where you have more than one Status with a description of "destroyed", and you wanted to query by Status ids instead of description, you would just do:

destroyedStatusIDs = Status.objects.filter(description="destroyed").values_list("id", flat=True)
clients_destroyed_items = StorageItem.objects.filter(client=client, 
    itemstatushistory__status__in=destroyedStatusIDs)

BTW, it's considered good practice to set related_name on your ForeignKey, OneToOneField, and ManyToManyField relationships, usually to plurals. So your history class becomes:

class ItemStatusHistory(models.Model):
    date = models.DateTimeField(auto_now=True)
    contact = models.ForeignKey(Contact, related_name="history")
    item = models.ForeignKey(StorageItem, related_name="history")
    status = models.ForeignKey(Status, related_name="history")
    user = models.ForeignKey(User, related_name="history")

which would change my first example to:

destroyedStatus = Status.objects.get(description="destroyed")
clients_destroyed_items = StorageItem.objects.filter(client=client, 
    history__status=destroyedStatus)

EDIT 2: Ah, so you only want to consider the current (i.e. latest) Status. This is where aggregation and F objects come in. Basically, the idea is to have the database create a "fake column" in the table which has the date of the latest (i.e. maximum date) Status, then require the date to match as well as the status:

from django.db.models import F, Max

destroyedStatus = Status.objects.get(description="destroyed")
clients_destroyed_items = StorageItem.objects.annotate(
    last_change_date=Max("itemstatushistory__date")).filter(client=client, 
    itemstatushistory__status=destroyedStatus, 
    itemstatushistory__date=F("last_change_date"))

I haven't tested this, this is the first time I've tried this, and there may be a better way, so comments are welcome.

If you want a queryset of the items that belong to a client and are destroyed:

clients_destroyed_items = StorageItem.objects.filter(client=client, 
    itemstatushistory__status__description='destroyed')

Lookups that span relationships¶

Django offers a powerful and intuitive way to "follow" relationships in lookups, taking care of the SQL JOINs for you automatically, behind the scenes. To span a relationship, just use the field name of related fields across models, separated by double underscores, until you get to the field you want.

This example retrieves all Entry objects with a Blog whose name is 'Beatles Blog':

Entry.objects.filter(blog_name_exact='Beatles Blog')

This spanning can be as deep as you'd like.

It works backwards, too. To refer to a "reverse" relationship, just use the lowercase name of the model.