Django order model by date and move models without date to the end-CodePudding

I have a model tickets, with a sum in it. I'm trying to make spending tickets by nearest expire date. But if I'm ordering by date_expire, it move date_expire=None models to the top. But date_expire=None means that ticket has no limit by time, and should be used after all tickets, that has time limit.

My code:

ticket = Tickets.objects.filter(
                sum__gte=0,
                date_expire__gte=timezone.now(),
                user_id=user.id,
                burned=False
            ).order_by(
                'date_expire'
            ).first()

I can try to sort by sorted func with lambda key, but I think there's a better way to do this in orm

UPD

I think i found a solution for myself. Now my code looks like:

ticket = Tickets.objects.filter(
                sum__gte=0,
                date_expire__gte=timezone.now(),
                user_id=user.id,
                burned=False
            ).annotate(
                has_date=Func(F('date_expire'), Value(None), function='IFNULL')
            ).order_by(
                '-has_date',
                'date_expire'
            ).first()

I used annotate function of QuerySet to make a new value has_date which is using an SQL func IFNULL, that returns True if field date_expire is NULL.

SQL field in query must be something like this:

IFNULL("tickets"."date_expire", NULL) AS "has_date"

And then i sort by -has_date first, to move all objects with True values under the False values.

Maybe there's a better way to do that, but that method works for my idea.

CodePudding user response：

You can use a F expression with nulls_first or nulls_last. Check the Using F() to sort null values on the django documentation.

In your case:

from django.db.models import F

ticket = Tickets.objects.filter(
            sum__gte=0,
            date_expire__gte=timezone.now(),
            user_id=user.id,
            burned=False
        ).order_by(
            F('date_expire').desc(nulls_last=True)
        ).first()

CodePudding user response：

Probably not the official solution:

One method is to introduce a value that represents a ceiling value. So instead of using None for date_expire use a date 1000 years in the future like 12/31/3000. That way this date is always later than your current dates.