CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-views
Sreekanth Reddy Balne
Sreekanth Reddy Balne

Reputation: 3424

Count consecutive rows with same value for a column in a database using django?

My App consists of a notification module. If the notifications are consecutively arrived from a same user, I would like to show "n notifications from john doe".

eg:

The database rows are as:

id    |   user    |     notif       |
------------------------------------
1       john doe     liked your pic 
2       john doe     commented on your pic
3       james        liked your pic
4       james        commented on your pic
5       john doe     pinged you
6       john doe     showed interest
7       john doe     is busy

The above notifications are to be shown as:

2 notifications from john doe
2 notification from james
3 notofications from john doe

How would I count these consecutive rows with same value in a column using django orm?

Notification.objects.all().values('user', 'notif_count').group_consecutive_by('user').as(notif_count=Sum())

Something like that. Please help.

Upvotes: 0

Views: 363

Answers (1)

Sreekanth Reddy Balne
Sreekanth Reddy Balne

Reputation: 3424

Let my model Notification model be:


Class Notification(models.Model):
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        related_name='notifications',
        on_delete=models.CASCADE)
    notif = models.CharField(max_length=255)
    date_created = models.DateTimeField(auto_now_add=True)

The database rows are as:

id    |   user    |     notif       |
------------------------------------
1       john doe     liked your pic 
2       john doe     commented on your pic
3       james        liked your pic
4       james        commented on your pic
5       john doe     pinged you
6       john doe     showed interest
7       john doe     is busy

Basically, I am trying to join consecutive rows by user

The above notifications then are to be shown as:

2 notifications from john doe
2 notification from james
3 notofications from john doe

instead of

5 notifications from john doe
2 notification from james

or

1 notifications from john doe
1 notifications from john doe
1 notification from james
1 notification from james
1 notofications from john doe
1 notofications from john doe
1 notofications from john doe

In order to achieve this, we are looking for a dictionary like:

{
"john doe": ["notif1", "notif2"],
"james": ["notif1", "notif2"],
"john doe": ["notif1", "notif2", "notif3"] #duplicate key.
}

But, that's not possible as duplicate keys are not allowed. Hence I am going with array of tuples instead.

[
  ('john doe', ['notif1', 'notif2']),
  ('james', ['notif1', 'notif2']),
  ('john doe', ['notif1', 'notif2', 'notif3']),
]

So, we first sort the Notifications by date_created. Then we use itertools.groupby to make groups per user.

from itertools import groupby
from operator import attrgetter

qs = Notification.objects.select_related('user').order_by('date_created')
notifs= [(u, list(nf)) for (u, nf) in groupby(qs, attrgetter('user'))]

You have everything sorted as needed in notifs.

Done!

Upvotes: 1

Related Questions