Django aggregate by split string

Question

I would like to aggregate by a substring of one of my fields.

models.py

class SomeModel(models.Model):
    symbol = models.CharField(db_column='SYMBOL', max_length=16)
    number = models.IntegerField(db_column='NUMBER')

Where symbol will be something like;

'symbol': 'foo.bar', 'number':5
'symbol': 'foo.foo', 'number':10
'symbol': 'some.water', 'number':1
'symbol': 'some.milk', 'number':1

What I would like is to aggregate by;

symbol.split('.')[0]

With the end result being something like;

'symbol': 'foo', 'number': 15
'symbol': 'some', 'number': 2

I've looked at the annotate and aggregate documentation but I am struggling with this. Thanks.

Check the answer from Shashank Singla `https://stackoverflow.com/questions/10340684/group-concat-equivalent-in-django` — Windsooon, Sep 15 '16 at 06:31
Thanks, I've solved the problem using a combination of this comment and the answer below. — user2019553, Sep 30 '16 at 08:17

score 0 · Accepted Answer · answered Sep 15 '16 at 06:17

0

Have you tried

SomeModel.objects.extra(select={'symbol': 'SUBSTRING_INDEX(symbol, ".")'})?

answered Sep 15 '16 at 06:17

Andrey Shipilov

thanks, this works to correctly identify the substring but I am unable to use `aggregate` on a parameter introduced by `extra` – user2019553 Sep 15 '16 at 07:48

1 Answers1