django use subquery to annotate count of distinct values of foreign key field-CodePudding

I am trying to annotate the quantity of distinct products but I have not been successful as of now. I get error such as:

ProgrammingError: subquery must return only one column
LINE 1: ..._id", "shop_selectedproduct"."create_date", COUNT((SELECT U0...

I have the following models:

class ShopItem(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    store = models.ForeignKey(to=Store, on_delete=models.CASCADE)
    name = models.CharField(max_length=250)

class SelectedProduct(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name="products", null=True, blank=True)
    product = models.ForeignKey(ShopItem, null=True, blank=True, on_delete=models.SET_NULL)

class Order(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    store = models.ForeignKey(Store, on_delete=models.CASCADE, related_name="orders")
    status = models.PositiveIntegerField(choices=OrderStatus.choices, default=OrderStatus.CART)
    number = models.CharField(max_length=36, blank=True, null=True)

my attempt to count distinct shopitems such as p1, p2, p3, p4 and p5 where p1 quantity should be 3 because it is selected three times, whereas others may only have a quantity of one since we only selected one of each products.

My original implementation:

products = order.products.all().annotate(quantity=Count("product"))

But this always returns 1 for all products, although they are selected multiple times.

My attempt as of now to fix this is:

# order = 1
SelectedProduct.objects.filter(order=order).annotate(quantity=Count(Subquery(ShopItem.objects.filter(id=OuterRef("product")))))

is there anyway I could count how many times a product is selected and annotate that on the selected_product such as:

p1.quanity
> 3
p2.quanity
> 1
p3.quanity
> 1
p4.quanity
> 1
p5.quanity
> 1

I'd appreciate any insights.

Edit: my further attempts:

subquery = Subquery(ShopItem.objects.filter(selectedproduct__order=order, selectedproduct=OuterRef('id')).order_by()
.values('selectedproduct').annotate(count=Count('pk'))
.values('count'), output_field=IntegerField())

SelectedProduct.objects.filter(order=order).annotate(child_count=Coalesce(Subquery, 0))

it throws RecursionError: maximum recursion depth exceeded

CodePudding user response：

Assuming you're tyring to do this on a per-order basis, you could query from the order instance itself and annotate on the related manager?

# Assuming the instance is self
self.products.all().annotate(
    quantity=Count(
        'product', distinct=True
    ),
)

CodePudding user response：

try this:

SelectedProduct.objects.filter(
   order=order
).values(
   'product'
).annotate(
   quantity=Count('product')
).order_by('product')

also,you can count data for all product has selected:

SelectedProduct.objects.values(
   'product'
).annotate(
   quantity=Count('product')
).order_by('product')

if you want to count all selected items for all products

ShopItem.objects.annotate(
   quantity=Count('selectedproduct')
)

CodePudding user response：

Given an order instance if you want a collection of ShopItems in that order annotated with their quantity in the order, you can query the ShopItem table.

products = (ShopItem.objects.filter(selectedproduct__order=order)
                            .annotate(quantity=Count('selectedproduct')))