I'm not sure if the title of this post made you understand the problem but let me explain:
I have models:
class ItemCategory(models.Model):
name = CharField()
class Item(models.Model):
brand = CharField()
model = CharField()
category = ForeignKey(ItemCategory)
attributes = JSONField(default=dict) # e.g {"size": 1000, "power": 250}
class StockItem(models.Model):
item = ForeignKey(Item)
stock = ForeignKey(Stock)
quantity = PositiveIntegerField()
This models represent some articles on stock. Now I want to implement functionality to allow user create "abstract container" where he can input data and monitor how many of "abstract containers" may be produced based on stock remainings.
Example:
class Container(models.Model):
name = CharField()
class ContainerItem(models.Model):
container = models.ForeignKey(Container)
item_category = models.ForeignKey(ItemCategory)
attributes = models.JSONField(default=dict)
quantity = models.PositiveIntegerField()
To handle aggregation I build a view:
class ContainerListView(ListView):
model = models.Container
def get_queryset(self):
items_quantity_sq = models.Item.objects.filter(
item_category=OuterRef('item_category'),
attributes__contains=OuterRef('attributes'),
).values('item_category').annotate(
total_quantity=Sum('stock_items__quantity')
).values('total_quantity')
min_available_sq = models.ContainerItem.objects.filter(
container_id=OuterRef('pk')
).annotate(
available=Coalesce(Subquery(items_quantity_sq), Value(0))
).order_by('available').values('available')[:1]
base_qs = super().get_queryset().annotate(
# Attach the minimum available quantity across all items
potential=Subquery(min_available_sq)
).prefetch_related(
Prefetch(
"items",
queryset=models.ContainerItem.objects.all()
.annotate(available=Subquery(items_quantity_sq))
.order_by("available"),
)
)
return base_qs.order_by("potential")
It works until ContainerItem attributes field contains a single value: {"size": 1000} but I want to allow user to input multiple values ("size": [1000, 800]) to find all Item objects which attributes field (JSONField) contains "size": 1000 or "size": 800.
There might be a solution using RawSQL. But I wondering if there is a right design at all for performing such aggregation in a future? Maybe in this case EAV may be a better solution?
{"size": 1000}if you start working with two types of values, you get in trouble.attributes__contains=OuterRef('attributes')however probably (?) interprets the JSON blobs as strings, so I'm not convinced this really filters "semantically".