Django Python - Itertools is not aligning products with sum of auction sales

Question

I generate a list of Products based on two criterias Entity and Period. The list retrieved contains all Products and the sum of all auctions sales for each. The example below shows a list of 4 products for Company XZY in November. Out of all the products listed only one product (sku 30) had two auction sales $180 and $220. The problem is the rest of products had no sales so the null value is not aligning properly with the product. I will need to still list those products and show $0 in sales.

Models.py

class Entity(models.Model):
    entity = models.CharField(max_length=40)

class Period(models.Model):
    period = models.CharField(max_length=7)

class Product(models.Model):
    entity = models.ForeignKey(Entity, on_delete=models.CASCADE, default=None, blank=True, null=True)
    period = models.ForeignKey(Period, on_delete=models.CASCADE, default=None, blank=True, null=True)
    sku = models.CharField(max_length=40)
    projectedsales = models.DecimalField(max_digits=11, decimal_places=2)

class Post(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE, default=None, blank=True, null=True)
    auctionsale = models.DecimalField(max_digits=11, decimal_places=2)

Views.py

def get(self, request):
        form = ProductsViewForm()
        return render(request, "products_list.html", {'form': form})

    def post(self, request):
        products = None
        posts = None
        entities = None
        periods = None
        mylist = None
        form = ProductsViewForm(request.POST)
        if request.method == 'POST':
            if form.is_valid():
                entityquery = form.cleaned_data['entity']
                periodquery = form.cleaned_data['period']
                entities = Entity.objects.get(entity=entityquery)
                periods = Period.objects.get(period=periodquery)
                products = Product.objects.filter(entity=entityquery, period=periodquery).values('id', 'period', 'entity', 'sku', 'projectedsales')
                posts = Post.objects.filter(product__sku__in=products.values('sku'), product__entity=entityquery, product__period=periodquery).annotate(total=Sum('auctionsale'))
                mylist = list(itertools.zip_longest(products, posts, fillvalue='0'))
            args = {'form': form, 'periods': periods, 'entities': entities, 'mylist': mylist}
        return render(request, "products_list.html.html", args)

products_list.html


    
        
            {% csrf_token %}
            
                
                    {{ form.entity|add_class:"custom-select" }}
                
                
                     
                
                
                    
                
            
        
    



    
        

        {% if entities %}
            Auction Sales
            Entity : {{entities.entity}}  
        {% endif %}

        {% if periods %}
            Period : {{periods.period}}
 
        {% endif %}
        

        {% if mylist %}
            
                    {% for product, post in mylist %}
                        
                    {% endfor %}
                
                
                    
                        Product
                        Projected Sales
                        Total Auction Sales
                    
                
                
                            {{product.sku}}
                            {{product.projectedsales}}
                            {{post.total}}
                        
            
        {% endif %}

Query

Entity: Company XZY 
Period: 11/2020

Expected results

Product -- Projected Auction Sales -- Total Auction Sales
sku 10  -- $100                    -- $0
sku 20  -- $200                    -- $0
sku 30  -- $600                    -- $400
sku 40  -- $500                    -- $0

The results i am getting

Product -- Projected Auction Sales -- Total Auction Sales
sku 10  -- $100                    -- $400
sku 20  -- $200                
sku 30  -- $600
sku 40  -- $500

Admin · Answer

If you're not stuck on itertools, but want to do this the Django way, then use:

views.py:

post_field_name = Post._meta.get_field('product').related_query_name()
field_ref = f"{post_field_name}__auctionsale"
sales = Product.objects.annotate(total_sales=Sum(field_ref))

template.html (sales queryset passed as "sales" variable to template):


    {% for sale in sales %}
        
    {% endfor %}
{{ sale.sku }} {{ sale.total_sales|default_if_none:"$ 0" }}

user_8283848 · Answer

I would rather use the Coalesce(...)--(Django Doc) database function of Django along with the Sum()--(Django Doc) function

from django.db.models import Sum
from django.db.models.functions import Coalesce

product_qs = Product.objects.annotate(
    action_sum=Coalesce(Sum("post__auctionsale"), 0)
                                                ^^^^^ this is the default value
)
for product in product_qs:
    print(product.sku, " ---- ", product.action_sum)

# Result
30  ----  400
10  ----  0
20  ----  0
40  ----  0

user_14872300 · Answer

The issue lies with your use of itertools.zip_longest(products, posts, fillvalue='0'), as only one of your items has had a sale, posts has only one item, and itertools.zip_longest associates this item to the first one of products, which happens to be sku 10.

To fix this, you can use a dictionnary for your posts that associates the posts to their product :

posts = {post.product: post.total for post in Post.objects.filter(product__sku__in=products.values('sku'), product__entity=entityquery, product__period=periodquery).annotate(total=Sum('auctionsale'))}

Then, to construct your list, you can define a function :

def mk_list(products, posts):
  values = []
  for product in products:
    values.append((product, posts.get(product, 0)))
  return values

When a product has no auction sales associated to it, posts.get(product, 0) will return 0, which is what you need.

Django Python - Itertools is not aligning products with sum of auction sales

3 Answers