Hope everyone is well.
I have two tables I'm looking to join and struggling to join in a particular way.
I could easily join them with SQL but rather I would want to do it using Django.
class employee(models.Model): client = models.ForeignKey(client, on_delete=models.CASCADE) mySharePlan_ID = models.CharField(max_length=10, unique=True) payroll_ID = models.CharField(max_length=100) first_name = models.CharField(max_length=155,) middle_name = models.CharField(max_length=155,null=True, blank=True) last_name = models.CharField(max_length=155) TFN = models.IntegerField(null=True,blank=True) subsidary = models.CharField(max_length=155,null=True, blank=True) divison = models.CharField(max_length=155,null=True, blank=True) job_title = models.CharField(max_length=155,null=True, blank=True) tax_rate = models.FloatField(null=True,blank=True) hire_date = models.DateField(null=True,blank=True) terminiaton_date = models.DateField(null=True,blank=True) termination_reason = models.CharField(max_length=155, blank=True) rehire_date = models.DateField(null=True,blank=True) created_on = models.DateTimeField(auto_now_add=True) updated_on = models.DateTimeField(auto_now=True) updated_by = models.ForeignKey(settings.AUTH_USER_MODEL,on_delete=models.DO_NOTHING) class Meta: unique_together = ('client', 'payroll_ID',) def __str__(self): full_name = "Payroll ID: " + self.payroll_ID + ", " + self.first_name + " " + self.last_name return full_name
class offer_eligibility(models.Model): offer = models.ForeignKey(offer,on_delete=models.CASCADE) employee = models.ForeignKey(employee,on_delete=models.CASCADE) amount_offered = models.PositiveBigIntegerField() created_on = models.DateTimeField(auto_now_add=True) updated_on = models.DateTimeField(auto_now=True) updated_by = models.ForeignKey(settings.AUTH_USER_MODEL,on_delete=models.DO_NOTHING)
Any employee can have many offers.
I'm trying to create a view that shows a list of employees who are in a selected offer and if they have any amount_offered.
This requires me to first filter the offer_eligibility by the offer, I have this queryset.
I want to add that queryset onto the employee queryset (filtered by client, easy enough to filter) and where the employee does not exist in the queryset I want amount_offered to be None/null.
Currently I cannot join the parent data onto the child data, I've tried a few different things but in raw SQL this is fairly trivial, so I'm thinking I am clearly not understanding something.
Any help is greatly appreciated.
Thanks in advance, Tom
I got it! after all day trying to work it out.
I feel I have a lot better understanding of how Django writes SQL now.
Filtering the model queryset
def get_queryset(self): #Filter queryset for client queryset = super(FilterView, self).get_queryset() context = selected_client(self.request) queryset = queryset.filter(client=context['selected_client']).annotate( amount_offered=Case( When( offer_eligibility__offer = self.kwargs['pk'] ,then=F('offer_eligibility__amount_offered')) ,default=None)) return queryset
class offer_eligibility_table(ExportMixin,tables.Table): client_name = tables.Column(accessor='client__client_name') mySharePlan_ID = tables.Column(accessor='mySharePlan_ID') payroll_ID = tables.Column(accessor='payroll_ID') first_name = tables.Column(accessor='first_name') middle_name = tables.Column(accessor='middle_name') last_name = tables.Column(accessor='last_name') hire_date = tables.Column(accessor='hire_date') amount_offered = tables.Column(accessor='amount_offered')
So to walk you through the answer, I first needed to filter on client, then I need to add a column to the original model which was my amount_offered but I only wanted to add the values if the employee was in the offer, as such I needed to use the case function, definitely recommend giving conditional-expressions a read if you are looking at my question and wanting similar help.