From 53413388e016c9353fbd4844690f3deacd309f65 2016-04-06 12:59:00 From: Christopher Neugebauer Date: 2016-04-06 12:59:00 Subject: [PATCH] Optimises queries through simplifying repeated queries and select_related use --- diff --git a/registrasion/controllers/cart.py b/registrasion/controllers/cart.py index def22c17d2d052a6af1b1707283dd0e0744ba265..abf56357d958e0634e4f7a7a7d571d120ed0a7e1 100644 --- a/registrasion/controllers/cart.py +++ b/registrasion/controllers/cart.py @@ -80,6 +80,11 @@ class CartController(object): pairs. ''' items_in_cart = rego.ProductItem.objects.filter(cart=self.cart) + items_in_cart = items_in_cart.select_related( + "product", + "product__category", + ) + product_quantities = list(product_quantities) # n.b need to add have the existing items first so that the new @@ -283,6 +288,7 @@ class CartController(object): # Fix products and discounts items = rego.ProductItem.objects.filter(cart=self.cart) + items = items.select_related("product") products = set(i.product for i in items) available = set(ProductController.available_products( self.cart.user, @@ -302,7 +308,9 @@ class CartController(object): # Delete the existing entries. rego.DiscountItem.objects.filter(cart=self.cart).delete() - product_items = self.cart.productitem_set.all() + product_items = self.cart.productitem_set.all().select_related( + "product", "product__category", + ) products = [i.product for i in product_items] discounts = discount.available_discounts(self.cart.user, [], products) @@ -310,6 +318,7 @@ class CartController(object): # The highest-value discounts will apply to the highest-value # products first. product_items = self.cart.productitem_set.all() + product_items = product_items.select_related("product") product_items = product_items.order_by('product__price') product_items = reversed(product_items) for item in product_items: diff --git a/registrasion/controllers/category.py b/registrasion/controllers/category.py index 04f502db1ff67d9e1eeec98c4aa04048a3fb7575..a3753600c7dd6bcd75a9546da0dd4d1d3b69dfa0 100644 --- a/registrasion/controllers/category.py +++ b/registrasion/controllers/category.py @@ -22,7 +22,7 @@ class CategoryController(object): from product import ProductController if products is AllProducts: - products = rego.Product.objects.all() + products = rego.Product.objects.all().select_related("category") available = ProductController.available_products( user, diff --git a/registrasion/controllers/conditions.py b/registrasion/controllers/conditions.py index 55b1a7d5d948da752ae2b44a093d8839f9a401d5..6e5fb82f807a11db1e3ed91c92a27720e5a8bd08 100644 --- a/registrasion/controllers/conditions.py +++ b/registrasion/controllers/conditions.py @@ -1,4 +1,5 @@ import itertools +import operator from collections import defaultdict from collections import namedtuple @@ -90,12 +91,22 @@ class ConditionController(object): quantities = {} # Get the conditions covered by the products themselves - all_conditions = [ - product.enablingconditionbase_set.select_subclasses() | - product.category.enablingconditionbase_set.select_subclasses() + + prods = ( + product.enablingconditionbase_set.select_subclasses() for product in products - ] - all_conditions = set(itertools.chain(*all_conditions)) + ) + # Get the conditions covered by their categories + cats = ( + category.enablingconditionbase_set.select_subclasses() + for category in set(product.category for product in products) + ) + + if products: + # Simplify the query. + all_conditions = reduce(operator.or_, itertools.chain(prods, cats)) + else: + all_conditions = [] # All mandatory conditions on a product need to be met mandatory = defaultdict(lambda: True) @@ -115,10 +126,14 @@ class ConditionController(object): from_category = rego.Product.objects.filter( category__in=condition.categories.all(), ).all() - all_products = set(itertools.chain(cond_products, from_category)) - + all_products = cond_products | from_category + all_products = all_products.select_related("category") # Remove the products that we aren't asking about - all_products = all_products & products + all_products = [ + product + for product in all_products + if product in products + ] if quantities: consumed = sum(quantities[i] for i in all_products) diff --git a/registrasion/controllers/discount.py b/registrasion/controllers/discount.py index 084584ee541ae9bc3cf10701efe3a4c44db7906a..f03063a83e20b389d8fb9060f6fbfc299e74ef41 100644 --- a/registrasion/controllers/discount.py +++ b/registrasion/controllers/discount.py @@ -13,7 +13,7 @@ class DiscountAndQuantity(object): self.quantity = quantity def __repr__(self): - print "(discount=%s, clause=%s, quantity=%d)" % ( + return "(discount=%s, clause=%s, quantity=%d)" % ( self.discount, self.clause, self.quantity, ) @@ -37,11 +37,20 @@ def available_discounts(user, categories, products): ) # (Not relevant: discounts that match products in provided categories) + product_discounts = product_discounts.select_related( + "product", + "product__category", + ) + + all_category_discounts = category_discounts | product_category_discounts + all_category_discounts = all_category_discounts.select_related( + "category", + ) + # The set of all potential discounts potential_discounts = set(itertools.chain( product_discounts, - category_discounts, - product_category_discounts, + all_category_discounts, )) discounts = [] @@ -50,6 +59,7 @@ def available_discounts(user, categories, products): accepted_discounts = set() failed_discounts = set() + for discount in potential_discounts: real_discount = rego.DiscountBase.objects.get_subclass( pk=discount.discount.pk, @@ -63,7 +73,7 @@ def available_discounts(user, categories, products): cart__user=user, cart__active=False, # Only past carts count cart__released=False, # You can reuse refunded discounts - discount=discount.discount, + discount=real_discount, ) agg = past_uses.aggregate(Sum("quantity")) past_use_count = agg["quantity__sum"] diff --git a/registrasion/controllers/product.py b/registrasion/controllers/product.py index b49d5569c3dab6a84c4c0d4ce13d57e0c4ca5754..4e9e6cab64895ceb38beb6779565b946bbce7c73 100644 --- a/registrasion/controllers/product.py +++ b/registrasion/controllers/product.py @@ -23,18 +23,25 @@ class ProductController(object): if category is not None: all_products = rego.Product.objects.filter(category=category) + all_products = all_products.select_related("category") else: all_products = [] if products is not None: - all_products = itertools.chain(all_products, products) + all_products = set(itertools.chain(all_products, products)) + + cat_quants = dict( + ( + category, + CategoryController(category).user_quantity_remaining(user), + ) + for category in set(product.category for product in all_products) + ) passed_limits = set( product for product in all_products - if CategoryController(product.category).user_quantity_remaining( - user - ) > 0 + if cat_quants[product.category] > 0 if cls(product).user_quantity_remaining(user) > 0 ) diff --git a/registrasion/templatetags/registrasion_tags.py b/registrasion/templatetags/registrasion_tags.py index 63a2bc245d0a1fe70e525449e358a99b15e8eec7..17edc8e47094ca3497a15df5fb3aa0be68ce4203 100644 --- a/registrasion/templatetags/registrasion_tags.py +++ b/registrasion/templatetags/registrasion_tags.py @@ -30,7 +30,7 @@ def items_pending(context): all_items = rego.ProductItem.objects.filter( cart__user=context.request.user, cart__active=True, - ) + ).select_related("product", "product__category") return all_items @@ -42,17 +42,18 @@ def items_purchased(context, category=None): all_items = rego.ProductItem.objects.filter( cart__user=context.request.user, cart__active=False, - ) + cart__released=False, + ).select_related("product", "product__category") if category: all_items = all_items.filter(product__category=category) - products = set(item.product for item in all_items) + pq = all_items.values("product").annotate(quantity=Sum("quantity")).all() + products = rego.Product.objects.all() out = [] - for product in products: - pp = all_items.filter(product=product) - quantity = pp.aggregate(Sum("quantity"))["quantity__sum"] - out.append(ProductAndQuantity(product, quantity)) + for item in pq: + prod = products.get(pk=item["product"]) + out.append(ProductAndQuantity(prod, item["quantity"])) return out diff --git a/registrasion/views.py b/registrasion/views.py index 1cb4ec5797152850ec4aed0e1c6b9a7720a55042..231783a323ebe910f70e2184458398b43c2e0713 100644 --- a/registrasion/views.py +++ b/registrasion/views.py @@ -115,11 +115,20 @@ def guided_registration(request, page_id=0): current_step = 3 title = "Additional items" + all_products = rego.Product.objects.filter( + category__in=cats, + ).select_related("category") + + available_products = set(ProductController.available_products( + request.user, + products=all_products, + )) + for category in cats: - products = ProductController.available_products( - request.user, - category=category, - ) + products = [ + i for i in available_products + if i.category == category + ] prefix = "category_" + str(category.id) p = handle_products(request, category, products, prefix) @@ -280,15 +289,17 @@ def handle_products(request, category, products, prefix): items = rego.ProductItem.objects.filter( product__in=products, cart=current_cart.cart, - ) + ).select_related("product") quantities = [] - for product in products: - # Only add items that are enabled. - try: - quantity = items.get(product=product).quantity - except ObjectDoesNotExist: - quantity = 0 - quantities.append((product, quantity)) + seen = set() + + for item in items: + quantities.append((item.product, item.quantity)) + seen.add(item.product) + + zeros = set(products) - seen + for product in zeros: + quantities.append((product, 0)) products_form = ProductsForm( request.POST or None, @@ -323,8 +334,12 @@ def handle_products(request, category, products, prefix): def set_quantities_from_products_form(products_form, current_cart): quantities = list(products_form.product_quantities()) + + pks = [i[0] for i in quantities] + products = rego.Product.objects.filter(id__in=pks).select_related("category") + product_quantities = [ - (rego.Product.objects.get(pk=i[0]), i[1]) for i in quantities + (products.get(pk=i[0]), i[1]) for i in quantities ] field_names = dict( (i[0][0], i[1][2]) for i in zip(product_quantities, quantities)