[Answered ]-Django Query – Get list that isnt in FK of another model

by

1👍

✅

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
👤AMG

Leave a comment