Using prefetch_related to reduce expensive query-CodePudding

How can i make the querying here on the database level. in my code for assigning the emails to the list i am using python code instead i want to make use of ORM querying i.e on the database level how can my query look like here.

models.py

class UserModel(models.Model):
     email = models.EmailField(unique=True, blank=True, null=True)

class Mymodel(models.Model):
    name = models.CharField(max_length=300)

    assignees = models.ManyToManyField(
    User,
    related_name='tasks',
    blank=True,
    )
    
    send_email = models.BooleanField(default=False)

views.py

def my_view(self):
    due_date = datetime.date.today()
    records = Mymodel.objects.filter(due_date=due_date)

    for rec in records:
        if rec.send_email:
            responsible_emails = []
            for person in rec.assignees.all():
                responsible_emails.append(person.email)

CodePudding user response：

You can certainly use prefetch_related() to make the query more efficient, but the reason that your code is slow is because you have DB calls nested within a forloop. You can re-write your queries to essentially do the if-statement and for-loop logic:

def my_view(self):
    ...

    # move your if-statement to the model filter
    records = Mymodel.objects.filter(
        due_date=due_date,
        send_email=True
    ).prefetch_related('assignees')


    # loop through each record (is this necessary?)
    for record in records:
        # get a list of email addresses
        responsible_emails = rec.assignees.values_list('email', flat=True)

CodePudding user response：

You can query from the UserModel itself like this.

due_date = datetime.date.today()
UserModel.objects.prefetch_related("tasks").filter(
    tasks__send_email=True, tasks__due_date=due_date
).values_list("email", flat=True)