Django Query - Get list that isnt in FK of another model

user_14143473 0
Viewed 35

I am working on a django web app that manages payroll based on reports completed, and then payroll generated. 3 models as follows. (ive tried to limit to data needed for question).

class PayRecord(models.Model):
    rate = models.FloatField()
    user = models.ForeignKey(User)

class Payroll(models.Model):
    company = models.ForeignKey(Company)
    name = models.CharField()

class PayrollItem(models.Model):
    payroll = models.ForeignKey(Payroll)
    record = models.OneToOneField(PayRecord, unique=True)

What is the most efficient way to get all the PayRecords that aren't also in PayrollItem. So i can select them to create a payroll item.

There are 100k records, and my initial attempt takes minutes. Attempt tried below (this is far from feasible).

records_completed_in_payrolls = [
        p.report.id for p in PayrollItem.objects.select_related(
                'record',
                'payroll'
        )
    ]
django-queryset django-models django
1 Answers

Because you have the related field record in PayrollItem you can reach into that model while you filter PayRecord. Using the __isnull should give you what you want.

PayRecord.objects.filter(payrollitem__isnull=True)

Translates to a sql statement like:

SELECT payroll_payrecord.id, 
   payroll_payrecord.rate, 
   payroll_payrecord.user_id
FROM payroll_payrecord
   LEFT OUTER JOIN payroll_payrollitem 
       ON payroll_payrecord.id = payroll_payrollitem.record_id
WHERE payroll_payrollitem.id IS NULL

Depending on your intentions, you may want to chain on a .select_related (https://docs.djangoproject.com/en/3.1/ref/models/querysets/#select-related)

PayRecord.objects.filter(payrollitem__isnull=True).select_related('user')

which translates to something like:

SELECT payroll_payrecord.id,
       payroll_payrecord.rate,
       payroll_payrecord.user_id,
       payroll_user.id,
       payroll_user.name
FROM payroll_payrecord
         LEFT OUTER JOIN payroll_payrollitem
             ON (payroll_payrecord.id = payroll_payrollitem.record_id)
         INNER JOIN payroll_user
             ON (payroll_payrecord.user_id = payroll_user.id)
WHERE payroll_payrollitem.id IS NULL
user_48721400
Related