0

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?

3
  • I think it would make sense in that case to wrap all values in lists, so even the {"size": 1000} if you start working with two types of values, you get in trouble. Commented May 14 at 7:43
  • The attributes__contains=OuterRef('attributes') however probably (?) interprets the JSON blobs as strings, so I'm not convinced this really filters "semantically". Commented May 14 at 7:44
  • @willeM_VanOnsem , thank for a useful reply, as always! That a great idea to test. But while I’m not went too far is my approach with JSONFiled worth the game or (mostly for data aggregation) EAV would fit better (performance is not a priority as project for internal use). I know it has the drawbacks with joins but didn’t find info which approach considering less antipattern as both are antipattern itself. Commented May 14 at 8:46

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.