[FIXED] Order queryset based on condition from fields in different table

Issue

I have some resources that a user can subscribe to, for added advantages. My challenge is in sorting them, where the resources with subscriptions come first and the rest follow.

Initially, the resources were stored like this.

from django.db import models
from django.utils import timezone


class Entity(models.Model):
    has_subscription = models.BooleanField()
    created_at = models.DateTimeField(default=timezone.now)
    # other fields

and I would sort them like this,

Entity.objects.all().order_by("-has_subscription", "-created_at")

which worked.

I decided to move to a different way of storing it that would favor a time bound subscription. I came up with this.

from django.db import models
from django.utils import timezone


class Entity(models.Model):
    created_at = models.DateTimeField(default=timezone.now)

    @property
    def has_active_subscription(self):
        if (
            self.entity_subscriptions.filter(
                start_date__lte=timezone.now(), end_date__gte=timezone.now()
            ).count()
            > 0
        ):
            return True

        return False

class Subscriptions(model.Model):
    entity = models.ForeignKey(
        Entity,
        on_delete=models.CASCADE,
        related_name="entity_subscriptions",
        related_query_name="entity_subscription",
    )
    start_date = models.DateTimeField()
    end_date = models.DateTimeField()

How can I sort the queryset in such a manner that the resources with subscriptions come first.

Solution

After much searching, The following solution worked for me.

from django.db.models import Case, When, Q
from django.utils import timezone

Entity.objects.annotate(
    active_sub=Case(
        When(
            Q(
                entity_subscription__start_date__lte=timezone.now(),
                entity_subscription__end_date__gte=timezone.now(),
            ),
            then=True,
        ),
        default=False,
    )
).order_by("-active_sub", "-created_at")

Answered By - Timothy Oliver