CODEWITHSUNDEEP
pythondjangoperformancepostgresqlsqlite
Hassan Baig
Hassan Baig

Reputation: 15834

Rewriting this database access query to circumvent 'DISTINCT ON' error, in Django

I have two very simple models:

class Link(models.Model):
    description = models.TextField(validators=[MaxLengthValidator(500)])
    submitter = models.ForeignKey(User)
    submitted_on = models.DateTimeField(auto_now_add=True)

class Publicreply(models.Model):
    submitted_by = models.ForeignKey(User)
    answer_to = models.ForeignKey(Link)
    submitted_on = models.DateTimeField(auto_now_add=True)
    description = models.TextField(validators=[MaxLengthValidator(250)])

In the bid to get the latest publicreply for each link object, I tried this: latest_replies = Publicreply.objects.filter(answer_to_id__in=link_ids).order_by('answer_to', 'submitted_on').distinct('answer_to') where link_ids is a list of all link objects I needed in this particular case.

Problem is, I get a DISTINCT ON fields is not supported by this database backend. I'm on SQLite locally, Postgres in production. What's a way to re-write this query such that I get the same result? Note that I don't want to run a database query under a FOR loop - I've profiled that and the results are very unsavory. Is there a single query to get the job done?

Upvotes: 1

Views: 83

Answers (1)

dursk
dursk

Reputation: 4445

You could just take care of the distinct in memory.

replies_by_link = {}
replies = Publicreply.objects.filter(answer_to_id__in=link_ids).order_by('answer_to', 'submitted_on')

for reply in replies:
    if reply.answer_to_id not in replies_by_link:
        replies_by_link[reply.answer_to_id] = reply

and then you can access all the replies via replies_by_link.values().

Upvotes: 1

Related Questions