Django model querying for first N rows that sum up to a given number

user_962891 0
Viewed 378

I am sing Django 3.2

I have a model like this:

class MyModel(models.Model):
    name = models.CharField(max_length=255)
    num_visits  = models.PositiveSmallNumber()
    created_at  = models.DateTimeField()

    class Meta:
        ordering = ['created_at']

I want to select the first N rows where the CUMULATIVE SUM of the number of visits = 100 (as an example) A trivial (but probably DB intensive way) to do it would be to iterate through the rows in the table (i.e. elements of the queryset) - but I don't want to do that - for reasons that should be obvious.

How can I write a query that fetches the first N rows where the SUM of visits is a specified number?

def get_rows_satisfying_visit_count(cumulative_sum_in_field=100):
    MyModel.objects.filter(/* what ? */)
django-q django-queryset django-models django python
2 Answers

You can use the slice operator for the queryset:

def get_rows_satisfying_visit_count(visits=100, rows=100):
    return MyModel.objects.filter(num_visits=number)[:rows]

EDIT: With the updated question, from Django >= 2.0, you can do this with Window and Sum:

MyModel.objects.annotate(
    cumulative_sum=Window(
        Sum('num_visits'),
        order_by=F('id').asc()
    )
).filter(cumulative_sum__lte=100)

This will get the first few rows that have the cumulative sum less than or equal to 100.

user_67598440

You can simply use

def get_rows_satisfying_visit_count(number=100):
    MyModel.objects.filter(num_visits=number)[:N]

The queryset will only return the first N elements that satisfy the num_visits condition. You might think that it will return all the objects satisfying the condition and then slice but in reality, a LIMIT database operation is being performed so it satifies your DB intensity condition. You can check out https://docs.djangoproject.com/en/3.2/topics/db/queries/#limiting-querysets for more info.

user_165213650
Related