I’ve been having a difficult time putting this into words so I’ll try and demonstrate what I’m looking to accomplish. I’m afraid I may be overthinking the problem which has lead me to spinning my wheels.
I’m building an application for work to help us keep better track of which employees have access to which systems/applications, what level of access they have for those and their current status.
Here is a simplified version of the models I’ve built:
from django.db import models
class Status(models.IntegerChoices):
REQUESTED = 0, "Requested"
ASSIGNED = 1, "Assigned"
DISABLED = 2, "Disabled"
class Apps(models.Model):
name = models.CharField(max_length=50)
def __str__(self):
return self.name
class Employee(models.Model):
name = models.CharField(max_length=50)
apps = models.ManyToManyField(Apps, related_name="employee_apps", through="EmployeeApps", blank=True)
def __str__(self):
return self.name
class Access(models.Model):
name = models.CharField(max_length=50)
app = models.ForeignKey(Apps, on_delete=models.CASCADE)
def __str__(self):
return f"{self.name} - {self.app}"
class EmployeeApps(models.Model):
employee = models.ForeignKey(Employee, on_delete=models.CASCADE)
app = models.ForeignKey(Apps, on_delete=models.CASCADE)
access_level = models.ForeignKey(Access, on_delete=models.CASCADE)
status = models.IntegerField(choices=Status.choices)
def __str__(self):
return f"{self.employee} - {self.app}"
This does what I expect at the database level. I can create an employee, a few apps and set access levels for each app. I can create an entry in the EmployeeApps through model which the appropriate data.
The problem I’m stuck on is how I can display a form to list of all Apps, their Access levels and a status dropdown. What I’m trying to accomplish is something like this:
+---------------------------------------+
| App | Access | Status |
+---------------------------------------+
| Prog1 | User | Requested |
| Prog2 | Admin | Assigned |
| Prog3 | <....> | <....> |
...................
The App column is just text (app.name) but the Access and Status fields are drop downs. I can’t seem to customize a ModelForm or modelformset_factory() enough to do what I’d like. Is this an instance where I’m better off querying for all the data (current apps in EmployeeApps as well as all other Apps and Access levels) and using Python to organize it into a dictionary to construct the form in the template? I’d prefer to use ModelForms to take advantage of Django’s validation.
It’s not fully clear what you want this list to be.
Is it to be a list of all Apps, or just a list of the Apps related to a specific Employee?
If it’s to be for all instances of Apps, then you’re dealing with a regular formset of Apps because you want to include any instances of App not currently related to that Employee.
However, if you only want to reference those instances of Apps that are related to that Employee, then what you’re looking at is a ModelFormset. Except, what you’re looking to generate the formset on is the EmployeeApps model, not the Apps model.
Side note: The generally accepted best practices for naming tables are that tables should be named in the singular, not plural. This means it should probably be App not Apps. Or, at a minimum, you want to be consistent in your naming - which means you could also achieve this by changing Employee to Employees. Being consistent does provide some definite benefits when coming back to your code after a couple months.
Let me back up a bit here. I assumed the model I want to target is EmployeeApps but now that you say that, I think I should start with Apps.
I want a list of all apps available, regardless if they’re assigned or not to the particular employee. If they are, I’d like to show that status from the EmployeeApps model.
After banging my head on this for weeks, I hadn’t thought to start with Apps and work through the relationship to EmployeeApps and filter on the Employee that way.
Edit to your side note: I agree with that 100%. In my actual project code it is that way but I threw that example together quickly and hadn’t noticed until I posted it.
@KenWhitesell - thanks for giving me something to think on. I went back and made a ManyToManyField on the Employee model:
apps = models.ManyToManyField(App, through=EmployeeApp, related_name="employee", blank=True)
I can now start in App and, using prefetch_related(), work my way back through the relationship. I’m still lost on how I would create a formset (or a modelformset).
In the example above, I would like to show a list of every app’s name (as text, not an input field) which would come from the App table, the associated access levels from the Access table and the current status (if one exists) from the EmployeeApp table for a particular employee.
I had to think about this one a bit. I think you’re on the right track (but not quite there) with your previous comment:
But you don’t need to go so far as:
I think you’ll find a regular formset to be quite suitable for this.
So, since a formset is -
a layer of abstraction to work with multiple forms on the same page.
it’s best to look at this in terms of a form.
How might you define a form for one App? What might it look like for Prog1 in your example above?
Don’t worry about the template or anything else like that, the first step is the Django form itself.
Hmm… I’m not sure I quite understand how I could create a form like that. The App itself would need to be a HiddenInput for the sake of saving the data but I’m not sure how/if I could display that value as a text label for that particular form/row. Access/Status would be ChoiceFields, with Access requiring some filtering (in the template?) to only display options for the App.
I suppose if I were to create a form, I would do something like:
class EmployeeAppForm(forms.Form):
app = forms.CharField(widget=forms.HiddenInput())
access = forms.ModelChoiceField(queryset=Access.objects.all())
status = forms.ChoiceField(choices=Status.choices)
I threw this example together to better explain what I’m trying to do:
I imagine each row of this table would be a single form in a formset/modelformset. The first column containing each App (App.name). The second column displays values associated to the App (based on the ForeignKey) and the third column is simply the Status.
Each App would be displayed in order and if that App exists in EmployeeApp, Access and Status would be prepopulated to match those fields. In the example, Payroll and Cell Phone are not assigned and therefore are blank.
It seems a modelformset would be appropriate since I’m working with Models and storing a mix of related data in the through table.
Sorry if I seem all over the place… I’ve been playing with this idea for a little over a month and keep ending up in the same position. I know I could do what I want using Python in the view but I’d really prefer to adhere to Django’s framework and utilize the appropriate form/modelform/formsets.
First:
I believe this to be an incorrect conclusion. The issue is that you’re generating a formset on App because you want to iterate over all instances of app, but you are not making any changes to the App model itself. You’re only affecting related models. (In fact, you’ve made it clear that you don’t want any changes to be made to App.)
But I want to stay focused on the form for the moment - we’re building up toward using a formset, but the formset’s not going to make a lot of sense if we don’t understand the form we need.
This is a good start.
First, your “HiddenInput” should actually be the primary key for that model. That way, when it’s submitted, you have the PK to do the assignments.
Then, your displayed column for the app name can be a disabled text field with the app name as the value. (If app is the primary key, that’s fine. Since this is a regular form and not a model form, you’re going to be responsible for populating the initial data - which means you can (if necessary) use the same data twice for initializing two separate fields.)
This gives us then:
class EmployeeAppForm(forms.Form):
id = forms.IntegerField(widget=forms.HiddenInput())
app = forms.CharField(disabled=True)
access = forms.ModelChoiceField(queryset=Access.objects.all())
status = forms.ChoiceField(choices=Status.choices)
So, with that in mind, assume we have variables named employee and app representing the instances of Employee and App for which we want this form populated.
That means to populate the initial form, we can do something like this:
a_form = EmployeeAppForm(initial={
'id': app.id,
'app': app.name,
'access': Access.objects.get(employeeapps_app=app, employeeapps_employee=employee),
'status': EmployeeApps.objects.get(employee=employee, app=app).status
})
Are you with me so far?
Yes. That does make sense. So we would initialize the form based off a particular object from the App model and pull in data from the Access and Status models at the same time.
I may be getting ahead of myself here but wouldn’t that throw a DoesNotExist exception if an app is not in the EmployeeApp table? Would it be better to use filter().first()?
Yes, good catch. That’s true with both queries in the setup.
(As a side note and along the same lines, you probably also want to create a constraint to ensure that you never have more than one row in EmployeeApps for the same combination of employee and app.)
So now that we have identified what the individual form is to be, now you want to create the formset, with each form in that set being an instance of this form, initialized with a different instance of App.
You’ll not be using the initial attribute on the form, instead, you’ll use this to create the initial attribute for the formset.
How might you write a query to retrieve this necessary data in a queryset? (One row per EmployeeAppForm)
forms.py
class EmployeeAppForm(forms.Form):
id = forms.IntegerField(widget=forms.HiddenInput())
app = forms.CharField(disabled=True)
access = forms.ModelChoiceField(queryset=Access.objects.all())
status = forms.ChoiceField(choices=Status.choices)
views.py
# Just for this example, getting first user
employee = Employee.objects.get(pk=1)
AppFormSet = formset_factory(EmployeeAppForm)
formset = AppFormSet(initial=[
{
'id': app.id,
'app': app.name,
'access': Access.objects.filter(employeeapps_app=app, employeeapps_employee=employee).first(),
'status': EmployeeApps.objects.filter(employee=employee, app=app).first().status,
} for app in App.objects.all()
])
Based on the documentation you linked earlier, it seems that we can pass a list of dictionaries to initial and create the entire formset in one line (more or less…).
This is good!
However, it can still be improved. We can push all this work into the database and let it create what we need, using an appropriate mix of queries, subqueries, and annotations.
Consider this:
app_objects = App.objects.all().order_by('name').annotate(
access=Subquery(
Access.objects.filter(
employeeapps__app=OuterRef('id'),
employeeapps__employee=employee
).values('id')[:1]
),
status=Subquery(
EmployeeApps.objects.filter(
employee=employee,
app=OuterRef('id')
).values('id')[:1]
)
).values('id', 'app', 'access', 'status')
Note that the values function returns a list of dict, where each dict has a key of the field name and value of the value of that field - which is exactly the structure needed for the initial attribute of a formset. No further processing of that query is required.
Note: I’m winging this - there may be anywhere between 1 and 10 syntax errors here, but hopefully it gives you some ideas.
Thank you for your help! I really appreciate the way you nudged me along the way rather than just provided a solution. I’ve learned some new things and that’s very valuable. I wasn’t aware of OuterRef and Subquery - still have much to learn!
I was able to construct a queryset based on that example and get the desired result. Tomorrow, I’ll try saving the data back into the database but at least I’m starting to understand these concepts a bit better. I appreciate your help!