Is possible add a subquery with anothers relationships in django orm annotate

Viewed 36

I have a View to show my total stock by "point of sale".

My problem is a new table added and I have put a more field in my View (counting total of reserved products).

My actual scenario:

Tables

class SalePoint(models.Model):
    location = models.CharField(max_length=200, verbose_name='Local')
    [...]
class Box(models.Model):
    sale_point = models.ForeignKey(
        [...]
        related_name='boxes',
        [...]
    )
    product = models.ForeignKey(
        [...]
        related_name='boxes',
        [...]
    )
    amount = models.PositiveSmallIntegerField()
    capacity = models.PositiveSmallIntegerField()
class Product(models.Model):
    name = models.CharField()
    sku = models.CharField()

View

SalePointStockViewSet(viewsets.ModelViewSet):
    def list(self, request):
        queryset = (
            SalePoint.objects.values(
                'location',
                'boxes__product__name'
            ).annotate(
                total_amount=Sum('boxes__amount'),
                total_capacity=Sum('boxes__capacity'),
            )
            .order_by('location', 'total_amount')
        )
        return Response(queryset)

As I had said, my view works fine. I need to include a new field "reserved" in my queryset.

Reserved is a sum of total ordered products with status "waiting" from this table:

class Order(models.Model):
    WAITING = 'WAIT'
    DELIVERED = 'DELI'
    STATUS_CHOICES = [
        (WAITING, 'Waiting'),
        (DELIVERED, 'Delivered'),
    ]
    sale_point = models.ForeignKey(
        [...]
        related_name='orders',
        [...]
    )
    product_sku = models.CharField()
    status = models.CharField(
        [...]
        choices=STATUS_CHOICES,
    )

What am I thinking

I'm thinking if it's possible to add the "reserved" field and fill it with a "subquery". The problem is that I have to filter the product by "sku". I don't know if this would be the best way.

0 Answers
Related