CODEWITHSUNDEEP
djangodjango-orm
Murilo Sitonio
Murilo Sitonio

Reputation: 305

How to get the latest version of a all previously distinctable objects within a single query in django?

I have a model with a version field.

class Workflow(models.Model):
    ...
    process_id = models.CharField(null=False, max_length=100) # not unique!
    version = models.IntegerField(default=1)
    enabled = models.BooleanField(default=True, null=False)

I'm trying to find the latest version for each process_id within a single query. Basically, I want to do the following:

class WorkflowFilterSet(django_filters.FilterSet):
    ...
    only_latest_versions = django_filters.BooleanFilter(method='get_only_latest_versions')

    def get_only_latest_versions(self, queryset, field_name, value):
        if value:
            workflows = []
            for worflow in queryset.distinct('process_id'):
                workflow_ = queryset.filter(process_id=worflow.process_id,
                                            enabled=True).latest('version')
                workflows.append(workflow_)
            return workflows  # even if I wanted to do like this I can't because I must return a QuerySet
        return queryset

How can I do the above with a single query?

Thank you very much!

EDIT:

I could retrieve the latest versions with the following query:

queryset.filter(enabled=True).values('process_id').annotate(max_version=Max('version'))

However, it returns, obviously, only the process_id and max_version. How can I retrieve all fields or the objects themselves with this approach?

Upvotes: 1

Views: 89

Answers (1)

Bernardo Duarte
Bernardo Duarte

Reputation: 4264

You can use the Subquery expressions together with OuterRef.

from django.db.models import OuterRef, Subquery
subquery = Workflow.objects.filter(process_id=OuterRef('process_id'), enabled=True).order_by('-version')
queryset = Workflow.objects.filter(version=Subquery(subquery.values('version')[:1]))

The variable queryset is a Queryset of Workflow containing the latest version for each process_id.

Upvotes: 2

Related Questions