Group concat using django ORM

Question

I need to fire following query using ORM

SELECT GROUP_CONCAT( af.name ) AS item, ac.name AS categories
FROM admin_fooditem af
JOIN admin_category ac ON ( ac.id = af.category_id ) 
GROUP BY ac.id

The output is as below

item                              Categories

Tuborg beer                       Beverages
Mutter Paneer,Palak Paneer        Vegeterian entries
Coke,Fanta                        Soft drinks

The original tables are

admin_category

        id  name    
        11  Softdrinks
        5   Vegeterian Entry
        2   Beverages

admin_fooditem

        id  name         category_id
        1   Palak Paneer    5
        2   Mutter Paneer   5
        5   Tuborg beer     2
        6   Coke            11
        7   Fanta           11

I have used simple join query(FoodItem.objects.select_related('Category').all()) and then regroup tag in template , but now I need json output of the above sql, I am clueless so I am using raw sql for now

Thanks!

please have a look on edited question , i have explained the scenario, any help will be appreciated — sumit, May 15 '12 at 08:11

score 1 · Answer 1 · answered May 15 '12 at 17:04

If you are using django 1.4, you can call prefetch_related() on your Categories QuerySet and it would probably be slightly less efficient, as @ColeMaclean pointed out.

If you are using older django version and these queries will be executed frequently, stick with raw SQL. The only other way, using extra() on QuerySet won't work, as you need custom group by statement (I actually started writing an answer that suggested using extra() but found out it can't add GROUP BY parameters).

Without prefetch_related() the code by Cole will hit database on each category. It's easy to execute a few hundred database queries per page by using code like this in production.

Ooh, good catch, forgot to add prefetch_related(). That's important. — Cole Maclean, Oct 28 '12 at 05:23

score 0 · Answer 2 · answered May 15 '12 at 09:32

You can use raw queries if you want in Django, but I think in this case you should probably just use the ORM. It's (very slightly) less efficient but much easier to work with.

E.g, just do something like (untested, may not work):

json_dict = {}
for category in Category.objects.all():
    json_dict.update({
        'category': category.name,
        'items': ','.join(category.items.values_list('name', flat=True)),
    })

Then JSON encode the result.

Group concat using django ORM

2 Answers2