CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-rest-frameworkorm
Hammad
Hammad

Reputation: 615

Limit prefetch_related to 1 by a certain criteria

So I have models like these

class Status(models.Mode):
    name = models.CharField(max_length=255, choices=StatusName.choices, unique=True)

class Case(models.Model):
    # has some fields

class CaseStatus(models.Model):
    case = models.ForeignKey("cases.Case", on_delete=models.CASCADE, related_name="case_statuses")
    status = models.ForeignKey("cases.Status", on_delete=models.CASCADE, related_name="case_statuses")
    created = models.DateTimeField(auto_now_add=True)

I need to filter the cases on the basis of the status of their case-status but the catch is only the latest case-status should be taken into account.

To get Case objects based on all the case-statuses, this query works:

Case.objects.filter(case_statuses__status=status_name)

But I need to get the Case objects such that only their latest case_status object (descending created) is taken into account. Something like this is what I am looking for:

Case.objects.filter(case_statuses__order_by_created_first__status=status_name)

I have tried Prefetch as well but doesnt seem to work with my use-case

sub_query = CaseStatus.objects.filter(
id=CaseStatus.objects.select_related('case').order_by('-created').first().id)

Case.objects.prefetch_related(Prefetch('case_statuses', queryset=sub_query)).filter(
case_statuses__status=status_name)

This would be easy to solve in raw postgres by using limit 1. But not sure how can I make this work in Django ORM.

Upvotes: 0

Views: 97

Answers (1)

nicootto
nicootto

Reputation: 159

You can annotate your cases with their last status, and then filter on that status to be what you want.

from django.db.models import OuterRef

status_qs = CaseStatus.objects.filter(case=OuterRef('pk')).order_by('-created').values('status__name')[:1]
Case.objects.annotate(last_status=status_qs).filter(last_status=status_name)

Upvotes: 1

Related Questions