# Display form containing row for each ManyToMany option and ForeignKeys

**URL:** <https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722>\
**Category:** Using the ORM\
**Created:** [February 8, 2023, 9:08pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722 "2023-02-08T21:08:15Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![gradiian](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gradiian/32/12192_2.png) [@gradiian](https://forum.djangoproject.com/u/gradiian)\
**Post date:** [February 8, 2023, 9:08pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/1 "2023-02-08T21:08:15Z")

</div>

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:

```auto
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:

```auto
+---------------------------------------+
| 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.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 8, 2023, 10:47pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/2 "2023-02-08T22:47:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![gradiian](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gradiian/32/12192_2.png) [@gradiian](https://forum.djangoproject.com/u/gradiian)\
**Post date:** [February 8, 2023, 10:52pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/3 "2023-02-08T22:52:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![gradiian](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gradiian/32/12192_2.png) [@gradiian](https://forum.djangoproject.com/u/gradiian)\
**Post date:** [February 13, 2023, 8:15pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/4 "2023-02-13T20:15:36Z")

</div>

@KenWhitesell - thanks for giving me something to think on. I went back and made a ManyToManyField on the Employee model:

```auto
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.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 14, 2023, 12:18pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/5 "2023-02-14T12:18:16Z")

</div>

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:

> [@gradiian](#):
>
> Is this an instance where I’m better off querying for all the data…

But you _don’t_ need to go so far as:

> [@gradiian](#):
>
> …using Python to organize it into a dictionary to construct the form in the template?

I think you’ll find a regular formset to be quite suitable for this.

So, since a [formset](https://docs.djangoproject.com/en/4.1/topics/forms/formsets/#formsets) 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.

---

<div class="post-metadata">

**Author:** ![gradiian](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gradiian/32/12192_2.png) [@gradiian](https://forum.djangoproject.com/u/gradiian)\
**Post date:** [February 14, 2023, 2:17pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/6 "2023-02-14T14:17:24Z")

</div>

> [@KenWhitesell](#):
>
> How might you define a form for **one** App? What might it look like for `Prog1` in your example above?

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:

```python
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:  
 ![access_example](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/a/a45f4ab7c97b7ddaa0fa760a94b3f5b634fc0010.jpeg)

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.

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 14, 2023, 3:04pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/7 "2023-02-14T15:04:02Z")

</div>

First:

> [@gradiian](#):
>
> It seems a modelformset would be appropriate…

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.

> [@gradiian](#):
>
> ```auto
> class EmployeeAppForm(forms.Form):
> app = forms.CharField(widget=forms.HiddenInput())
> access = forms.ModelChoiceField(queryset=Access.objects.all())
> status = forms.ChoiceField(choices=Status.choices)
> 
> ```

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:

```auto
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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![gradiian](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gradiian/32/12192_2.png) [@gradiian](https://forum.djangoproject.com/u/gradiian)\
**Post date:** [February 14, 2023, 3:18pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/8 "2023-02-14T15:18:12Z")

</div>

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()`?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 14, 2023, 3:40pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/9 "2023-02-14T15:40:23Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![gradiian](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gradiian/32/12192_2.png) [@gradiian](https://forum.djangoproject.com/u/gradiian)\
**Post date:** [February 14, 2023, 4:25pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/10 "2023-02-14T16:25:56Z")

</div>

**forms.py**

```python
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**

```python
# 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…).

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [February 14, 2023, 6:26pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/11 "2023-02-14T18:26:42Z")

</div>

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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![gradiian](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/gradiian/32/12192_2.png) [@gradiian](https://forum.djangoproject.com/u/gradiian)\
**Post date:** [February 14, 2023, 9:17pm UTC](https://forum.djangoproject.com/t/display-form-containing-row-for-each-manytomany-option-and-foreignkeys/18722/12 "2023-02-14T21:17:45Z")

</div>

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!