Django get distinct based on rows fields group

user_12814331 0
Viewed 28

I have a model which stores mail logs. I want to send one primary email and two reminder emails. I have separated the email logs based on the type. (first_email, first_reminder_email, second_reminder_email) What is wanted is the records to which we have sent all three emails.

My model

class MailLog(models.Model):

    MAIL_TYPE = (
        ('first_email', 'First Email'),
        ('first_reminder_email', 'First Reminder Email'),
        ('second_reminder_email', 'Second Reminder Email'),
    )
    user = models.ForeignKey('user.User', on_delete=models.CASCADE, blank=True, null=True, related_name='user_mail_log')
    category = models.IntegerField()  #  to store category
    mail_type = models.CharField(choices=MAIL_TYPE, max_length=100)

I want to group by category, user and mail_type (should have all three status)

Sample data

| id | user | category | mail_type |
----------------------------------- 
| 1 |  20   | 4        | first_email |
| 2 |  20   | 4        | first_reminder_email |
| 3 |  20   | 4        | second_reminder_email |
| 4 |  20   | 5        | first_email |
| 5 |  22   | 4        | first_email |
| 6 |  22   | 4        | first_reminder_email |

I tried couple of approach

MailLog.objects.filter(Q(category__id=6) & Q(mail_type='first_email') & Q(mail_type='first_reminder_email') & Q(mail_type='second_reminder_email') )


MailLog.objects.values_list('user', 'category').distinct(
postgresql django python database
1 Answers

You can use Count with Concat.

Assuming the user_id, mail_type and category are not unique together:

from django.db.models import Count
from django.db.models.functions import Concat

MailLog.objects
    .filter(category=1)
    .values('user_id')
    .annotate(unique_mail_type_count=Count(Concat('user_id', 'mail_type'), distinct=True))
    .filter(unique_mail_type_count=3)
user_120406550
Related