Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can't use annotated field in CASE-WHEN Clause #1299

Closed
kirillismad opened this issue Nov 27, 2022 · 0 comments · Fixed by #1748
Closed

Can't use annotated field in CASE-WHEN Clause #1299

kirillismad opened this issue Nov 27, 2022 · 0 comments · Fixed by #1748

Comments

@kirillismad
Copy link

kirillismad commented Nov 27, 2022

Describe the bug
I have tortoise.exceptions.FieldError: Unknown filter param 'waiting_time__gte'. Allowed base values are ['client_info', 'created_at', 'guest_info', 'id', 'manager', 'manager_id', 'priority', 'source']

To Reproduce
Model:

class Chat(Model):
    id = fields.UUIDField(pk=True)
    created_at = fields.DatetimeField(auto_now_add=True)
    manager = fields.ForeignKeyField(
        'models.Manager',
        related_name='chat_requests',
        null=True,
        on_delete=fields.SET_NULL,
    )
    priority = fields.IntField(
        validators=[
            MinValueValidator(MIN_PRIORITY),
            MaxValueValidator(MAX_PRIORITY),
        ]
    )
    source = fields.IntEnumField(ChatSource)
    guest_info = fields.JSONField(null=True)
    client_info = fields.JSONField(null=True)

Query:

queryset = Chat.all()
queryset = queryset.annotate(
    type=Case(
        When(Q(guest_info__isnull=False), then='GUEST'),
        When(Q(client_info__isnull=False), then='CLIENT'),
    ),
)
queryset = queryset.annotate(
    waiting_time=RawSQL('CURRENT_TIMESTAMP') - F('created_at'),
)

queryset = queryset.annotate(
    ignore_priority=Case(
        When(Q(priority=1, waiting_time__gte=timedelta(minutes=10)), then=True),
        When(Q(priority__in=(2, 3), waiting_time__gte=timedelta(minutes=15)), then=True),
        When(Q(priority__in=(4, 5), waiting_time__gte=timedelta(minutes=25)), then=True),
        default=False,
    ),
)

Expected behavior
Expected result is: queryset, which I can await and get Iterable[Chat], with attribute ignore_priority

Additional context
Full traceback:

File "/usr/local/lib/python3.9/site-packages/tortoise/queryset.py", line 998, in __await__
    self._make_query()
  File "/usr/local/lib/python3.9/site-packages/tortoise/queryset.py", line 960, in _make_query
    self.resolve_filters(
  File "/usr/local/lib/python3.9/site-packages/tortoise/queryset.py", line 126, in resolve_filters
    has_aggregate = self._resolve_annotate()
  File "/usr/local/lib/python3.9/site-packages/tortoise/queryset.py", line 241, in _resolve_annotate
    annotation_info[key] = annotation.resolve(self.model, table)
  File "/usr/local/lib/python3.9/site-packages/tortoise/expressions.py", line 595, in resolve
    criterion, term = arg.resolve(model, table)
  File "/usr/local/lib/python3.9/site-packages/tortoise/expressions.py", line 564, in resolve
    modifier &= node.resolve(model, model._meta.basetable)
  File "/usr/local/lib/python3.9/site-packages/tortoise/expressions.py", line 359, in resolve
    return self._resolve_kwargs(model, table)
  File "/usr/local/lib/python3.9/site-packages/tortoise/expressions.py", line 317, in _resolve_kwargs
    key, value = self._get_actual_filter_params(model, raw_key, raw_value)
  File "/usr/local/lib/python3.9/site-packages/tortoise/expressions.py", line 311, in _get_actual_filter_params
    raise FieldError(f"Unknown filter param '{key}'. Allowed base values are {allowed}")
tortoise.exceptions.FieldError: Unknown filter param 'waiting_time__gte'. Allowed base values are ['client_info', 'created_at', 'guest_info', 'id', 'manager', 'manager_id', 'priority', 'source']

It works for me, but it's really dirty solution:

queryset = Chat.all()
queryset = queryset.annotate(
    type=Case(
        When(Q(guest_info__isnull=False), then='GUEST'),
        When(Q(client_info__isnull=False), then='CLIENT'),
    ),
)
_ignore_priority = '''
    CASE 
    WHEN "priority" = 1 AND (CURRENT_TIMESTAMP - created_at  >= '10 minutes'::interval) THEN true
    WHEN "priority" IN (2, 3) AND (CURRENT_TIMESTAMP - created_at  >= '15 minutes'::interval) THEN true
    WHEN "priority" IN (4, 5) AND (CURRENT_TIMESTAMP - created_at  >= '25 minutes'::interval) THEN true
    ELSE false
    END
'''  # noqa
queryset = queryset.annotate(ignore_priority=RawSQL(dedent(_ignore_priority)))

_order = f'''
    ROW_NUMBER()
    OVER (ORDER BY ({_ignore_priority}) DESC, "priority" DESC, (CURRENT_TIMESTAMP - created_at) ASC)
'''  # noqa

queryset = queryset.annotate(order=RawSQL(dedent(_order)))
queryset = queryset.order_by('order')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant