8

need to get a queryset with the first book (by a date field) for each author (related to by foreign key) ...is there a Django ORM way to do this (without custom SQL preferred but acceptable)

*Edit: Please note that an answer that works using only a modern open source backend like Postgresql is acceptable ..still ORM based solution preferred over pure custom sql query)

Models
class Book(Model):
  date = Datefield()
  author = ForeignKey(Author)

class Author(Model):
  name = CharField()


Book.objects.filter(??)
eskhool
  • 657
  • 1
  • 11
  • 24
  • I had almost the same question yesterday http://stackoverflow.com/questions/14213333/get-latest-objects-django . In the EDIT 2 section is my current hack. – Milla Well Jan 09 '13 at 12:17
  • Well, I'm on PostgreSQL so I am not limited on the distinct...I'm not sure if I can find my answer based on your question...thanks for the pointers however – eskhool Jan 10 '13 at 12:26

1 Answers1

10

If you use PostgreSQL or another DB backend with support for DISTINCT ON there is a nice solution:

Books.objects.order_by('author', '-date').distinct('author')

Otherwise I don't know a solution with only one query. But you can try this:

from django.db.models import Q, Max
import operator

books = Book.objects.values('author_id').annotate(max_date=Max('date'))
filters = reduce(operator.or_, [(Q(author_id=b['author_id']) &
    Q(date=b['max_date'])) for b in books])
queryset = Books.objects.filter(filters)

With the combination of .values() and .annotate() we group by the author and annotate the latest date of all books from that author. But the result is a dict and not a queryset. Then we build a SQL statement like WHERE author_id=X1 AND date=Y1 OR author_id=X2 AND date=Y2.... Now the second query is easy.

Hannes
  • 109
  • 2
  • Thanks, I am aware of the 2 query reply...this will likely be the answer I would like to accept given so far since its in detail and addresses the exact question, but I'm still hoping that the Django ORM with custom sql might allow this to happen in a single query – eskhool Jan 16 '13 at 05:30
  • Then I assume you want a solution for all DB backends in a single query? Something like this: `Book.objects.raw('SELECT *, MAX(date) as max_date FROM myapp_book GROUP BY author_id')`? – Hannes Jan 18 '13 at 09:47
  • Updated my question for a postgresql only solution ...still would prefer a solution based in the ORM even if it doesn't work on all backends...custom sql would just make further chaining difficult – eskhool Jan 19 '13 at 10:41