CODEWITHSUNDEEP
djangodjango-templates
Jason Howard
Jason Howard

Reputation: 1586

Stats by month in template

I'm trying to create a simple dashboard that will indicate the number of orders by month. Each order has an invoice date, which is in the form of YYYY-MM-DD

models.py

class Order(models.Model):

    user = models.ForeignKey(UserCheckout, null=True, on_delete=models.CASCADE)
    invoice_date = models.DateField(auto_now=False, auto_now_add=False, null=True, blank=True) 

    def total(self):
       qs = Order.objects.filter(invoice_date[0:6]=self)
       qs.count()
       return qs

template.html

{% for month in monthyear %}
    {{month}}: {{month.total}}
{% endfor %}

monthyear will need to be a queryset of months/year in the form of 2018-07 (i.e. July of 2018)

1) How would I get a queryset of monthyear rather than writing out a huge list of year-month strings for the next 50 or 60 months?

2) Is my query set in the total function correct from a syntax point of view?

3) Am I calling the function correctly within the template?

4) Is there an easier way to do this?

Thanks!

Edit: I've implemented neverwalkaloner's solution, but I get the following error:

Traceback:

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\sqlite3\base.py" in execute
  303.         return Database.Cursor.execute(self, query, params)

The above exception (user-defined function raised exception) was the direct cause of the following exception:

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\handlers\exception.py" in inner
  35.             response = get_response(request)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\handlers\base.py" in _get_response
  128.                 response = self.process_exception_by_middleware(e, request)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\handlers\base.py" in _get_response
  126.                 response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\contrib\auth\decorators.py" in _wrapped_view
  21.                 return view_func(request, *args, **kwargs)

File "C:\Users\jason\Desktop\jason\accounts\views.py" in sales_detail
  1271.     return render(request, 'accounts/salesdetail.html', context)  

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\shortcuts.py" in render
  36.     content = loader.render_to_string(template_name, context, request, using=using)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\loader.py" in render_to_string
  62.     return template.render(context, request)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\backends\django.py" in render
  61.             return self.template.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render
  175.                     return self._render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in _render
  167.         return self.nodelist.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render
  943.                 bit = node.render_annotated(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render_annotated
  910.             return self.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\loader_tags.py" in render
  155.             return compiled_parent._render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in _render
  167.         return self.nodelist.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render
  943.                 bit = node.render_annotated(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render_annotated
  910.             return self.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\loader_tags.py" in render
  155.             return compiled_parent._render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in _render
  167.         return self.nodelist.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render
  943.                 bit = node.render_annotated(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render_annotated
  910.             return self.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\loader_tags.py" in render
  67.                 result = block.nodelist.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render
  943.                 bit = node.render_annotated(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render_annotated
  910.             return self.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\loader_tags.py" in render
  67.                 result = block.nodelist.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render
  943.                 bit = node.render_annotated(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\base.py" in render_annotated
  910.             return self.render(context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\template\defaulttags.py" in render
  168.             len_values = len(values)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\query.py" in __len__
  254.         self._fetch_all()

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\query.py" in _fetch_all
  1179.             self._result_cache = list(self._iterable_class(self))

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\query.py" in __iter__
  106.         for row in compiler.results_iter(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size):

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\sql\compiler.py" in results_iter
  1017.             results = self.execute_sql(MULTI, chunked_fetch=chunked_fetch, chunk_size=chunk_size)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\models\sql\compiler.py" in execute_sql
  1066.             cursor.execute(sql, params)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py" in execute
  100.             return super().execute(sql, params)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py" in execute
  68.         return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py" in _execute_with_wrappers
  77.         return executor(sql, params, many, context)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\utils.py" in __exit__
  89.                 raise dj_exc_value.with_traceback(traceback) from exc_value

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\utils.py" in _execute
  85.                 return self.cursor.execute(sql, params)

File "C:\Users\jason\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\sqlite3\base.py" in execute
  303.         return Database.Cursor.execute(self, query, params)

Exception Type: OperationalError at /accounts/sales/10PERCENT/
Exception Value: user-defined function raised exception

Upvotes: 2

Views: 371

Answers (1)

neverwalkaloner
neverwalkaloner

Reputation: 47364

You can get required data with one query using annotation. To get month use TruncMonth function:

from django.db.models import Count
from django.db.models.functions import TruncMonth
Order.objects.annotate(month=TruncMonth('invoice_date')).values('month').annotate(total=Count('id')).order_by()

This query will give you following structure as the result:

{'month': datetime.date(2018, 1, 1), 'total': 1}, {'month': datetime.date(2018, 4, 1), 'total': 6}, {'month': datetime.date(2018, 5, 1), 'total': 2}

Upvotes: 2

Related Questions