Using .aggregate() on a value introduced using .extra(select={…}) in a Django Query?

by

You could use a custom aggregate function to produce your query:

WEEK_FUNC = 'STRFTIME("%%%%W", %s)' # use 'WEEK(%s)' for mysql

class WeekCountAggregate(models.sql.aggregates.Aggregate):
    is_ordinal = True
    sql_function = 'WEEK' # unused
    sql_template = "COUNT(%s)" % (WEEK_FUNC.replace('%%', '%%%%') % '%(field)s')

class WeekCount(models.aggregates.Aggregate):
    name="Week"
    def add_to_query(self, query, alias, col, source, is_summary):
        query.aggregates[alias] = WeekCountAggregate(col, source=source, 
            is_summary=is_summary, **self.extra)


>>> game_objects.extra(select={'week': WEEK_FUNC % '"games_game"."date"'}).values('week').annotate(count=WeekCount('pk'))

But as this API is undocumented and already requires bits of raw SQL, you might be better off using a raw query.

More Related Contents:

  1. Simple Subquery with OuterRef
  2. How do I filter query objects by date range in Django?
  3. Mac OS X – EnvironmentError: mysql_config not found
  4. Django Aggregation: Summation of Multiplication of two fields
  5. How can I filter a Django query with a list of values?
  6. How do I do a not equal in Django queryset filtering?
  7. Install mysql-python (Windows)
  8. How do I get the object if it exists, or None if it does not exist in Django?
  9. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock
  10. Django orm get latest for each group
  11. Django values_list vs values
  12. How to run Django’s test database only in memory?
  13. MySQL Improperly Configured Reason: unsafe use of relative path
  14. Using Django database layer outside of Django?
  15. How to move a model between two Django apps (Django 1.7)
  16. Can one use the Django database layer outside of Django?
  17. “_set” in a queryset object in Django
  18. Count number of records by date in Django
  19. How do i cast char to integer while querying in django ORM?
  20. How to make an Inner Join in django?
  21. QuerySet, Object has no attribute id – Django
  22. Django custom annotation function
  23. How to do many-to-many Django query to find book with 2 given authors?
  24. django filter on the basis of text length
  25. django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb
  26. Install mysqlclient for Django Python on Mac OS X Sierra
  27. Django filter JSONField list of dicts
  28. How to annotate Count with a condition in a Django queryset
  29. How to delete a record in Django models?
  30. Can a dictionary be passed to django models on create?

Leave a Comment