CODEWITHSUNDEEP
djangodjango-orm
Aleksei Khatkevich
Aleksei Khatkevich

Reputation: 2197

Create check constraint based on custom SQL and custom Django fucntion

Question is regarding usage of custom function in check constraint:

I have a following model:

class UserIP(models.Model):
    user = models.ForeignKey(
        to=User,
        on_delete=models.CASCADE,
        related_name='user_ip',
    )
    ip = models.GenericIPAddressField(
        verbose_name='User ip address'
    )
    sample_time = models.DateTimeField(
        auto_now=True,
        verbose_name='User ip sample time'
    )

and I have following custom function in database:

create or replace function count_ips(v_ip inet , v_user_id int, v_limit int)
                               returns boolean as $$
                               select count(*) > v_limit
                               from users_userip
                               where ip = v_ip and user_id = v_user_id
                               $$ language sql;

which returns True if there are more then X (meant to be 3) entries in DB with same ip and user.

Based on this function I created Django function like this:

class IpCount(Func):
    function = 'count_ips'
    arity = 3
    output_field = BooleanField()

usage example:

UserIP.objects.all().annotate(ann=IpCount(Value('127.0.0.1'), 1,3)).first().ann

works perfect

Now I want to make a check constraint that would not allow to save in DB any new entry if there are already 3 or more entries in DB where user and ip are the same.


constraints = [
    models.CheckConstraint(
        name='max_3_ips',
        check=~models.Q(IpCount('ip', 'user_id', 3)),
    ), ]

It says then since Django > 3.1. it supports boolean expressions inside chekc constraints but what I have written it does not work. Error is like this:

 File "C:\Users\hardcase1\.virtualenvs\series-EAOB5IHD\lib\site-packages\django\db\models\query_utils.py", line 117, in deconstruct
    kwargs = {child[0]: child[1]}
TypeError: 'IpCount' object is not subscriptable

Seems like Django mirations can not serialize this function.

Question is – how to use this function in check constraint and is it possible at all or should i just forget it and create custom migration with a bunch of RAW sQL inside instead?

Thanks

Upvotes: 3

Views: 697

Answers (1)

Aleksei Khatkevich
Aleksei Khatkevich

Reputation: 2197

Since django 3.1 it is possible to use expressions that returns boolean, so that it is no point of using Q

models.CheckConstraint(
                name='max_3_ips',
                check=IpCount(models.F('user_id'), models.Value(2)),
            ), ]

Upvotes: 2

Related Questions