Django manager annotate first element of m2m as fk

Question

Let's say I have these models.

class AdventureWaypoint(models.Model):
    adventure = models.ForeignKey("Adventure", on_delete=models.CASCADE)
    waypoint = models.ForeignKey("Waypoint", on_delete=models.CASCADE)
    created_timestamp = models.DateTimeField(auto_now_add=True)

class Waypoint(models.Model):
    name = models.CharField(max_length=255)

class Adventure(models.Model):
    waypoints = models.ManyToManyField("Waypoint", through='AdventureWaypoint', related_name='waypoints')
    objects = AdventureManager()

How could I write a manager that annotates first_waypoint to every queryset?

I tried the following, but unfortunately Subquery doesn't work as expected. Couldn't use F as well.

class AdventureManager(models.Manager):
    def get_queryset(self):
        qs = super(AdventureManager).get_queryset()
        first_waypoint = AdventureWaypoint.objects.filter(adventure_id=OuterRef("id")).order_by('created_timestamp').first().waypoint
        return qs.annotate(first_waypoint=Subquery(first_waypoint, output_field=models.ForeignKey("Waypoint", on_delete=models.CASCADE, null=True, blank=True)))

Do I have to resort to raw SQL or how can this be done via Django ORM?

Note: I don't want to use @property as I want to use this annotated field in querysets e.g for filtering.

ruddra · Accepted Answer · 2020-06-16T11:24:52.550

1

You can try like this(according to the documentation):

first_waypoint = AdventureWaypoint.objects.filter(adventure_id=OuterRef("id")).order_by('created_timestamp')
return qs.annotate(first_waypoint=Subquery(first_waypoint.values('waypoint')[:1]))

Update

You can't add an actual instance like this. You can get only the name like this:

return qs.annotate(first_waypoint=Subquery(first_waypoint.values('waypoint__name')[:1]))

Or you can use prefetch_related to fetch the data to reduce database hit and access the value via for loop:

for adventure in Adventure.objects.prefetch_related('waypoints'):
   print(adventure.waypoints.first())

edited Jun 16 '20 at 11:24

answered Jun 16 '20 at 09:08

ruddra

50,746
7
78
101

This does work, but is there a way to get the actual instance reference as well (as if it was fk)? `adventure.first_waypoint` gives the id `1`. I would want to do `adventure.first_waypoint.name`. – colddev Jun 16 '20 at 09:36

Django manager annotate first element of m2m as fk

1 Answers1

Update

Linked