Django: Limiting foreign key choices based on related object

Question

I'm not sure of the correct terminology to explain what I need, I think the easiest way is by example.

I have the following models - a company and a software asset.

class Company(models.Model):
    name = models.CharField(max_length=200)

class SoftwareAsset(models.Model):
    name = models.CharField(max_length=200)

I would then like to map various companies to the software assets they own. For example, most companies may own the 'Microsoft Office' asset, but only a few may own 'Adobe Photoshop'. I use a join table for this (again, I'm not sure if this is the correct term, or more importantly, the correct way of going about this).

class CompanyAssets(models.Model):
    company = models.ForeignKey(Company)
    asset = models.ForeignKey(SoftwareAsset)

I then need to define employees along with the company they are employed by:

class Employee(models.Model):
    name = models.CharField(max_length=200)
    company = models.ForeignKey(Company)

And finally, I need to define what applications each employee has access to:

class EmployeeSoftware(models.Model):
    employee = models.ForeignKey(Employee)
    asset = models.ForeignKey(SoftwareAsset)

Now, all this works fine, with one exception. When I'm in the admin interface, and I add records to the EmployeeSoftware table, I'm able to select SoftwareAssets which the Company does not own. The SoftwareAsset dropdown allows me to select any software package defined in the SoftwareAsset table. I would like to limit this to assets owned by the Company, as defined in CompanyAssets.

My preference would be to do this in the models - if it's possible to prevent assigning an employee an asset the company doesn't own at the database level, but from the reading I've done this is not possible. I've messed around with the ForeignKey.limit_choices_to argument but didn't have much luck.

I tried editing admin.py for the EmployeeSoftwareAdmin class using formfield_for_foreignkey:

class EmployeeSoftwareAdmin(admin.ModelAdmin):

    def formfield_for_foreignkey(self, db_field, request, **kwargs):

        if db_field.name == 'asset':
           kwargs['queryset'] = CompanyAssets.objects.filter(company__name="XXXXX") #works but is obviously static 

    return super(EmployeeSoftwareAdmin,self).formfield_for_foreignkey(db_field,request,**kwargs)

I have not been able to find a way to access any object within the formfield_for_foreignkey method that would allow the correct filtering to happen.

Answer 1

Give the SoftwareAsset an FK to Company.

class SoftwareAsset(models.Model):
    name = models.CharField(max_length=200)
    company = models.ForeignKey(Company)

So a company can own many software assets. Now you can easily filter the software assets in your custom modelform.

EmployeeSoftwareForm(forms.ModelForm):
    class Meta:
        model = EmployeeSoftware

    def __init(self, *args, **kwargs):
        super(EmployeeSoftwareForm, self).__init__(*args, **kwargs)
        if 'instance' in kwargs:
        self.fields['assets'].queryset = SoftwareAsset.objects.filter(company = kwargs['instance'].employee.company)

Finally use this custom form for the EmployeeSoftware modeladmin:

class EmployeeSoftwareAdmin(admin.ModelAdmin):
    form = EmployeeSoftwareForm

This eliminates the need for the CompanyAsset model.

UPDATE: OK, you want each new instance of EmployeeSoftware to know what assets it can have, based on the employee company. But because you dont know what your employee is until you select it, its impossible to do on form init. You have to use javascript to filter the select based on the employee choice. Something like this (using JQuery):

$("#employee").change(function(){

    $.post(<your assets select url>, $("employeeform").serialize(), function (data) {
    //populate your returned JSON into the asset select
}, 'json');

})

Obviously, the url your post goes to filters the assets based on the selected employee, which should be simple.