Foreign key with complex relationship

Using Django Using the ORM
1

I’m actually just looking for some guidance here and possibly a place to start. I’m trying to setup the proper relationship from one table to two other tables. here is the example (simplified for this forum post)

class Employee(models:Model):
    agency = Models.ForeignKey(Agency, on_delete=models.CASCADE)
    last_name = models.CharField(max_length=100)
    first_name = models.CharField(max_length=100)

class Departments(models.Model):
    agency = models.ForeignKey(Agency, on_delete=models.CASCADE)
    name = models.CharField(max_length=100)

class EarningsTypes(models.Model):
    agency = models.ForeignKey(Agency, on_delete=models.CASCADE)
    name = models.CharField(max_length=100)

class EmployeeEarningsTemplate(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    department = models.ForeignKey(Departments, on_delete=models.CASCADE)
    earnings_type = models.ForeignKey(EarningsTypes, on_delete=models.CASCADE)

For the final class, EmployeeEarningsTemplate, I need the department to only include departments for the agency in which this employee is related. Similarly, I need the earnings_type to only include earnings types for the agency of the employee.

I’m not sure how I can actually state these relationships through the model. If you can point me to the right direction, I would appreciate it. I’ve been looking through the Django documentation in the Models section, but I haven’t really found anything similar to what I’m trying to do here.

Thanks!
Bob

2

In the absence of the complete description and requirement for the entities involved here, it seems to me that there is no direct relationship that needs to be defined.

There’s no need to define a relationship in EmployeeEarningsTemplate, because you can retrieve the department and earnings_type from the employee.

For any specific instance of EmployeeEarningsTemplate, employee is a reference to Employee, employee.agency is the Agency they are related to, and employee.agency.departments_set.all() is the set of Departments related to the Agency related to that Employee.

Likewise, employee.agency.earningstype_set.all() is the set of EarningTypes.

See Related objects reference | Django documentation | Django for more details on this.

Trying to define these relationships directly in a model is redundant and creating the possibility of inconsistent data.

3

Okay, I will check your links for more details.

However, assuming I don’t have to change anything, what makes me think the relationships may be wrong is how the Admin console is showing the available departments.

Django_Admin_Earnings_Template
Django_Admin_Earnings_Template782×757 94.8 KB

As you can see here, it shows ALL departments, not just the departments associated with the Agency in which this employee belongs (as evidence, there is only one Executive department setup for this agency).

Maybe I shouldn’t be too concerned with how the Admin Console is rendering this, but it did make me consider that the relationships were not correct.

Thanks Ken!

Bob

4

Okay, so after reading your reply again, I think what you are saying is that the relationships are stated correctly, however, in terms of providing a selection of departments for the employee, I need to make sure I am using employee.agency.departments_set.all(), correct?

So I added this to my model form as follows:

    def __init__(self, *args, **kwargs):
        super(ModelForm, self).__init__(*args, **kwargs)
        self.fields['department'] = (
            DepartmentModelChoiceField(queryset=Employee.agency.departments_set.all().order_by('department'),
                                       empty_label='(select department)',
                                       widget=forms.Select(
                                        attrs={'style': 'width: 200px;'})))

However, when I try to render the form, I get the error AttributeError at /personnel/employee-earnings/83ec3f21-fda3-478e-8d85-af117bdeb5c1/ 'ForwardManyToOneDescriptor' object has no attribute 'departments_set'

How do I define departments_set?

5

Not quite.

It wasn’t clear to me from your original post that the real goal wasn’t to address the model itself, but to define the limitations of the select field of a form.

Now, regarding this specific situation:

The problem here is that Employee is the model - it’s not an instance of the model, and so the expression is not valid.

You have the situation where the employee field is a selectable item, which means that Django is unable to evaluate that expression at the time the form is generated. (If the Employee was previously identified, then the expression would be employee.agency.department_set.order_by('department').)

But in this case where the Employee itself is a field, Django has no way to know how to generate the option list when there is no identified Employee.

It seems to me that what you’re trying to do here is to have the Department select options be dynamically updated based upon what was selected for the employee - something you’ll see referred to as “chained”, “dependent” or “cascading” select lists.

This is going to involve some degree of JavaScript such that when the Employee is set/changed, it calls a view to get the list of Departments for that employee, then updates the select list with the data retrieved by that view.

This can be done a couple different ways - There’s the Django-Select2 package that can do this directly, or it can also be done with HTMX, or you can do it with native JavaScript if you’re so inclined.

Regardless of how you do this with the UI, you will still need to update the choice field in the form for when the form is submitted to ensure that it is validated correctly.

6

Okay, thanks for the information Ken, I’ll look into the other options that you mention.

This is the type of complexity that often comes with Tenant-Based web applications!

7

Okay, just following up on this, I was able to get this to work by passing the Employee object to the form as an argument. Doing this I was able to establish the Department list and Earnings Type list that correspond to the agency of the user. Using this method, the form shows the correct information in the selection lists.

Here is the code…

class EmployeeEarningsForm(ModelForm):
    class Meta:
        model = EmployeeEarningsTemplate
        fields = ['department', 'earnings_type', 'hours', 'rate']
        labels = {
                  'department': 'Department',
                  'earnings_type': 'Earnings Type',
                  'hours': 'Hours',
                  'rate': 'Rate',
        }

    def __init__(self, employee, *args, **kwargs):
        super(ModelForm, self).__init__(*args, **kwargs)
        self.fields['department'] = (
            DepartmentModelChoiceField(queryset=Departments.objects.filter(agency=employee.agency),
                                       empty_label='(select department)',
                                       widget=forms.Select(attrs={'style': 'width: 200px;'})
                                       )
        )
        self.fields['earnings_type'] = (
            EarningsTypeModelChoiceField(queryset=EarningsTypes.objects.filter(agency=employee.agency),
                                         empty_label='(select type)',
                                         widget=forms.Select(attrs={'style': 'width: 200px;'})
                                         )
        )


class EarningsTypeModelChoiceField(forms.ModelChoiceField):
    def label_from_instance(self, obj):
        self.widget_attrs({'style', 'width:200px;'},)
        return '%s' % obj.name


class DepartmentModelChoiceField(forms.ModelChoiceField):
    def label_from_instance(self, obj):
        self.widget_attrs({'style', 'width:200px;'},)
        return '%s' % obj.name

I probably should have mentioned earlier, that from the outset, I have the employee object. This will not be a situation where the user will select the employee and the model-choices will change. I’m getting to the view with the PK of the employee object.

As I mentioned, this does render properly and only shows departments and earnings types that are associated with the Agency that the employee is related to.

Now, the issue I’m having is during the save of the form. This is the view that receives the updated form.

def employee_earnings(request, pk):
    employee = Employee.objects.get(pk=pk)
    earnings = EmployeeEarningsTemplate.objects.get(employee=employee)
    form = EmployeeEarningsForm(employee=employee, instance=earnings)
    if request.method == 'POST':
        form = EmployeeEarningsForm(request.POST, employee=employee, instance=form.instance)
        if form.is_valid():
            earnings_entry = form.save(commit=False)
            earnings_entry.employee = employee
            earnings_entry.save()
            return redirect('employee_list')
    context = {'employee_id': pk,
               'form': form}
    return render(request, 'PayrollPersonnel/employee_earnings.html', context)

When I get to the line after if request.method == 'POST', I get the following error.

TypeError at /personnel/employee-earnings/83ec3f21-fda3-478e-8d85-af117bdeb5c1/
EmployeeEarningsForm.__init__() got multiple values for argument 'employee'

I’m passing the current employee to the form instantiation, but maybe the employee already exists? However, I’ve also tried removing the employee argument and I get this error…

AttributeError at /personnel/employee-earnings/83ec3f21-fda3-478e-8d85-af117bdeb5c1/
'QueryDict' object has no attribute 'agency'

I’m guessing it can’t find the agency because that comes from the employee, and I am not sending the employee argument in this version.

I’m not sure which method (with or without the employee argument) is correct, but I’m stuck at this point.

Any ideas on what I might be missing?

Thanks!!

8

Side note: In the future, when requesting assistance with an error message, please post the complete traceback.

Your override of the __init__ signature is going to assign request.POST to what your function defines as employee.

I would suggest defining employee as a keyword parameter in the function signature.

Also:

This like is likely to cause an error in the future as it is using a “get” call on a query that can return multiple rows.

9

Thanks for the reply.

From what I can tell I am using keyword parameters when creating the form instance after the post, unless I am not specifying this correctly.

    if request.method == 'POST':
        form = EmployeeEarningsForm(request.POST, employee=employee, instance=form.instance)

I hear what you are saying about using filter instead of get, but the thing is, this can very well return multiple rows. The idea behind this model is that for any employee, they can have multiple earnings entries in the template which will be the basis for the employee earnings section in any newly created payroll. For example, they might work 32 hours in Department A and 8 hours in Department B, so that would be the template for the employee.

I’m not sure what other ramifications exists for using GET but the idea is to use model formset factory to give the user multiple rows that can be edited. The code doesn’t completely reflect that now because I’m trying to get to the point where I can edit and save a single entry.

10

[Emphasis added]

That means here:

See the docs for Keyword Arguments for more details on this.

11

In which case, get will throw an error. By definition, get is to return exactly one row. See the docs for get() for more details on this.

12

Okay, at this point I am totally lost on this. After reading up on keyword arguments, it seems like as long as the required, positional parameters are specified in order, the keyword arguments can be specified by name, so in this definition…

def __init__(self, employee, *args, **kwargs):

and called using…

form = EmployeeEarningsForm(employee=employee, instance=earnings)

definitely works because in the resulting form, I am only seeing the departments for this employee as I should, instead of all departments for any agency.

However, the call after a post…

    if request.method == 'POST':
        form = EmployeeEarningsForm(request.POST, employee=employee, instance=form.instance)

causes the error, even though the employee parameter is being specified as a keyword argument. The only difference between the calls is that in the second example, I am passing the request.POST argument first.

It sounds like you are saying that I need to make a change to the init definition to formally define employee as a keyword argument, but I’m not sure how to do that, and in the example that you linked, it doesn’t really mention anything about the function definition, but rather the function call.

Can you give me an example of how I would change the init_ call to classify the employee parameter as a keyword argument?

I may have to totally rethink this interface to be more step-wise, rather than presenting the user a single form to select a department and earnings type. Select the department, [Continue], select the Earnings type, [Continue], enter the rate and hours, and [Save]. This just seems very clumsy though.

As always, very appreciative of any help, feedback and examples.

13

See the function definition for parrot.

Also, see the section on Special Parameters further down on the same page.

You might want to spend some time becoming more familiar with how Python allows you to define functions and how those function are called.

So, for your situation, it would be something like:
def __init__(self, *args, employee=None, **kwargs):

14

Yes, that was it!!

I did see the parrot example, but I couldn’t figure out how to specify a default value for employee.

employee=None

did the trick. It is now properly saving the selected department.

Thanks for all of your help with this issue, I really appreciate it.

15

Okay, just when I thought I was all set…

I was able to render the form for editing an existing entry (which was added through the Admin console), but now I’m trying to add a new Earnings Template entry.

Just to recap, this is the model:

class EmployeeEarningsTemplate(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
    department = models.ForeignKey(Departments, on_delete=models.CASCADE)
    earnings_type = models.ForeignKey(EarningsTypes, on_delete=models.CASCADE)
    hours = models.DecimalField(max_digits=7, decimal_places=2, default=0)
    rate = models.DecimalField(max_digits=9, decimal_places=3, default=0)
    date_created = models.DateTimeField(auto_now_add=True)
    sort_order = models.IntegerField(default=100)
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False, unique=True)

    def __str__(self):
        return f'{self.employee.last_name}, {self.department.name}, {self.earnings_type.abbreviation}'

This is the form:

class EmployeeEarningsForm(ModelForm):
    class Meta:
        model = EmployeeEarningsTemplate
        fields = ['department', 'earnings_type', 'hours', 'rate']
        labels = {
                  'department': 'Department',
                  'earnings_type': 'Earnings Type',
                  'hours': 'Hours',
                  'rate': 'Rate',
        }

    def __init__(self, *args, employee=None, **kwargs):
        super(ModelForm, self).__init__(*args, **kwargs)
        self.fields['department'] = (
            DepartmentModelChoiceField(queryset=Departments.objects.filter(agency=employee.agency),
                                       empty_label='(select department)',
                                       widget=forms.Select(attrs={'style': 'width: 200px;'})
                                       )
        )
        self.fields['earnings_type'] = (
            EarningsTypeModelChoiceField(queryset=EarningsTypes.objects.filter(agency=employee.agency),
                                         empty_label='(select type)',
                                         widget=forms.Select(attrs={'style': 'width: 200px;'})
                                         )
        )


class EarningsTypeModelChoiceField(forms.ModelChoiceField):
    def label_from_instance(self, obj):
        self.widget_attrs({'style', 'width:200px;'},)
        return '%s' % obj.name


class DepartmentModelChoiceField(forms.ModelChoiceField):
    def label_from_instance(self, obj):
        self.widget_attrs({'style', 'width:200px;'},)
        return '%s' % obj.name

Here is the template:

    <form method="post" action="{% url 'employee_earnings_new' employee.id %}">
        {% csrf_token %}
        {{ form.as_p }}
        <button>Submit</button>
    </form>

And this is the view used to add a new earnings template entry:

def employee_earnings_new(request, pk):
    employee = Employee.objects.get(pk=pk)
    form = EmployeeEarningsForm(employee=employee)
    if request.method == 'POST':
        form = EmployeeEarningsForm(request.POST, instance=form.instance, employee=employee)
        form.save(commit=False)
        form.employee = employee
        form.save(commit=True)
        return redirect('employee_earnings', pk=pk)
    context = {
        'form': form,
        'employee': employee,
    }
    return render(request, 'PayrollPersonnel/employee_earnings_new.html', context)

The form renders correctly and I can select a department and an earnings type, as well as enter the hours and the rate. However, when I go to submit the edited form for saving I get the following error.

Django_Admin_Earnings_Template_03
Django_Admin_Earnings_Template_031886×1587 236 KB

Django_Admin_Earnings_Template_04
Django_Admin_Earnings_Template_041698×1703 471 KB

I’m passing the employee object to the form so I’m not sure why it is not finding it. I also commented out these line…

        # form.save(commit=False)
        # form.employee = employee

but it didn’t make a difference.

I have no idea why I’m getting this error.

16

Okay, I have a new development on this.

I am able to get the creation of a new entity to work, but only if I add the employee field to the form. When I do, it lets me select the employee, however, this is not what I want to happen.

When I open the form template through the view, I already have the employee object, and I’m passing that as a pk, so I don’t want the user to select it. Also, the user can select any employee, even employees from other agencies, which is not good.

If I don’t include the employee in the form I get the error shown in the previous post.

What I’m trying to do is to allow the edit and entry of an EmployeeEarningsTemplate entry, only asking the user for the Department, the Earnings type, the hours and the rate. How is it that the employee object which I am adding to the new entry is not being recognized by the EmployeeEarningsTemplate object?

Here is the code in the view for new entries:

def employee_earnings_new(request, pk):
    employee = Employee.objects.get(pk=pk)
    form = EmployeeEarningsForm(employee=employee)
    if request.method == 'POST':
        form = EmployeeEarningsForm(request.POST, instance=form.instance, employee=employee)
        form.save(commit=False)
        form.employee = employee
        form.save(commit=True)
        return redirect('employee_earnings', pk=pk)
    context = {
        'form': form,
        'employee': employee,
    }
    return render(request, 'PayrollPersonnel/employee_earnings_new.html', context)

Here is the code in the view for editing entries:

def employee_earnings_edit(request, pk):
    earnings = EmployeeEarningsTemplate.objects.get(pk=pk)
    employee = Employee.objects.get(pk=earnings.employee.id)
    form = EmployeeEarningsForm(employee=employee, instance=earnings)
    if request.method == 'POST':
        form = EmployeeEarningsForm(request.POST, instance=form.instance, employee=employee)
        if form.is_valid():
            form.save()
            return redirect('employee_earnings', pk=employee.id)
    context = {
        'form': form,
        'employee': employee,
    }
    return render(request, 'PayrollPersonnel/employee_earnings_edit.html', context)

These are both working now, but only if I include the employee field on the form.

17

Re-review the docs for The save() method, particularly the examples when using commit=False.

18

Yes, I didn’t actually need to use commit=False. I’m pretty sure I was just trying that in case it was somehow losing track of the employee for the entry.

However, I still have the issue where the employee field has to be in the template, or the form does not save correctly. I figured out how to default the value to the employee passed to the view and form for a new entry, but it has to be on the form. Even if I could make the resulting dropdown for the employee read-only or hidden, that would be fine. As it is now, it can be changed by the user, but shouldn’t be.

Anyway to add a field to a form as read-only or hidden? I tried…

self.fields['employee'].widget.attrs['readonly'] = True

in the model form, but the field can still be changed in the form. It doesn’t render as read-only.

19

No, actually you do - or I should say you need to use it to do this properly. What you need to review is how to use it.

20

Thanks I’ll look into it a bit deeper. I’ve used it in other areas to assign a foreign key value that is not part of the data model, but in this case I didn’t need it because employee is part of the model.