How do I properly write the filter code so it returns only the Animals that are not sold out.
I'm using POSTGRES db, python3.6 and Django 2.1.7 (currently there are v2.2a1,v2.2b1 pre-release versions)
My questioin is an extension to Django JSONField filtering which filters on a hard coded value in the filter.
My Case requires an annotated value in the filter.
models.py I know that the models can be optimized, but I already have huge amount of records since more than 3 years
from django.db import models
from django.contrib.postgres.fields import JSONField
class Animal(models.Model):
data = models.JSONField(verbose_name=_('data'), blank=True)
class Sell(models.Model):
count = models.IntegerField(verbose_name=_('data'), blank=True)
animal = models.ForeignKey('Animal',
on_delete=models.CASCADE,
related_name="sales_set",
related_query_name="sold"
)
in my api I want to return only the animals that still have something left for selling
animal = Animal(data={'type':'dog', 'bread':'Husky', 'count':20})
What I want to filter should be similar to animal.data['count'] > sum(animal.sales_set__count
Animal.objects.annotate(animals_sold=Sum('sales_set__count'))
.filter(data__contains=[{'count__gt': F('animals_sold')}])
with the code above i get builtins.TypeError
TypeError: Object of type 'F' is not JSON serializable
if I remove the F it won't filter on the value of the animals_sold, but on the text 'animals_sold' and it doesn't do any help.
Animal.objects.annotate(animals_sold=Sum('sales_set__count'))
.filter(data__contains=[{'count__gt': F('animals_sold')}])
Edit 1: There is one more topic here that can be linked: Postgres: values query on json key with django
Edit 2: here is some additional code with custom transform classes as suggested in related django ticket
from django.db.models.constants import LOOKUP_SEP
from django.db.models import F, Q, Prefetch, Sum
from django.db.models import IntegerField, FloatField, ExpressionWrapper
from django.db.models.functions import Cast
from django.contrib.postgres.fields import JSONField
from django.contrib.postgres.fields.jsonb import KeyTransform, KeyTextTransform
class KeyIntegerTransform(KeyTransform): # similar to KeyTextTransform
""" trasnform the data.count to integer """
operator = '->>'
nested_operator = '#>>'
output_field = IntegerField()
class KeyIntTransformFactory:
""" helper class for the JSONF() """
def __init__(self, key_name):
self.key_name = key_name
def __call__(self, *args, **kwargs):
return KeyIntegerTransform(self.key_name, *args, **kwargs)
class JSONF(F):
""" for filtering on JSON Fields """
def resolve_expression(self, query=None, allow_joins=True, reuse=None, summarize=False, for_save=False):
rhs = super().resolve_expression(query, allow_joins, reuse, summarize, for_save)
field_list = self.name.split(LOOKUP_SEP)
for name in field_list[1:]:
rhs = KeyIntegerTransform(name)(rhs)
return rhs
queryset filtering I tried so far:
q = q.filter(data__contains={'count__gt':JSONF('sold_count_sum')})
# err: Object of type 'JSONF' is not JSON serializable
q = q.filter(sold_count_sum__lt=Cast(JSONF('data_count'), IntegerField()))
# err: operator does not exist: text ->> unknown
q = q.filter(sold_count_sum__lt=Cast(JSONF('data__count'), IntegerField()))
# err: 'KeyIntegerTransform' takes exactly 1 argument (0 given)
q = q.filter(sold_count_sum__lt=KeyIntegerTransform('count', 'data'))
# err: operator does not exist: text ->> unknown
q = q.filter(sold_count_sum__lt=F('data__count'))
# err: operator does not exist: text ->> unknown
q = q.filter(sold_count_sum__lt=F('data_count'))
# err: operator does not exist: text ->> unknown
q = q.filter(sold_count_sum__lt=JSONF('data_count'))
# err: operator does not exist: text ->> unknown
q = q.filter(sold_count_sum__lt=JSONF('data__count'))
# err: 'KeyIntegerTransform' takes exactly 1 argument (0 given)
q = q.filter(sold_count_sum__lt=JSONF('data', 'count'))
# err: JSONF.__init__() takes 2 params
