Is there a way to do the equivalent of a full outer join in Django (I think I've read that full outer joins are not supported).
My scenario is that I have three tables:
Staff / WeekList / WeeksCompleted
The relevant fields I'm trying to work with are:
- Staff table - Staff Number.
- WeekList table - Week Start date.
- WeeksCompleted table - Week Start date and Staff Number.
Basically, everyone should have an entry in the WeeksCompleted table (if they're still active, that is, but that's not pertinent for this question). The queryset I would like to produce is a list of Staff who have missing weeks in the WeeksCompleted table.
I can get the result I want using SQL queries but it involves a full outer join on the Staff and WeekList tables. I was wondering if anyone knows of a way to do this using the queryset functions?
The only other way I can think to do the equivalent of the full join is to create a list using a nested loop of Staff Numbers against each week, which might have a sizeable processing overhead?
EDIT: if it helps, here are the three simplified models.
models.py
class Staff(models.Model):
staff_number = models.CharField(max_length=9, null=True)
class WeekList(models.Model):
week_start = models.DateField(null=True)
class WeeksCompleted(models.Model):
staff = models.ForeignKey(to='weekscompleted.Staff', null=True, on_delete=models.PROTECT)
week_list = models.ForeignKey(to='weekscompleted.WeekList', null=True, on_delete=models.PROTECT)
EDIT 2: The join I think I need is:
SELECT staff_number, week_start
FROM Staff, Contractor
GROUP BY staff_number, week_start
This will give a list of the expected weeks completed for staff:
week_start staff_number
17/10/2020 12345
17/10/2020 54321
I can then compare this to the WeeksCompleted table:
week_start staff_number
17/10/2020 12345
to find which staff are missing for a week using this query (keep in mind that this is a query I produced in a database):
SELECT qryShouldBeCompleted.week_start, qryShouldBeCompleted.staff_number
FROM qryShouldBeCompleted
LEFT JOIN WeeksCompleted ON qryShouldBeCompleted.staff_number =
WeeksCompleted.staff_number
AND qryShouldBeCompleted.week_start = WeeksCompleted.week_start
WHERE WeeksCompleted.staff_number Is Null
This would then produce the result I need:
week_start staff_number
17/10/2020 54321
Edit 3:
I just found an article on FilteredRelation that gets me partway there:
Staff.objects.annotate(missing=FilteredRelation('weekscompleted', condition=Q(weekscompleted__week_start='some date'))).values('staff_number', 'missing__staff__staff_number', 'missing__week_start')
which gets me this:
{'staff_number': '54321', 'missing__staff__staff_number': None, 'missing__week_start': None}
The only thing with this is that it only appears to work for one week at a time - using __lte in the condition doesn't return any 'None' values so I'd have to loop through each week...