Django: a related model values list as part of a values list query

I am trying to figure out if its possible to set up a query which would act something like Model.objects.values_list('id', 'related_model__ids') and return something like [(1L, [2L, 6L]), (2L, [1L,]), (3L, [1L, 3L, 4L, 5L]), ...] Here are some specifics:

class Player(models.Model):
    positions = models.ForeignKey(Position)
    ...

>>> # players 31, 32, 33 play multiple positions, and I want a result to be
>>> # [(player.id, [a list of position.id for all positions]), ...]

>>> Player.objects.filter(id__in=[31, 32, 33]).values_list('id', 'positions__id')
[(31L, 13L), (31L, 7L), (32L, 13L), (32L, 8L), (33L, 13L), (33L, 7L)]

This values_list query produces multiple tuples for the same id value, two entries for id=31, id=32 and id=33. When I make a values_list query on a specific player (id=31), I get the list of position ids I would like:

>>> p31 = Player.objects.get(id=31)
>>> position_ids = p31.positions.values_list('id', flat=True)
>>> type(position_ids)
<class 'django.db.models.query.ValuesListQuerySet'>
>>> position_ids
[13L, 7L]
>>>
>>> # I want [(31L, [13L, 7L]), (32L, [13L, 8L), (33L, [13L, 7L)]
>>> # Without have to do something like a list comprehension:
>>> # [(p.id, list(p.positions.values_list('id', flat=True))
>>> #     for p in Player.objects.filter(id__in=[31, 32, 33])]

No doubt a list comprehension will yield the result I would like. I wanted to avoid multiple individual queries to the database with each list(p.positions.values_list('id', flat=True)) forcing the resolution of a ValuesListQuerySet into an actual list. Of course, if I don't force the resolution, an operation on the result of the list comprehension would seem to execute a fresh p.positions.valuse_list('id', flat=True) query.

Solution

Yeah, I wish there were some grouping query method as well.

I recently had to do something similar with highly nested models to avoid some crippling database calls. I ended up using values() to efficiently get all the, umm, values I needed. Then, stitched everything together as a dictionary in pure Python. It ended up miles more efficient, but of course the customization has its own maintenance downside.

The gist of it, using the Author/Book models:

# models.py
class Author(models.Model):
    name = models.CharField()

class Book(models.Model):
    name = models.CharField()
    authors = models.ManyToManyField(Author)

Then in a view (or a shell in my case) construct the dictionary you can pass to your template:

>>> authors = Author.objects.prefetch_related('book_set').values('id', 'book')
>>> print authors
    [{'book': 1, 'id': 1}, {'book': 2, 'id': 1}, {'book': 2, 'id': 2}]
>>> author_dict = {}
>>> for a in authors:
    if a['id'] not in author_dict:
        author_dict[a['id']] = set()
    author_dict[a['id']].add(a['book'])
>>> print author_dict
    {1: set([1, 2]), 2: set([2])}

Not the answer you probably wanted, but it's the only thing I have come up with. I'd love to hear if anyone else has solved the problem with some native Django method.

Hope this helps!