django-ormex 0.2.1

GroupConcat

Works like Concat, but for concatenating field values of related ManyToMany model. For instance, you may use it if you have an Author model as ManyToMany relation in the Book model (Book.authors = ManyToManyField(Author)) and you want to have concatenated list of all authors coupled to a given book.

Given the following models:

class Publisher(models.Model):
    """Publisher."""

    name = models.CharField(max_length=255)
    address = models.CharField(max_length=255)
    city = models.CharField(max_length=255)
    state_province = models.CharField(max_length=255)
    country = models.CharField(max_length=255)
    website = models.URLField(max_length=255)


class Author(models.Model):
    """Author."""

    salutation = models.CharField(max_length=255)
    name = models.CharField(max_length=255)
    email = models.EmailField(max_length=255)
    headshot = models.ImageField(upload_to='authors', null=True, blank=True)


class Book(models.Model):
    """Book."""

    title = models.CharField(max_length=255)
    authors = models.ManyToManyField('books.Author', related_name='books')
    publisher = models.ForeignKey(Publisher, related_name='books')
    publication_date = models.DateField()
    isbn = models.CharField(max_length=255, unique=True)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    pages = models.PositiveIntegerField(default=200)
    stock_count = models.PositiveIntegerField(default=30)

We could use GroupConcat as follows:

from ormex.aggregations import GroupConcat

book = Book.objects.all() \
        .values('id',
                'title',
                'pages',
                'price',
                'publisher__id',
                'publisher__name') \
        .annotate(
            authors__name=GroupConcat('authors__name', separator=', ')
        ) \
        .first()

Output would look as follows:

{
    'authors__name': 'Finn Janssen, Dan Dijkman, Merel Wolf, Evy de Jong',
    'id': 14,
    'pages': 83,
    'price': Decimal('62.13'),
    'publisher__id': 19,
    'publisher__name': 'Rijn, de Bruyn and Verharen',
    'title': 'Laboriosam officia temporibus facere omnis odit.'
}

GroupConcat accepts an optional argument order_by which can be used for tuning the sorting order of the resulted list of strings. In case if self is given as value, sorted by the same field. In order to sort the list of authors by name from the example above, do:

book = Book.objects.all() \
        .values('id',
                'title',
                'pages',
                'price',
                'publisher__id',
                'publisher__name') \
        .annotate(
            authors__name=GroupConcat('authors__name',
                                      separator=', ',
                                      order_by='self')
        ) \
        .first()

Output would look as follows:

{
    'authors__name': 'Dan Dijkman, Evy de Jong, Finn Janssen, Merel Wolf',
    'id': 14,
    'pages': 83,
    'price': Decimal('62.13'),
    'publisher__id': 19,
    'publisher__name': 'Rijn, de Bruyn and Verharen',
    'title': 'Laboriosam officia temporibus facere omnis odit.'
}