Can you use the ORM to join rows to create result sets?

Using Django
1

If I had data like;

id – line_id – line_text
1 – 1 – text
2 – 1 – text
3 – 1 – text
4 – 2 – text
5 – 2 – text
6 – 3 – text
7 – 3 – text

Is it possible to use aggregation or some other method to group by line_id and end up with three results rather than the seven rows? So basically break the above into three result sets like;

Set one:
1 – 1 – text
2 – 1 – text
3 – 1 – text

Set two:
4 – 2 – text
5 – 2 – text

Set three;
6 – 3 – text
7 – 3 – text

Then be able to loop through all sets?

2

I may be onto something. This seems to work, but I need to print everything out and test looping to be sure.

c = Category.objects.annotate(arr=StringAgg('category_name', delimiter='')).values('line__cateogry_id')

result from shell:
<QuerySet [{‘line__cateogry_id’: 1}, {‘line__cateogry_id’: 2}]>

3

You could query all the relevant items in one query, then group them in memory with itertools.groupby.

from itertools import groupby
from operator import attrgetter

for category_id, lines in groupby(Line.objects.order_by('category_id'), key=attrgetter('category_id')):
    ...
4

Pretty slick. Thanks.

5

This may be a good candidate for a Common Table Expression (postgres docs) and django-cte.

6

Yes you can definitely do it with a CTE. I’m learning the ORM and just experimenting so to speak. I didn’t know django had a package for CTEs.

Thanks.