Dynamic views in Django ORM - best approach?

Question

Some background

I am considering rebuilding an existing Laravel website with Django. It's a website that allows sharing benchmark data from drone/UAV propulsion components. Some benchmarks are done while testing multiple motors and propellers at the same time, which means a battery would be under the load of multiple motors, but it also means the airflow from one propeller has an impact on the data captured on the other propeller. This means the data is physically coupled. Here is an example. Right now I am trying to structure this project to allow upcoming features, and to see if Django ORM is a good fit.

Simplified Django models

class Benchmark(models.Model):
    title = models.CharField()
    private = models.BooleanField(default=False)
    hide_torque = models.BooleanField(default=False)


class AbstractComponent(models.Model):
    brand = models.CharField()
    name = models.CharField()

    class Meta:
        abstract = True


class Motor(AbstractComponent):
    shaft_diameter_mm = models.FloatField()


class Propeller(AbstractComponent):
    diameter_in = models.FloatField()


class Battery(AbstractComponent):
    capacity_kwh = models.FloatField()


class Powertrain(models.Model):
    benchmark = models.ForeignKey(Benchmark, on_delete=models.CASCADE, related_name='powertrains')
    motor = models.ForeignKey(Motor, on_delete=models.CASCADE)
    propeller = models.ForeignKey(Propeller, on_delete=models.CASCADE, blank=True, null=True)
    battery = models.ForeignKey(Battery, on_delete=models.CASCADE, blank=True, null=True)


class DerivedDataManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset()\
            .annotate(electrical_power_w=F('voltage_v') * F('current_a'))\
            .annotate(mechanical_power_w=F('torque_nm') * F('rotation_speed_rad_per_s'))\
            .annotate(motor_efficiency=F('mechanical_power_w') / F('electrical_power_w'))


class DataSample(models.Model):
    powertrain = models.ForeignKey(Powertrain, on_delete=models.CASCADE, related_name='data')
    time_s = models.FloatField()
    voltage_v = models.FloatField(blank=True, null=True)
    current_a = models.FloatField(blank=True, null=True)
    rotation_speed_rad_per_s = models.FloatField(blank=True, null=True)
    torque_nm = models.FloatField(blank=True, null=True)
    thrust_n = models.FloatField(blank=True, null=True)

    objects = models.Manager()
    derived = DerivedDataManager()

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['powertrain', 'time_s'], name='unique temporal sample')
        ]

Question

I was able to add "derived" measurements, like electrical_power_w to each row of the data, but I have no clue on how can I add derived measurements that combines the data of multiple drive trains within the same benchmark:

Assuming 3 powertrains, each with their own voltage and current data, how can I do:

Total_power = powertrain1.power + powertrain2.power + powertrain3.power

for each individual timestamp (time_s)? A total power is only meaningul if the Sum is made on simultaneously taken samples.

Goal

Without loading all the database data in Django, I would eventually want to get the 5 top benchmarks in terms of maximum total power, taking into account some business logic:

• benchmarks marked as private are automatically excluded (until auth comes in)
• benchmarks that opt to hide the torque automatically make the torque data, along as all the derived mechanical power and motor efficiency values go to None.

I would like to recreate this table, but with extra columns appended, like 'maximum thrust', etc... This table is paginated from within the database itself.

Answer 1

Hmmmm, this is quite a tricky one to navigate. I am going to start by adding the following annotation model method:

from django.db.models import Sum

I guess then it would be a case of adding:

.annotate(total_power=Sum(electrical_power_w))

But I think the issue is that each row in your DerivedDataManager queryset represents one DataSample which in turn links to one Powertrain via the ForeignKey field.

It would be better to do this in the business logic layer, grouping by the powertrain's UUID (you need to add this to your Powertrain model - see https://docs.djangoproject.com/en/3.0/ref/models/fields/#uuidfield for details of how to use this. Then, because you have grouped by, you can then apply the Sum annotation to the queryset.

So, I think you want to navigate down this path:

DataSample.objects.order_by(
    'powertrain'
).aggregate(
    total_price=Sum('electrical_power_w')
)