Django qs.extra(select=)?

Question

PostgreSQL

Not strong in sql, more precisely just yesterday I read the basics for the smallest.

actions = ('не', ' важно', 'что тут')
select = {'workflow_date': 'SELECT MAX(created_at) FROM workflow_item 
           WHERE workflow_item.info_id = info.id AND workflow_item.kind IN {actions!r}'.format(actions=actions)}

If the WHERE condition is not met then workflow_date = None, I need to get rid of this effect. A great option would be to replace None with some default date. In general, can someone give me an idea how to do it more precisely how to do it in sql.
After that, I just apply order_by('-workflow_date') and if the result is None, they come first.

Answer 1

A task from the category of sorting authors by the latest science fiction book

select = {'workflow_date': 'SELECT MAX(CASE WHEN workflow_item.kind IN {actions!r} THEN created_at '
                                       'ELSE DATE(\'2000-01-01 00:00:00.000\') END) '
                                       'FROM workflow_item '
                                       'WHERE workflow_item.info_id = info.id'.format(actions=actions)}

The solution turned out to be simple, maybe it will come in handy for someone, although of course everyone has their own task with their own conditions.

Answer 2

from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name


class Book(models.Model):
    BOOK_TYPE_FICTION = 0
    BOOK_TYPE_NONFICTION = 1
    BOOK_TYPE = (
            (BOOK_TYPE_FICTION, 'fiction'),
            (BOOK_TYPE_NONFICTION, 'nonfiction'),
    )

    author = models.ForeignKey(Author, related_name='book_author')
    name = models.CharField(max_length=200)
    book_type = models.IntegerField(default=BOOK_TYPE_FICTION)
    created_at = models.DateField()

    def __str__(self):
        return '"%s", author: %s' % (self.name, self.author.name)

from datetime import date
from books.models import Author, Book


a0 = Author.objects.create(name='Edgar Allan Poe')

Book.objects.bulk_create([
    Book(author=a0, name="The Black Cat", book_type=Book.BOOK_TYPE_FICTION, created_at=date(1843, 8, 19)),
    Book(author=a0, name="The Cask of Amontillado", book_type=Book.BOOK_TYPE_NONFICTION, created_at=date(1853, 8, 19)),
    Book(author=a0, name="A Descent into the Maelström", book_type=Book.BOOK_TYPE_NONFICTION, created_at=date(1863, 8, 19)),
    Book(author=a0, name="The Facts in the Case of M. Valdemar", book_type=Book.BOOK_TYPE_FICTION, created_at=date(1873, 8, 19)),
])

a1 = Author.objects.create(name='Author fiction')

Book.objects.bulk_create([
    Book(author=a1, name="1943", book_type=Book.BOOK_TYPE_FICTION, created_at=date(1943, 8, 19)),
    Book(author=a1, name="1953", book_type=Book.BOOK_TYPE_FICTION, created_at=date(1953, 8, 19)),
    Book(author=a1, name="1963", book_type=Book.BOOK_TYPE_FICTION, created_at=date(1963, 8, 19)),
    Book(author=a1, name="1973", book_type=Book.BOOK_TYPE_FICTION, created_at=date(1973, 8, 19)),
])

a2 = Author.objects.create(name='Author non fiction')

Book.objects.bulk_create([
    Book(author=a2, name="1943", book_type=Book.BOOK_TYPE_NONFICTION, created_at=date(1943, 8, 19)),
    Book(author=a2, name="1953", book_type=Book.BOOK_TYPE_NONFICTION, created_at=date(1953, 8, 19)),
    Book(author=a2, name="1963", book_type=Book.BOOK_TYPE_NONFICTION, created_at=date(1963, 8, 19)),
    Book(author=a2, name="1973", book_type=Book.BOOK_TYPE_NONFICTION, created_at=date(1973, 8, 19)),
])


from django.db.models import Case, When

ids = Book.objects.filter(book_type=Book.BOOK_TYPE_FICTION).order_by('-created_at').values_list('author', flat=True).distinct()

preserved = Case(*[When(pk=pk, then=pos) for pos, pk in enumerate(ids)])

Author.objects.filter(pk__in=ids).order_by(preserved)