I have a Django app that imports data from a third-party source and allows me to map it to standardized data sets using a translation table. The translation table takes two values from the source data (manufacturer and model) and returns the model ID for the matching model in the standardized data. The reason for the translation table is that the model description isn’t 100% consistent in the source data, so I might need to map 2 or 3 model #s in the source data to a single model # in the standardized model table.

I have an interface that provides me the models in the source data that have not been matched to a model in the standardized data. I don’t currently use a foreign key field in the Django model, so what I’ve been doing to get the list to feed to the template is

mkt = MarketData.objects.all()
mapped_mdls = MktModel.objects.all().values('mfr','mdl')
mdl = mkt.values('equip_mfr','equip_model').distinct() 
    .annotate(mfr=F('equip_mfr'),mdl=F('equip_model')).values('mfr','mdl').difference(mapped_mdls) 
    .order_by('mfr','mdl')

The values…annotate…values chain is to change the field names in the source data to match the lookup table.

I am trying to add the total number of units represented by the manufacturer/model combo, which is contained in a field called “units.” Normally I would add something like .annotate(total_units=Sum("units")), but if I include this after the difference I get an error because (obviously) you can’t do the annotation after the difference, and I get an error if I include it before the difference because it means the field count is off between the querysets being compared.

Any suggestions?