[Answer]-Django ORM query to exclude grouped by row if certain conditions are met (postgresql)

by

1👍

✅

I like to write queryset methods that help explain what we are trying to do, and work backwards from there.

Version.objects.newest_by_content_type().unreviewed()

Then, we just need the queryset/manager methods. [Note: I’m using the pattern described at Building a higher-level query API to add these methods onto the query set].

Which, as it turns out, is the code you have already written, just ordered slightly differently.

def newest_by_content_type(self):
    return self.order_by('content_type__id', 'object_id', '-updated').distinct('content_type', 'object_id')

def unreviewed(self):
    return self.filter(is_reviewed=False)

Then, you can do other things with this query set. Like remove ones owned by the current user, for instance.

The previous incarnation of this answer discussed an alternative solution, using a superseded_by, or supersedes self-relationship:

class Version(models.Model):
    supersedes = models.ForeignKey('Version', 
        related_name='superseded_by', null=True, blank=True)

Then, you can get all non-superseded objects by:

Version.objects.filter(superseded_by=None)

Depending upon constraints, it may make sense to use a OneToOneField for the superseded relationship: if a version can only be superseded by at most one version, and a version may only supersede one version. It’s also possible to use this, along with HTTP headers and to return 412 Precondition Failed when an already superseded object is attempted to be superseded again, for instance. This would make sense where a version chain is ‘straight’, and does not allow for branches.

👤Matthew Schinckel

Leave a comment