It is not possible to use a custom lookup/transorm in a CheckConstraint

[Fabien MICHEL]

Using a custom lookup or transform in a CheckContraint make migrate command fail.

class MonthLastDay(Transform):
    lookup_name = "month_last_day"
    output_field = DateField()
    template = "(DATE_TRUNC('month', %(expressions)s) + (interval '1 month - 1 day'))::date"

DateField.register_lookup(MonthLastDay)

class MyModel(models.Model):
    class Meta:
        constraints = [
            CheckConstraint(check=Q(date__month_last_day__gte=F("period_start")), name="date_month_end_after_period_start"),
        ]

   date = models.DateField()
   period_start = models.DateField()

django makemigrations command does not indicate any issue with this and make use of month_last_day lookup as if it know about it.

Migration command for this contraint:

migrations.AddConstraint(
    model_name='mymodel',
    constraint=models.CheckConstraint(
        check=models.Q(
            ('date__month_last_day__gte', django.db.models.expressions.F('period_start'))
        ), 
        name='date_month_end_after_period_start'),
),

django migrate command fail indicating that date__month_last_day join is not possible

django.core.exceptions.FieldError: Joined field references are not permitted in this query

I'm not sure what could be done. May be the documentation should warn to not use custom lookup/transform in CheckContraint.

[Mariusz Felisiak]

It works for me when you register a custom lookup as ​documented:

"You will need to ensure that this registration happens before you try to create any querysets using it. You could place the implementation in a models.py file, or register the lookup in the ready() method of an AppConfig"

[Fabien MICHEL]

Thanks for your answer.

If I understand, I should copy the custom lookup into the migration file to have it available during migration process ?

[Mariusz Felisiak]

Replying to Fabien MICHEL:

If I understand, I should copy the custom lookup into the migration file to have it available during migration process ?

No, you "....need to ensure that this registration happens before you try to create any querysets using it.", so you can call register_lookup() in your models.py or AppConfig.ready().

[Fabien MICHEL]

In this case, won't I expose my migration to use code that could be ananchronic if the code of the custom lookup changes after the migration has been created ?
Is it not better to embed the custom lookup on the migration which insert the constraint in database ?

[Simon Charette]

In this case, won't I expose my migration to use code that could be ananchronic if the code of the custom lookup changes after the migration has been created? Is it not better to embed the custom lookup on the migration which create the constraint in database ?

The migration framework doesn't detect change at the resulting SQL level so if you were to change your lookup logic you'd have to manually create a migration that removes and adds back the new constraint. Nothing prevents you from defining the lookup in two locations though (ready()) and in the migration itself so it's already there and only overrides the project specific lookup when the migration file is loaded. Just keep in mind that Django won't be hand holding you if you change the SQL generated by the lookup.

In a sense this is similar to how Django allows you to add and remove managers to your model definition without 'baking' the manage definition in the migration file; since the migration framework holds 'references' of managers and lookups it's your responsibility to keep 'referenced values' coherent with migration states.

[Fabien MICHEL]

It is all clear for me, thanks you. Shouldn't we add a warning about this in CheckConstraint documentation ?
Here is a proposal:

.. note::

    If you use a :doc:`custom lookup </howto/custom-lookups>` in CheckContraint
    expression you will have to ensure the expected custom lookup is registered
    before the migration run. Therefore, the migration framework doesn't detect
    change at the resulting SQL of the expression. It is up to you to keep 
    referenced lookup coherent with migration state.