samedi 24 octobre 2015

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?

Aucun commentaire:

Enregistrer un commentaire