Django get distinct based on rows fields group
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(
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)