Equivalent of group_concat in Django ORM

Question

We're trying once again to build a web-based tool to help us manage Software Carpentry workshops. We're using Django, which despite its age is still the most widely used (and best documented) web programming framework for Python. It's been a few years since I built anything in it, though, and I'm stumbling over a few things.

For example, my data model looks like this (with lots of irrelevant stuff stripped out):

class Person(models.Model):
    '''Someone we know.'''
    email      = models.CharField(max_length=STR_LONG, unique=True, null=True)

class Event(models.Model):
    '''A workshop or other event.'''
    slug       = models.CharField(max_length=STR_LONG, unique=True)

class Role(models.Model):
    '''The kinds of things people can do at workshops.'''
    name       = models.CharField(max_length=STR_MED)

class Task(models.Model):
    '''Someone did something at some workshop.'''
    event      = models.ForeignKey(Event)
    person     = models.ForeignKey(Person)
    role       = models.ForeignKey(Role)

One of the pages in the application displays information about a particular event. I want to add the names of all the people who were instructors at that event to the page. If I was using SQL directly, I'd write something like:

select   Event.slug, group_contact(Person.email, ', ')
from     Person join Event join Role join Task
on       Person.id=Task.person and Event.id=Task.event and Role.id=Task.role
where    Role.name='instructor'
group by Event.id;

How can I do this with Django's ORM? According to this Stack Overflow question, I can use the 'regroup' tag in the view or build a custom aggregator. The former is complicated by the multi-step nature of the join, and the latter feels... complicated. My instinct is that I ought to be able to attach all the Person objects corresponding to instructors at a particular Event to that event, then loop over them in my view. If you know how to do this, I'd be grateful for a pointer.

*"I want to add the names of all the people who were instructors at that event"*. Naive question from a quick read, but this sounds a lot like the use case for a ForeignKey relationship. Is there any reason you didn't go that route with your data model? The ForeignKey relationship would automatically get you some free ORM functionality in the form of `event.person_set.all()' or similar... https://docs.djangoproject.com/en/1.4/topics/db/examples/many_to_one/ — abought, Dec 08 '14 at 14:20
Problem with (my understanding of) ForeignKey is that this is a tripartite relationship - I don't see how to get all the instructors groups with the event via the task having the right role without looping. — Greg Wilson, Dec 08 '14 at 19:42

score 2 · Answer 1 · answered Dec 08 '14 at 14:20

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.

I was hoping to avoid looping over events (since I used to teach my students that if you looped a query, you were doing it wrong). And yes, we'll potentially have 50-200 events/page. — Greg Wilson, Dec 08 '14 at 19:40

Joel Crocker · Answer 2 · 2014-12-09T04:00:54.093

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.

score 1 · Answer 3 · answered Dec 09 '14 at 14:55

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

score 0 · Answer 4 · answered Dec 08 '14 at 13:34

0

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

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

answered Dec 08 '14 at 13:34

biobirdman

4,060
1
17
15

You should always reduced to a minimum sql commands when you have ORM. – Sławek Kabik Dec 08 '14 at 13:39
@Slawek Obviously his task, is beyond what django ORM can provide. Read the first paragraph from the link that I have shown : When the model query APIs don’t go far enough, you can fall back to writing raw SQL. Django gives you two ways of performing raw SQL queries: you can use Manager.raw() to perform raw queries and return model instances, or you can avoid the model layer entirely and execute custom SQL directly. Hence instead of 'hacking' your way thru and spending hours to get it working thru django ORM, use what there is ! How is this even a down vote. This solve his problem! – biobirdman Dec 08 '14 at 13:42
Like I wrote.. you should always use ORM when you have it on the project. When you are thinking about SQL commands.. stop coding and go to improve your knowledge about current ORM :) – Sławek Kabik Dec 08 '14 at 13:50
1

@Slawek Now slawek, given your extensive knowledge on ORM, will you be kind enough to show us an answer ? – biobirdman Dec 08 '14 at 13:51
no way... I don't have time to refactoring his code. – Sławek Kabik Dec 08 '14 at 14:01
@slawek Precisely my point, instead of having to do any refactoring (that requires time), Im suggesting that he should implement what he already knew and django provide him with a way of doing. – biobirdman Dec 08 '14 at 14:06
Yeah I know.. but what will you do with this programmer when you get this code to refactoring in your job? :) – Sławek Kabik Dec 08 '14 at 14:11
1

@Slawek Ah.. a convenient excuse :) But I agree with you. Totally acceptable! – biobirdman Dec 08 '14 at 14:38
1

"It's possible but I can't be bothered to show you how" is the kind of answer that drives a lot of novices away from participation in forums like this. – Greg Wilson Dec 08 '14 at 19:44

score 0 · Answer 5 · answered Dec 08 '14 at 14:24

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).

Equivalent of group_concat in Django ORM

5 Answers5