1

In Django, on a given model it looks like the RelatedManager may only be set statically (see: docs) but is there any way to set the order_by on the related manager for a given Queryset? I tried using prefetch_related and Prefetch, but that does not seem to work:

qs = qs.prefetch_related(
   Prefetch('item_set', queryset=Item.objects.order_by('capture_dt').all()))

res = qs.first().item_set.all().ordered

In this case res is False.

The problem comes when I try to access the related manager: accessing w/o order_by does not call another SQL query whereas if I then try to order_by, an additional query is executed which causes an N + 1 SQL queries to be executed.

These two lines return the same results but the first generates far fewer SQL queries:

r0 = [x.pk for x in sorted(self.parent.item_set.all(), key=lambda s: s.capture_dt)].index(self.pk)
r1 = list(x.pk for x in self.parent.item_set.order_by('capture_dt').all()).index(self.pk)
Alex Rothberg
  • 10,243
  • 13
  • 60
  • 120
  • its hard to say without the models, are you looking for `.order_by('fkmodel__field')`?.. – Sayse Feb 05 '16 at 07:26
  • I don't want to order the top level queryset (above that would be `qs`). I just want to order the items in `item_set` for each entry in `qs`. – Alex Rothberg Feb 05 '16 at 14:31

1 Answers1

2

I'm a little late to the party here, but hopefully I can help some future explorer.

I had a very similar problem, and I even found an old bug report that more or less relates to this here, and judging by that, it's not going to be solved anytime soon; and if the last lines on that page are correct, it will never be solved. So, you simply can't order by a related field and not retrieve duplicates; whether it be the way you tried through the RelatedManger or some other approach: for instance SomeModel.objects.order_by('related__related_field')

I did find a way to make it work, though it's not elegant. In my case, I have a model that describes a base model of guitar that I build, and there are photos related to that model. I'd like to have the query order the photos based on a priority level; this allows me to control the order photos are displayed on the website served by the Django back-end. So the ordering is hard-coded into the model like so:

class PhotosModel(models.Model):
    class Meta:
        ordering = ['priority_level']
    priority_level = models.SmallIntegerField()
    photo_src = models.URLField(null=True, blank=True, default=None, max_length=65)
    photo_name = models.CharField(max_length=20)
    photo_description = models.TextField(max_length=200, blank=True, null=True)
    base_model = models.ForeignKey(BaseModelModel,
                                   on_delete=models.CASCADE,
                                   related_name='photos')

    def __str__(self):
        return self.photo_name

For me, this works well enough, though it does have the obvious downside that it's always ordered that way. I'd love to know a better way, if anyone has one.

Hildy
  • 510
  • 5
  • 15