Django.fun

Django ORM, parent and child connecting

Django ORM

G'day all,

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.

Models below;

Child:

    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

Parent:

    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'][1]).annotate(
                amount_offered=Case(
                When( offer_eligibility__offer = self.kwargs['pk'] 
                ,then=F('offer_eligibility__amount_offered'))
                ,default=None))
        return queryset 

My Django-tables2:

    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.

https://docs.djangoproject.com/en/4.0/ref/models/conditional-expressions/