How can I select and order items based on a subquery?
<p>I have the following models in Django:</p>

<pre><code>class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()
    country = models.ForeignKey(Country)

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ForeignKey(Author)
    pubdate = models.DateField()
</code></pre>

<p>How can I get a queryset of Authors sorted by the first time they published a book?</p>

<p>In SQL, I could do this using:</p>

<pre><code>SELECT *
  FROM ( SELECT author_id 
              , MIN(pubdate) as date
           FROM books
          GROUP
             BY author_id
         HAVING
            MIN(pubdate)) AS first_published
  JOIN author
    ON author.id = first_published.author_id
 LIMIT 15
OFFSET 15
 ORDER
    BY first_published.author_id
</code></pre>

<p>It's been a while with Django and I haven't been able to figure out how to do this.</p>

<hr>

<p>Now this is just nasty:</p>

<pre><code>from django.db.models.sql.compiler import SQLCompiler
_quote_name_unless_alias = SQLCompiler.quote_name_unless_alias
SQLCompiler.quote_name_unless_alias = lambda self,name: name if name.startswith('(') else _quote_name_unless_alias(self,name)

subquery = "(SELECT author_id, MIN(pubdate) as first_release_date FROM app_books GROUP BY author_id HAVING MIN(pubdate)) AS releases"
condition = "releases.author_id = app_authors.id"
order = '-releases.first_release_date'
Author.objects.get_queryset().extra(tables=[subquery], where=[condition]).order_by(order)
</code></pre>

<p>Try this</p>

<pre><code>Author.objects.all().annotate(s=Min('book__pubdate')).order_by('s')
</code></pre>

<p>When if some author dont have books</p>

<pre><code>Author.objects.exclude(book__pubdate__isnull=True).annotate(s=Min('book__pubdate')).order_by('s')
</code></pre>

python sql django django-models django-orm