How do I reverse the sign in a django database query on matching rows?-CodePudding

I have a Django model that looks like this:

direction_choices = (
    ("up", "Up"),
    ("down", "Down"),
    ("left", "Left"),
    ("right", "Right"),
)

class Movement:
    direction = models.CharField(max_length=5, choices=direction_choices)
    distance = models.PositiveSmallIntegerField()

Lets say the data in my database looks like this:

Movement(direction="up", 4)
Movement(direction="up", 6)
Movement(direction="down", 3)
Movement(direction="down", 1)
Movement(direction="left", 7)
Movement(direction="right", 4)
Movement(direction="left", 1)
Movement(direction="right", 8)

I want to do a database query that will calculate the total distance moved in the Y axis.

In our example, it would be: 4 6-3-1 = 6

I can do something like:

Movement.objects.all().filter(
    direction__in=("up", "down")
    ).aggregate(
        total=Sum('distance')
    )

which gives me:

{'total': 16}

I'm not sure how to tell the database that "down" fields should be multiplied by -1 to have their signs reversed before adding up the total.

I know this is easy to do with Python with a for loop, or list comprehension. How would one have the database do this?

CodePudding user response：

You can aggregate with:

from django.db.models import Q

Movement.objects.filter(
    direction__in=('up', 'down')
).aggregate(
    total=Sum('distance', filter=Q(direction='up'))
        - Sum('distance', filter=Q(direction='down'))
)

or you can work through annotation:

from django.db.models import Case, F, When

Movement.objects.filter(
    direction__in=('up', 'down')
).alias(
    dir=Case(
        When(direction='down', then=-F('distance')),
        default=F('distance')
    )
).aggregate(
    total=Sum('dir')
)

CodePudding user response：

I would do this using a group by to only sum within each direction, followed by doing the final subtraction in Python:

result = Movement.objects.values('direction').order_by('direction').annotate(total_distance=Sum('distance'))
# Transform to dict
result = {row['direction']: row['total_distance'] for row in result}
y_axis = result['up'] - result['down']
x_axis = result['right'] - result['left']

What's the advantage of this approach? It produces a more efficient query, which only needs to iterate over the data once, which is helpful if you have a lot of rows.