Filter with order for two fields-CodePudding

I have Model Klass with fields like this:

  date_start = models.DateField(null=True, blank=True, default=date.today)
  date_finish = models.DateField(null=True, blank=True)

As you see, date_start will be usually filled but date_finish may not. If neither one is filled we should not consider this record in further filtering.

I would like to see objects (first N results) ordered by latest date regardless if that's date_start or date_finish. To be exact: considered date shall be date_finish if exists, date_start otherwise.

Please note, that I don't want N/2 finished items and N/2 only started items concatenated but recent N "touched" ones.

My first idea is to provide this extra field of considered_date that would be filled as I proposed but I don't know how to implement this. Shall it be done:

on Model level, so new DateField to be added and have it's content always filled with sth
selected for 2 seperate conditions (N elements each), then provided with temporary extra field (but without saving into db), then 2 sets joined and ordered by again for this new condition

Fun fact: I also have BooleanField that indicates if period is closed or not. I needed it for simplicity and filtering but we could use this here as well. It's obviously handled by save() function (default True, set to False if date_finish gets filled).

To be honest this "feature" is not critical in my app. It's just displaying some "latest changes" on welcome page so can be triggered quite often.

CodePudding user response：

It seems that your description of considered_date is a perfect use case for COALESCE sql function, which returns first not NULL value from its arguments.

So, in plain SQL this will return value of date_finish if it is not NULL or date_start otherwise (assuming date_start is never NULL as it has the default value)

COALESCE(your_table_name.date_finish, your_table_name.date_start);

Django ORM has an API for this function. Using it with annotate queryset method, we can build the query you want without creating extra fields on the model.

Let's call your model TestModel just for convenience

from django.db.models.functions import Coalesce

TestModel.objects.annotate(
    latest_touched_date=Coalesce("date_finish", "date_start")
).order_by("-latest_touched_date")

Please note, this will work only if date_finish is bigger than date_start. (which I think is true)

You can add exclude to filter any records where both date_finish and date_start are NULLs

from django.db.models.functions import Coalesce

TestModel.objects.exclude(date_start__isnull=True, date_finish__isnull=True).annotate(
    latest_touched_date=Coalesce("date_finish", "date_start")
).order_by("-latest_touched_date")

Just slice the queryset to get first N results.