Aggregates

MySQL-specific database aggregates for the ORM.

The following can be imported from django_mysql.models.

class BitAnd(column)

Returns an int of the bitwise AND of all input values, or 18446744073709551615 (a BIGINT UNSIGNED with all bits set to 1) if no rows match.

Docs: MySQL / MariaDB.

Example usage:

>>> Book.objects.create(bitfield=29)
>>> Book.objects.create(bitfield=15)
>>> Book.objects.all().aggregate(BitAnd('bitfield'))
{'bitfield__bitand': 13}
class BitOr(column)

Returns an int of the bitwise OR of all input values, or 0 if no rows match.

Docs: MySQL / MariaDB.

Example usage:

>>> Book.objects.create(bitfield=29)
>>> Book.objects.create(bitfield=15)
>>> Book.objects.all().aggregate(BitOr('bitfield'))
{'bitfield__bitor': 31}
class BitXor(column)

Returns an int of the bitwise XOR of all input values, or 0 if no rows match.

Docs: MySQL / MariaDB.

Example usage:

>>> Book.objects.create(bitfield=11)
>>> Book.objects.create(bitfield=3)
>>> Book.objects.all().aggregate(BitXor('bitfield'))
{'bitfield__bitxor': 8}
class GroupConcat(column, distinct=False, separator=', ', ordering=None)

An aggregate that concatenates values from a column of the grouped rows. Useful mostly for bringing back lists of ids in a single query.

Docs: MySQL / MariaDB.

Example usage:

>>> from django_mysql.models import GroupConcat
>>> author = Author.objects.annotate(
...     book_ids=GroupConcat('books__id')
... ).get(name="William Shakespeare")
>>> author.book_ids
"1,2,5,17,29"

Warning

MySQL will truncate the value at the value of group_concat_max_len, which by default is quite low at 1024 characters. You should probably increase it if you’re using this for any sizeable groups.

group_concat_max_len docs: MySQL / MariaDB.

Optional arguments:

distinct=False

If set to True, removes duplicates from the group.

separator=','

By default the separator is a comma. You can use any other string as a separator, including the empty string.

Warning

Due to limitations in the Django aggregate API, this is not protected against SQL injection. Don’t pass in user input for the separator.

ordering=None

By default no guarantee is made on the order the values will be in pre-concatenation. Set ordering to 'asc' to sort them in ascending order, and 'desc' for descending order. For example:

>>> Author.objects.annotate(
...     book_ids=GroupConcat('books__id', ordering='asc')
... )