1

I have following models:

class Topic(models.Model):
    title = models.CharField(max_lenght=32)
    # Some other fields.

class Thread(models.Model):
    topic = models.ForeignKey(Topic, related_name=threads', on_delete=models.CASCADE)
    # some other fields

class Message(models.Model):
    thread = models.ForeignKey(Thread, related_name='messages', on_delete=models.CASCADE)
    text = models.TextField()
    created = models.DateTimeField(auto_now_add=True)

I want to calculate index of each element in each Thread, in the queryset by F(). For example If in the Thread 1 I have 5 messages, I want to messages have indexes as 1 to 5.
my code doesn't work. code comes in the following:

from django.models import Count, Q, F
messages = Message.objects.filter(...).annotate(
    index=Count('id', filter=Q(thread_id=F('thread_id'), created__lt=F('created'))) + 1
).annotate(
    page=F('index') / 20
)

This return same index for all elements. For example this returns index=5 for all items in queryset.
How can I calculate index of elements in the queryset?

UPDATE:
Consider following:
I have 1000 Messages.
page size = 20.
number of messages pages = 1000 / 20 = 50

Now if I filter searched = Message.objects.filter(text__contains="hello"), 5 messages will be returned in the searched queryset.
My ultimate goal is to find each message is in which page? (I have 50 pages)

msln
  • 1,318
  • 2
  • 19
  • 38
  • Do you only need to perform numbering in the template? Because if all you want is numbering then doing that in a query won't make much sense (as the answer below suggests you would need to use Window functions, which honestly would be overkill for _only_ numbering) – Abdul Aziz Barkat May 20 '21 at 14:30
  • Yep, enumeration's enough if it's just for display – Tim Nyborg May 20 '21 at 14:37
  • @AbdulAzizBarkat I want to use this `index` field for some other calculations. It's not for display. – msln May 20 '21 at 15:05

1 Answers1

1

If you want this done by the database, you want to look into Window functions, specifically the Rank function (https://docs.djangoproject.com/en/3.2/ref/models/database-functions/#rank), which allows you to assign each row a number according to a grouping (like by thread)

This might do the trick:

from django.db.models import Window, F
from django.db.models.functions import Rank

index = Window(
    expression=Rank(),
    order_by=F('created'),
    partition_by=F('thread'),
)
Message.objects.annotate(index=index)
Tim Nyborg
  • 1,599
  • 1
  • 9
  • 16
  • Adding following did not solve the problem. it returns index from 1 to n, while first item index should be 5 (first item in query set is the message number 5 in the Message.objects.all()) – msln May 20 '21 at 15:32
  • You want the opposite order? Then try out order_by=F('created').desc() – Tim Nyborg May 20 '21 at 15:51
  • I updated the question description. Please take a look at update section at the bottom of the question. Thanks a lot. – msln May 20 '21 at 15:52
  • Sorry, I'm not understanding how that's different from the answer above. You can add an .order_by() to your queryset if you want to order your results from newest to oldest (5 -> 1) – Tim Nyborg May 20 '21 at 15:58
  • Probelm is not because of ordering. Your code calculates an index for each of the elements in the queryset correctly. But, it calculates the index of the element in the queryset, not in the Message.objects.filter(created__lt=item.created). Is there any way to write the UPDATE section with ORM? – msln May 20 '21 at 16:01
  • Maybe it is because you do first the filtering and then the annotate? You do that in the code you posted. If you want to have a global index (as in Message.objects.all()), maybe you should do first the annotate as @TimNyborg suggests, and then do the filter. – Marc Compte May 28 '21 at 22:24