Django get a list of related id's linked to each parent record id in a query set?

Question

I have a relationship client has many projects.

I want to create a dictionary of the form:

{
    'client_id': ['project_id1', 'project_id2'],
    'client_id2': ['project_id7', 'project_id8'],
}

what I tried was;

clients_projects = Client.objects.values_list('id', 'project__id')

which gave me:

<QuerySet [(3, 4), (3, 5), (3, 11), (3, 12), (2, 3), (2, 13), (4, 7), (4, 8), (4, 9), (1, 1), (1, 2), (1, 6), (1, 10)]>

which I can cast to a list with list(clients_projects):

[(3, 4),
 (3, 5),
 (3, 11),
 (3, 12),
 (2, 3),
 (2, 13),
 (4, 7),
 (4, 8),
 (4, 9),
 (1, 1),
 (1, 2),
 (1, 6),
 (1, 10)]

Hey @surfer190, I was wondering, did you find any of the answers helpful? — John Moutafis, Nov 23 '18 at 11:26

score 1 · Answer 1 · answered Apr 10 '18 at 13:08

1

Assuming that projects is the ReverseManyToOneDescriptor attached to the Client model, you can write that:

clients_projects = { c.id : c.projects.values_list('id', flat=True) for c in Client.objects.all() }

answered Apr 10 '18 at 13:08

albar

3,020
1
14
27

I used my solution along with this answer: https://stackoverflow.com/questions/1233546/python-converting-list-of-tuples-into-a-dictionary#answer-1233588...I wonder if your solution is faster – tread Apr 10 '18 at 13:18

John Moutafis · Answer 2 · 2018-04-10T14:22:42.500

This problem is quite similar to this one: Django GROUP BY field value.
Since Django doesn't provide a group_by (yet) you need to manually replicate the behavior:

result = {}
for client in Client.objects.all().distinct():
    result[client.id] = Client.objects.filter(id=client.id)
                                      .values_list('project__id', flat=True)

Breakdown:

Get a set of distinct clients from you Client model and iterate through them.
(You can also order that set if you wish, by adding .order_by('id') for example)
Because you need only the project__id as a list, you can utilize values_list()'s flat=True argument, which returns a list of values.

Finally, result will look like this:

{
    'client_1_id': [1, 10, ...],
    'client_5_id': [2, 5, ...],
    ...
}

There is a module that claims to add GROUP BY functionality to Django: https://github.com/kako-nawao/django-group-by, but I haven't used it so I just list it here and not actually recommend it.

Django get a list of related id's linked to each parent record id in a query set?

2 Answers2