CODEWITHSUNDEEP
pythonsqldjangodjango-modelsdjango-orm
Mridang Agarwalla
Mridang Agarwalla

Reputation: 44968

How can I select and order items based on a subquery?

I have the following models in Django:

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()

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

In SQL, I could do this using:

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

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

Now this is just nasty:

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)

Upvotes: 0

Views: 60

Answers (1)

itzMEonTV
itzMEonTV

Reputation: 20339

Try this

Author.objects.all().annotate(s=Min('book__pubdate')).order_by('s')

When if some author dont have books

Author.objects.exclude(book__pubdate__isnull=True).annotate(s=Min('book__pubdate')).order_by('s')

Upvotes: 1

Related Questions