Django order_by() filter with distinct()

django django-models django-aggregation django-filters
pkdkk picture pkdkk · Dec 14, 2013 · Viewed 31.7k times · Source

How can I make an order_by like this ....

p = Product.objects.filter(vendornumber='403516006')\
                   .order_by('-created').distinct('vendor__name')

The problem is that I have multiple vendors with the same name, and I only want the latest product by the vendor ..

Hope it makes sense?

I got this DB error:

SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: SELECT DISTINCT ON ("search_vendor"."name") "search_product"...

Answer

janos picture janos · Dec 14, 2013

Based on your error message and this other question, it seems to me this would fix it:

p = Product.objects.filter(vendornumber='403516006')\
               .order_by('vendor__name', '-created').distinct('vendor__name')

That is, it seems that the DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). So by making the column you use in distinct as the first column in the order_by, I think it should work.

Related questions

How to filter objects for count annotation in Django?

Consider simple Django models Event and Participant: class Event(models.Model): title = models.CharField(max_length=100) class Participant(models.Model): event = models.ForeignKey(Event, db_index=True) is_paid = models.BooleanField(default=False, db_index=True) It's easy to annotate …

Read More
Annotating SUM aggregation function leading to 'None' value in Django

Doing my first real Django project, and need guidance. Background: My project is a reddit clone. Users submit links+text. Visitors upvote or downvote. There's a social_ranking algo, runs every ~2 mins as a background script, reranks all the submissions …

Read More
Django GROUP BY strftime date format

I would like to do a SUM on rows in a database and group by date. I am trying to run this SQL query using Django aggregates and annotations: select strftime('%m/%d/%Y', time_stamp) as the_date, …

Read More