django-pgtrigger

django-pgtrigger copied to clipboard

Here's an example representation of my models:

class Tag(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)

class Activity(models.Model):
    user = models.ForeignKey('auth.User', on_delete=models.CASCADE)
    tags = models.ManyToManyField(Tag, through='TagBinding')

class TagBinding(models.Model):
    tag = models.ForeignKey(Tag)
    activity = models.ForeignKey(Activity)

example picked from this SO Question

And I wanted to make sure tag.user is same as activity.user. So I added the following trigger on TagBinding class

@pgtrigger.register(
    pgtrigger.Protect(
        name='ensure_same_user',
        operation=pgtrigger.Insert | pgtrigger.Update,
        condition=pgtrigger.Q(new__tag__user_id__df=pgtrigger.F('new__activity__user_id'))
        )
    )

Ad when running ./manage.py pgtrigger install, it throws:

django.core.exceptions.FieldDoesNotExist: TagBinding has no field named 'activity__user_id'

Tried dropping the _id from user_id from both F statement and Q statement. No luck.

Is there any way to work around this?

I mainly want to use triggers to ensure that my related data belongs to same user (tried using constraints, but they cannot be configured on related models, so I read that triggers are the solution, but the above errors put a question mark on this approach). Should I do application level validation instead? Please suggest on what can be done here @wesleykendall :)

Have not tested this, but as a workaround, try a pgtrigger.Trigger with a func and without a condition. The function can execute subqueries to resolve the relationships, and it can use the subquery results in an IF to determine whether an exception should be raised.

Works wonders! Thanks for the suggestions @MarkKoz !

Here's a made-up script for anyone looking at similar requirement:

We are trying to ensure that the City has same Country as that assigned to State. i.e. city.state.country == city.country

class State(models.Model):
    country = models.ForeignKey(Country)
    class Meta:
        db_table = 'myapp_state'

@pgtrigger.register(
   pgtrigger.Trigger(
      name='ensure_same_country',
          when=pgtrigger.Before,
          operation=pgtrigger.Insert | pgtrigger.Update,
          func=f"""
              DECLARE
                  country_id   INTEGER;
              BEGIN
                SELECT t.country_id INTO country_id FROM myapp_state as t WHERE t.id=NEW.country_id LIMIT 1;
                IF country_id != NEW.country_id THEN
                    RAISE EXCEPTION 'invalid country_id: country being inserted is "%" which is not equal to country assigned to strategy_task(%) %', NEW.country_id, NEW.state_id, country_id;
                END IF;
                RETURN NEW;
              END;""",
            )
        )
class City(models.Model):
    country = models.ForeignKey(Country)
    state = models.ForeignKey(State)

@tiholic thanks for showing the example! Unfortunately it's not possible to do that type of condition with Postgres trigger conditions. Trigger conditions can only be defined on table columns and cannot do joins.

I'm going to mark this as a wontfix. I'm going re-open this issue for now and close it after I:

1. Add it to the docs that it's not possible to do conditions on joined data
2. Add the city/state example to the trigger cookbook for others