It’s not uncommon to have some type of cascading of values in a system. For instance, in our software, we allow a Brand to have some default settings, and then a Location may override some or all of these settings, or just fallback to the brand settings. I’m going to have a look at how this type of thing can be implemented using Django, and a way that this can be handled seamlessly.
We’ll start with our models:
class Brand(models.Model):
brand_id = models.AutoField(primary_key=True)
name = models.TextField()
class Location(models.Model):
location_id = models.AutoField(primary_key=True)
brand_id = models.ForeignKey(Brand, related_name='locations')
name = models.TextField()
WEEKDAYS = [
(1, _('Monday')),
(2, _('Tuesday')),
(3, _('Wednesday')),
(4, _('Thursday')),
(5, _('Friday')),
(6, _('Saturday')),
(7, _('Sunday')),
]
class BrandSettings(models.Model):
brand = models.OneToOneField(Brand, primary_key=True, related_name='settings')
opening_time = models.TimeField()
closing_time = models.TimeField()
start_day = models.IntegerField(choices=WEEKDAYS)
class LocationSettings(models.Model):
location = models.OneToOneField(Location, primary_key=True, related_name='_raw_settings')
opening_time = models.TimeField(null=True, blank=True)
closing_time = models.TimeField(null=True, blank=True)
start_day = models.IntegerField(choices=WEEKDAYS, null=True, blank=True)
We can’t use an abstract base model here, because the LocationSettings values are all optional, but the BrandSettings are not. We might have a look later at a way we can have a base model and inherit-and-change-null on the fields. In the place where we have used this, the relationship between Location and Brand is optional, which complicates things even further.
In practice, we’d have a bunch more settings, but this will make it much easier for us to follow what is going on.
To use these, we want to use a value from the LocationSettings object if it is set, else fall-back to the BrandSettings value for that column.
Location.objects.annotate(
opening_time=Coalesce('settings__opening_time', 'brand__settings__opening_time'),
closing_time=Coalesce('settings__closing_time', 'brand__settings__closing_time'),
start_day=Coalesce('settings__start_day', 'brand__settings__start_day'),
)
And this is fine, but we can make it easier to manage: we want to be able to use Location().settings.start_day, and have that fall-back, but also build some niceness so that we can set values in a nice way in the UI.
We can use a postgres view, and then have a model in front of that:
CREATE OR REPLACE VIEW location_actualsettings AS (
SELECT location_id,
COALESCE(location.opening_time, brand.opening_time) AS opening_time,
COALESCE(location.closing_time, brand.closing_time) AS closing_time,
COALESCE(location.start_day, brand.start_day) AS start_day
FROM location_location
INNER JOIN location_brandsettings brand USING (brand_id)
INNER JOIN location_locationsettings location USING (location_id)
)
Notice that we have used INNER JOIN for both tables: we are making the assumption that there will always be a settings object for each brand and location.
Now, we want a model in front of this:
class ActualSettings(models.Model):
location = models.OneToOneField(Location, primary_key=True, related_name='settings')
opening_time = models.TimeField(null=True, blank=True)
closing_time = models.TimeField(null=True, blank=True)
start_day = models.IntegerField(choices=WEEKDAYS, null=True, blank=True)
class Meta:
managed = False
We want to indicate that it should allow NULL values in the columns, as when we go to update it, None will be taken to mean “use the brand default”.
As for the ability to write to this model, we have a couple of options. The first is to make sure that when we edit instances of the model, we actually use the Location()._raw_settings instance instead of the Location().settings. The other is to make the ActualSettings view have an update trigger:
CREATE OR REPLACE FUNCTION update_location_settings()
RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
RAISE NOTICE 'DELETE FROM location_locationsettings WHERE location_id = %', OLD.location_id;
DELETE FROM location_locationsettings WHERE location_id = OLD.location_id;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE location_locationsettings
SET opening_time = NEW.opening_time,
closing_time = NEW.closing_time,
start_day = NEW.start_day
WHERE location_locationsettings.location_id = NEW.location_id;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO location_locationsettings (SELECT NEW.*);
RETURN NEW;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER update_location_settings
INSTEAD OF INSERT OR UPDATE OR DELETE
ON location_actualsettings
FOR EACH ROW EXECUTE PROCEDURE update_location_settings();
And this works as expected: however it is subject to a pretty significant drawback. If you add columns to the table/view, then you’ll need to update the function. Indeed, if you add columns to the tables, you’ll need to update the view too.
In many cases, this will be sufficient: those tables may not change much, and when they do, it’s just a matter of writing new migrations to update the view and function.
In practice, having the writeable view is probably overkill. You can just use a regular view, with a model in front of it, and then use that model when you need to use the coalesced values, but use the raw model when you are setting values.
You can even make it so that as a UI affordance, you show what the brand fallback value is instead of the None value:
class SettingsForm(forms.ModelForm):
class Meta:
model = LocationSettings
fields = (
'opening_time',
'closing_time',
'start_day'
)
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
# We'll probably want to make sure we use a select_related() for this!
brand = self.instance.location.brand
brand_settings = brand.settings
for name, field in self.fields.items():
# See if the model knows how to display a nice value.
display = 'get_{}_display'.format(name)
if hasattr(brand_settings, display):
brand_value = getattr(brand_settings, display)()
else:
brand_value = getattr(brand_settings, name)
# If we have a time, then we want to format it nicely:
if isinstance(brand_value, datetime.time):
brand_value = Template('{{ value|time }}').render(Context({
'value': brand_value
}))
blank_label = _('Default for {brand}: {value}').format(
brand=brand.name,
value=brand_value,
)
# If we have a select that is _not_ a multiple select, then we
# want to make it obvious that the brand default value can be
# selected, or an explicit choice made.
if hasattr(field, 'choices') and field.choices[0][0] == '':
field.widget.choices = field.choices = [
(_('Brand default'), [('', blank_label)]),
(_('Choices'), list(field.choices[1:]))
]
else:
# On all other fields, set the placeholder, so that no value
# entered will show the brand default label.
field.widget.attrs['placeholder'] = blank_label
As mentioned in a comment: this uses a couple of lookups to get to the BrandSettings, you’d want to make sure your view used a .select_related():
class LocationSettingsView(UpdateView):
form_class = SettingsForm
def get_object(self):
return LocationSettings.objects.select_related('location__brand__settings').get(
location=self.kwargs['location']
)
Again, this is all simplified when we have the requirement that there is always a Brand associated with a Location, and each of these always has a related settings object. It’s the latter part of this that is a little tricky. You can have objects automatically created in a signal handler, but in that case it would have to use default values.
Just from a DRY perspective, it would be great if you could have all three models inherit from the one base class, and have the view and trigger function update automatically.
In order to do that, we’ll need to do a bit of magic.
class SettingsBase(models.Model):
opening_time = models.TimeField()
closing_time = models.TimeField()
start_day = models.IntegerField(choices=WEEKDAYS)
class Meta:
abstract = True
def __init_subclass__(cls):
if getattr(cls, '_settings_optional', False):
for field in cls._meta.fields:
field.null = True
field.blank = True
class BrandSettings(SettingsBase):
brand = models.OneToOneField(
Brand,
primary_key=True,
related_name='settings',
on_delete=models.CASCADE,
)
class LocationSettings(SettingsBase):
location = models.OneToOneField(
Location,
primary_key=True,
related_name='raw_settings',
on_delete=models.CASCADE,
)
_settings_optional = True
class ActualSettings(SettingsBase):
location = models.OneToOneField(
Location,
primary_key=True,
related_name='settings',
on_delete=models.DO_NOTHING,
)
_settings_optional
class Meta:
managed = False
The magic is all clustered in the one spot, and Django’s order it does things makes this easy. By the time __init_subclass__ is evaluated, the subclass exists, and has all of the inherited fields, but none of the non-inherited fields. So, we can update those fields to not be required, if we find a class attribute _settings_optional that is true.
Automatically creating or replacing the view is a bit more work.
class ActualSettings(BaseSettings):
location = models.OneToOneField(
Location,
primary_key=True,
related_name='settings',
on_delete=models.DO_NOTHING,
)
_settings_optional = True
class Meta:
managed = False
@classmethod
def view_queryset(cls):
settings = {
attribute: Coalesce(
'raw_settings__{}'.format(attribute),
'brand__settings__{}'.format(attribute)
) for attribute in (f.name for f in cls._meta.fields)
if attribute != 'location'
}
return Location.objects.annotate(**settings).values('pk', *settings.keys())
This would then need some extra machinery to put that into a migration, and then, when running makemigrations, we’d want to automatically look at the last rendered version of that view, and see if what we have now differs. However, intercepting makemigrations, and changing the operations it creates is something I have not yet figured out how to achieve.
Instead, for Versioning complex database migrations I wound up creating a new management command.
A nicer syntax might be to have some way of defining a postgres view by using a queryset.
ActualSettings = Location.objects.annotate(
opening_time=Coalesce('_raw_settings__opening_time', 'brand__settings__opening_time'),
closing_time=Coalesce('_raw_settings__closing_time', 'brand__settings__closing_time'),
start_day=Coalesce('_raw_settings__start_day', 'brand__settings__start_day'),
).values('location_id', 'opening_time', 'closing_time', 'start_day').as_view()
The problem with this is that we can’t do that in a model definition, as the other models are not loaded at this point in time.
Another possible syntax could be:
class ActualSettings(View):
location = models.F('location_id')
opening_time = Coalesce('_raw_settings__opening_time', 'brand__settings__opening_time')
closing_time = Coalesce('_raw_settings__closing_time', 'brand__settings__closing_time')
start_day = Coalesce('_raw_settings__start_day', 'brand__settings__start_day')
class Meta:
queryset = Location.objects.all()
… but I’m starting to veer off into a different topic now.
Actually writing a trigger function that handles all columns seamlessly is something that we should be able to do. Be warned though, this one is a bit of a doozy:
CREATE OR REPLACE FUNCTION update_instead()
RETURNS TRIGGER AS $$
DECLARE
primary_key TEXT;
target_table TEXT;
columns TEXT;
BEGIN
-- You must pass as first parameter the name of the table to which writes should
-- actually be made.
target_table = TG_ARGV[0]::TEXT;
-- We want to get the name of the primary key column for the target table,
-- if that was not already supplied.
IF (TG_ARGV[1] IS NULL) THEN
primary_key = (SELECT column_name
FROM information_schema.table_constraints
INNER JOIN information_schema.constraint_column_usage
USING (table_catalog, table_schema, table_name,
constraint_name, constraint_schema)
WHERE constraint_type = 'PRIMARY KEY'
AND table_schema = quote_ident(TG_TABLE_SCHEMA)
AND table_name = quote_ident(target_table));
ELSE
primary_key = TG_ARGV[1]::TEXT;
END IF;
-- We also need the names of all of the columns in the current view.
columns = (SELECT STRING_AGG(quote_ident(column_name), ', ')
FROM information_schema.columns
WHERE table_schema = quote_ident(TG_TABLE_SCHEMA)
AND table_name = quote_ident(TG_TABLE_NAME));
IF (TG_OP = 'DELETE') THEN
EXECUTE format(
'DELETE FROM %1$I WHERE %2$I = ($1).%2$I',
target_table, primary_key
) USING OLD;
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
-- columns must be treated as a string, because we've already
-- quoted the columns in the query above.
EXECUTE format(
'INSERT INTO %1$I (%2$s) (SELECT ($1).*)',
target_table, columns
) USING NEW;
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
EXECUTE format(
'UPDATE %1$I SET (%2$s) = (SELECT ($1).*) WHERE %3$I = ($1).%3$I',
target_table, columns, primary_key
) USING NEW;
RETURN NEW;
END IF;
RAISE EXCEPTION 'Unhandled.';
END;
$$ LANGUAGE plpgsql VOLATILE;
There are some things I learned about postgres when doing this: specifically that you can use the EXECUTE format('SELECT ... ($1).%s', arg) USING NEW syntax: the format() function makes it much neater than using string concatenation, and using the EXECUTE '...($1).%s' USING ... form was the only way I was able to access the values from the NEW and OLD aliases within an execute. There’s also a bunch of stuff you have to do to make sure that the columns line up correctly when updating or inserting into the target table.
We can then apply this to our view:
CREATE TRIGGER update_instead
INSTEAD OF UPDATE OR INSERT OR DELETE
ON location_actualsettings
FOR EACH ROW
EXECUTE PROCEDURE update_instead('location_locationsettings', 'location_id');
