CODEWITHSUNDEEP
djangopostgresql
Headmaster
Headmaster

Reputation: 2322

Django distinct with order by different column

I have such model

    class Product(models.Model):
        created_at = models.DateTimeField(default=timezone.now)
        cell = models.CharField(max_length=100, null=True)

Now I need a queryset that must be ordered by creation date and unique cell, something like Product.objects.order_by('created_at').distinct('cell') but I got an sql error.

django.db.utils.ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON ("apps_product"."cell")

How should queryset be changed to get required result?

Upvotes: 0

Views: 237

Answers (2)

ddelange
ddelange

Reputation: 1577

This is currently not possible without a Subquery. There is an open feature request that would allow queries like:

qs = (
  Product.objects
  # deduplicate on "cell", keeping the most recently created record
  .order_by("cell", "-created_at").distinct("cell")
  # then order the result of the distinct subquery to get most recently created first
  .order_by("-created_at")
)

If you try this query now (as of Django 4.1.7), it will fail with the same error message you posted.

As a comment under the feature requests explains, you can try something like:

from django.db.models import Subquery
distinct = (
  Product.objects
  # deduplicate on "cell", keeping the most recently created record
  .order_by("cell", "-created_at").distinct("cell")
  .values("pk")
)
qs = (
  Product.objects
  .filter(pk__in=Subquery(distinct))
  # then order the result of the distinct subquery to get most recently created first
  .order_by("-created_at")
)

Upvotes: 0

Aurélien
Aurélien

Reputation: 1655

Folowing your error message, you should try:

Product.objects.order_by('cell', '-created_at').distinct('cell')

Upvotes: 2

Related Questions