Custom manager With DISTINCT ON

Using Django
1

Hi everyone,

I have the next code:

class ReportManager(Manager):
    def get_queryset(self):
        return (
            super()
            .get_queryset()
            .select_related("owner")
            .order_by("owner", "type", "-created_date")
            .distinct("owner, "type")
        )


class Report(Model):
    owner = ForeignKey("otherApp.Owner", on_delete=CASCADE)
    type = CharField(max_length=50, db_index=True)
    data = JSONField(default=dict)

    created_date = DateTimeField(auto_now_add=True, null=True, db_index=True)
    updated_date = DateTimeField(auto_now=True)

    objects = ReportManager()

    class Meta:
        unique_together = (("owner", "type", "created_date"))

I’m using postgres so I can use DISTINCT ON. The problem is that when I run the test I get the next error:

>>> python manage.py test
...
django.db.utils.ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: ...O SCROLL CURSOR WITH HOLD FOR SELECT DISTINCT ON ("app_report...

When I comment the line objects = ReportManager() I don’t get the error.

Can someone guide me and tell me what I’m doing wrong?

2

Are you missing the close-quote in the distinct clause for “owner” in your real code?

What versions of Django and Python are you using? I’m unable to recreate those symptoms under Django 3.2 / Python 3.8.

3

Hi Ken, Thanks for the reply,

It was a typo when I “sanitized” the code for the forum.

I am using python 3.8 and Django 3.1.8

class ReportManager(Manager):
    def get_queryset(self):
        return (
            super()
            .get_queryset()
            .select_related("owner")
            .order_by("owner", "type", "-created_date")
            .distinct("owner", "type")
        )


class Report(Model):
    owner = ForeignKey("otherApp.Owner", on_delete=CASCADE)
    type = CharField(max_length=50, db_index=True)
    data = JSONField(default=dict)

    created_date = DateTimeField(auto_now_add=True, null=True, db_index=True)
    updated_date = DateTimeField(auto_now=True)

    objects = ReportManager()

    class Meta:
        unique_together = (("owner", "type", "created_date"))
4

So you’ve mentioned that you’ve “sanitized” this for posting. If you run this as posted, do you still get the error? (I don’t - I can’t recreate this from what you have here.)
If you do, it may be a difference between Django 3.1 and 3.2 If you don’t, then there’s probably something trimmed out that is the actual cause.

5

Mmmm you are right I’ve created a new project and I can’t reproduce the error.

On the real project I already fix the bug in the following way:

  1. Edit de default Manager:
class Report(Model):
    owner = ForeignKey("otherApp.Owner", on_delete=CASCADE)
    type = CharField(max_length=50, db_index=True)
    data = JSONField(default=dict)

    created_date = DateTimeField(auto_now_add=True, null=True, db_index=True)
    updated_date = DateTimeField(auto_now=True)

    objects = Manager()
    last_reports = ReportManager()

    class Meta:
        unique_together = (("owner", "type", "created_date"))
        default_manager_name = "objects"
  1. I change all occurrences of models.Report.objects to models.Report.last_reports

I really appreciate your help!