Ticket #27498: tests.py

from django.db import connection
from django.db.models import Sum, F, Case, When
from django.test import TestCase
from .models import Product, Stock


class ErrorTestCase(TestCase):
    def setUp(self):
        p1 = Product.objects.create(name="Product1", qty_target=10)
        p2 = Product.objects.create(name="Product2", qty_target=10)
        p3 = Product.objects.create(name="Product3", qty_target=10)

        s1 = Stock.objects.create(product=p1, qty_available=5)
        s2 = Stock.objects.create(product=p1, qty_available=5)
        s3 = Stock.objects.create(product=p1, qty_available=3)  # 3 over target

        s4 = Stock.objects.create(product=p2, qty_available=5)
        s5 = Stock.objects.create(product=p2, qty_available=4)  # 1 under target

        s6 = Stock.objects.create(product=p3, qty_available=0)  # 10 under target

    def testError(self):
        qs = Product.objects.annotate(qty_available_sum=Sum('stock__qty_available')) \
                            .annotate(qty_needed=F('qty_target') - F('qty_available_sum')) \
                            .filter(qty_needed__gt=0)

        # Query:
        print()
        query = str(qs.query)
        print("# Query from QuerySet:")
        print(query)
        rows = []
        with connection.cursor() as cursor:
            cursor.execute(query)
            rows = cursor.fetchall()
        print("# Results from above Query:")
        for r in rows:
            print(r)
        self.assertEqual(len(rows), 2, "Two products need stock by SQL query.")

        # QuerySet:
        print()
        print("# Results from QuerySet:")
        for q in qs:
            print(q, q.qty_needed)
        self.assertEqual(qs.count(), 2, "Two products need stock by Django QuerySet.")