django-fdict

Dictionary mappings in the database.

Introduction

In databases, one typically uses JOINs to perform mappings. Imagine for example we have a database of products, then we can add the vat regime, and make a JOIN:

from django.db import models

class VATRate(models.Model):
    name = models.CharField(max_length=32, unique=True)
    pct = models.FloatField()

class Product(models.Model):
    name = models.CharField(max_length=255)
    vat_rate = models.ForeignKey(VATRate, on_delete=models.PROTECT)

and then we can annotate by using a JOIN, like:

Product.objects.annotate(vat_pct=F('vate_rate__pct'))

or we can load the entire VATRate object along each product with .select_related(..):

Product.objects.select_related('vate_rate')

and then fetch it through .vat_rate.

Occasionally, we might want to perform mappings with a Python dictionary. This might be the case when we have no control over the database tables, and the vat rate is missing.

If for example the Product contains a CharField with the vat rate:

class Product(models.Model):
    name = models.CharField(max_length=255)
    country = models.CharField(max_length=2)
    country_of_origin = models.CharField(max_length=2)
    vat_rate = models.CharField(max_length=32)

We might want to perform a dictionary mapping with:

# will *not* work

vat_rates = {'R03': 0.21, 'R02': 0.12, 'R01': 0.06}

Product.annotate(vat_pct=vat_rates[F('vat_rate')])

This is the problem django-fdict wants to solve. We first wrap the entries in an FDict, which is a subclass of dict:

# *hopefully* works

from django_fdict import FDict

vat_rates = FDict({'R03': 0.21, 'R02': 0.12, 'R01': 0.06})

Product.annotate(vat_pct=vat_rates[F('vat_rate')])

this will then set an attribute vat_pct to the

Usually mapping just to get an attribute is not a good idea. It is mainly done for further filtering in the queryset, like:

# *hopefully* works

from django_fdict import FDict

vat_rates = FDict({'R03': 0.21, 'R02': 0.12, 'R01': 0.06})

Product.annotate(vat_pct=vat_rates[F('vat_rate')]).filter(vat_pct__range=(0.10, 0.15))

Multiple fields

One can also apply the trick with two or more fields. Then FDict is given a dictionary where the keys are tuples, for example:

# *hopefully* works

from django_fdict import FDict

vat_rates = FDict({('R03', 'BE'): 0.21, ('R02', 'BE'): 0.12, ('R01', 'BE'): 0.06, ('R03', 'FR'): 0.2, ('R02', 'FR'): 0.1})

Product.annotate(vat_pct=vat_rates[F('vat_rate'), F('country')]).filter(vat_pct__range=(0.10, 0.15))

which normally fetches the VAT rates based on the vat rate label, and the country code.

One can also pass non-F values in the lookups, then it will first filter down the dictionary:

# *hopefully* works

from django_fdict import FDict

vat_rates = FDict({('R03', 'BE'): 0.21, ('R02', 'BE'): 0.12, ('R01', 'BE'): 0.06, ('R03', 'FR'): 0.2, ('R02', 'FR'): 0.1})

Product.annotate(vat_pct=vat_rates[F('vat_rate'), 'BE']).filter(vat_pct__range=(0.10, 0.15))

now we thus always use BE (Belgium) as country in the dictionary lookups.

Query-like keys/values

Strictly speaking the keys, and the values in the dictionary can also be database expressions, for example with the given model:

# *hopefully* works

from django_fdict import FDict

origin = F('country_of_origin')

vat_rates = FDict({('R03', origin): 0.21, ('R02', origin): 0.12, ('R01', origin): 0.06})

Product.annotate(vat_pct=vat_rates[F('vat_rate'), F('country')])

it will produce NULL if the country_of_origin is not the same as the country field. But this makes mapping more complicated and eventually the order in which the entries are mapped might determine the outcome.

Technical details

Databases allow to work with CASE … WHEN … THEN … ELSE … END. The FDict converts the dictionary into a (long) chain of WHEN expressions.

So for the first example we use:

SELECT *
       CASE
           WHEN vat_label = 'R03' THEN 0.21
           WHEN vat_label = 'R02' THEN 0.12
           WHEN vat_label = 'R01' THEN 0.06
           ELSE NULL
        END as vat_pct
FROM product

this can also work for more complicated conditions, like:

SELECT *
       CASE
           WHEN vat_label = 'R03' AND country='BE' THEN 0.21
           WHEN vat_label = 'R02' AND country='BE' THEN 0.12
           WHEN vat_label = 'R01' AND country='BE' THEN 0.06
           WHEN vat_label = 'R03' AND country='FR' THEN 0.20
           WHEN vat_label = 'R02' AND country='FR' THEN 0.10
           ELSE NULL
        END as vat_pct
FROM product