[Django]-Equivalent of group_concat in Django ORM

I don’t think you need such a complicated query.

For any particular event, you can do this:

instructors = Task.objects.filter(event=my_event, role__name='instructor').select_related('person').values_list('person__email')

which will get you a list of emails for all Persons related to Tasks for that event with the ‘instructor’ role. Admittedly, this is one query per event, rather than one single massive query, but unless you’re planning on showing hundreds of events per page the trade-off in terms of clarity is probably worth it.

How about the following (Django 1.7+):

from django.db.models import Prefetch

prefetch = Prefetch(
    'task_set',
    queryset=Task.objects.filter(role__name='instructor')
    .select_related('person'), to_attr='instructor_tasks'
)
events = Event.objects.all().prefetch_related(prefetch)

for event in events:
    print event

    for task in event.instructor_tasks:
        print task.person.email

Using prefetch_related in this way should prevent O(n) DB queries (despite the looping).

Django DB query logging output for the above query/loop (with an admittedly tiny data set of 2 events):

DEBUG django.db.backends: (0.001) SELECT "myapp_event"."id", "myapp_event"."slug" FROM "myapp_event"; args=()
DEBUG django.db.backends: (0.001) SELECT "myapp_task"."id", "myapp_task"."event_id", "myapp_task"."person_id", "myapp_task"."role_id", "myapp_person"."id", "myapp_person"."email" FROM "myapp_task" INNER JOIN "myapp_role" ON ( "myapp_task"."role_id" = "myapp_role"."id" ) INNER JOIN "myapp_person" ON ( "myapp_task"."person_id" = "myapp_person"."id" ) WHERE ("myapp_role"."name" = 'instructor' AND "myapp_task"."event_id" IN (1, 2)); args=('instructor', 1, 2)

As such, this query code should continue to only issue two DB queries even as the number of events grows.

In the upcoming Django 1.8 you could just implement GroupConcat expression, and then the query would look like:

Event.objects.values('slug').annotate(emails=GroupConcat('task__person__email'))

The .values().annotate() combination sets the GROUP BY to slug, and of course the GroupConcat implementation does the actual aggregation.

For how to write the GroupConcat implementation check out https://docs.djangoproject.com/en/dev/ref/models/expressions/#writing-your-own-query-expressions

Since you are already proficient with mysql commands, try executing Raw mysql command from Django.

https://docs.djangoproject.com/en/dev/topics/db/sql/

The usual solution here is not to try to optimize and just do one lookup per event:

for event in Event.objects.filter(**some_lookup):    
    instructors = Person.objects.filter(
        task__role__name="instructor", 
        task__event=event
        ).values_list("name", flat=True)
    print "Event: %s - instructors : %s" % (event.slug, ", ".join(instructors))

If and when it becomes a performance bottleneck it will be time to find a better solution (which may or not include falling back to raw sql).