How to create an annotation in Django that references two related models

Question

I'm trying to add an annotation to a QuerySet that is True/False when the value of a field on one related object is less than the value of a field on a different related object.

Here are some models for an example:

class RobotManager(models.Manager):
    queryset = super(RobotManager, self).get_queryset()
    queryset = queryset.annotate(canteen_empty=UNKNOWN CODE)
    return queryset

class Robot(models.Model):
    # Has some other unrelated stuff
    objects = RobotManager()

class CanteenLevel(models.Model):
    time = models.DateTimeField()
    robot = models.ForeignKey("SomeApp.Robot")
    gallons = models.IntegerField()

class RobotConfiguration(models.Model):
    time = models.DateTimeField()
    robot = models.ForeignKey("SomeApp.Robot")
    canteen_empty_level = models.IntegerField()

With the above models, as the Robot's Configuration or CanteenLevel change, we create new records and save the historicals.

What I would like to do is add an annotation to a Robot QuerySet that states if the Robot's Canteen is considered empty (Robot's latest CanteenLevel.gallons is less than the Robot's latest Configuration.canteen_empty_level).

The aim is to allow for a statement like this using the annotation in the QuerySet:

bad_robots = Robot.objects.filter(canteen_empty=True)

I had tried something like this in the annotation:

canteen_empty=ExpressionWrapper(CanteenLevel.objects.filter(robot=OuterRef('pk')).order_by('-time').values('gallons')[:1] <= RobotConfiguration.objects.filter(robot=OuterRef('robot')).order_by('-time').values('canteen_empty_level')[:1], output_field=models.BooleanField))

But obviously the "<=" operator isn't allowed.

I also tried this:

canteen_empty=Exists(CanteenLevel.objects.filter(robot=OuterRef('pk')).order_by('-time').values('gallons')[:1].filter(gallons__lte=Subquery(RobotConfiguration.objects.filter(robot=OuterRef('robot')).order_by('-time').values('canteen_empty_level')[:1]))))

But you can't filter after taking a slice of a QuerySet.

Any help would be appreciated!

score 2 · Accepted Answer · answered Jun 12 '19 at 19:46

We can make two annotations here:

from django.db.models import Subquery, OuterRef

latest_gallons = Subquery(CanteenLevel.objects.filter(
    robot=OuterRef('pk')
).order_by('-time').values('gallons')[:1])

latest_canteen = Subquery(RobotConfiguration.objects.filter(
    robot=OuterRef('pk')
).order_by('-time').values('canteen_empty_level')[:1])

then we can first annotate the Robot objects with these, and filter:

from django.db.models import F

Robot.objects.annotate(
    latest_gallons=latest_gallons,
    latest_canteen=latest_canteen
).filter(latest_gallons__lte=F('latest_canteen'))

This will construct a query that looks like:

SELECT robot.*,
    (SELECT U0.gallons
     FROM canteenlevel U0
     WHERE U0.robot_id = robot.id
     ORDER BY U0.time DESC
     LIMIT 1) AS latest_gallons,
    (SELECT U0.canteen_empty_level
     FROM robotconfiguration U0
     WHERE U0.robot_id = robot.id
     ORDER BY U0.time DESC
     LIMIT 1) AS latest_canteen
FROM robot
WHERE
    (SELECT U0.gallons
     FROM canteenlevel U0
     WHERE U0.robot_id = robot.id
     ORDER BY U0.time DESC
     LIMIT 1
    ) <= (
     SELECT U0.canteen_empty_level
     FROM robotconfiguration U0
     WHERE U0.robot_id = robot.id
     ORDER BY U0.time DESC
     LIMIT 1
    )

Note however that if a Robot has no related CanteenLevel, or RobotConfiguration (one of them, or both), then that Robot will not be included in the queryset.

Thank you very much for the quick and detailed response! This definitely gets me what I need. — Bryan Hurst, Jun 13 '19 at 16:49

How to create an annotation in Django that references two related models

1 Answers1