django query to retrieve dictionary of children values keyed by parent name?

Question

With the following models with a one-to-many relationship, where a kid can have some collection of toys:

class Kid(models.Model):
    name = models.CharField(max_length=200)

class Toy(models.Model):
    name = models.CharField(max_length=200)
    material = models.CharField(max_length=200)
    owner = models.ForeignKey(Kid)

I would like to generate a dictionary with kid names as keys and a list of tuples as values.

The following query gets me the list of tuples as shown:

kids = Kid.objects.all()
kids.filter( toy__name__in[ some list of names ] ).\
   values_list( 'name', 'toy__name', 'toy__material' )

[ ( 'billy', 'hotwheelscar', 'metal' ),
  ( 'billy', 'actionFigure', 'plastic' ),
  ( 'sam', 'hotwheelscar', 'metal' ),
  ( 'sam', 'doll', 'plastic' ),
  ( 'sam', 'dollHouse', 'plastic' ),
  ( 'jimmy', 'hotwheelscar', 'metal' ),
  ( 'jimmy', 'actionFigure', 'plastic' ) ]

But is there a way I could get these results in a form like the following:

{ 'billy': [ ( 'hotwheelscar', 'metal' ), ( 'actionFigure', 'plastic' ) ],
  'sam':   [ ( 'hotwheelscar', 'metal' ), ( 'doll', 'plastic' ), ( 'dollhouse', 'plastic' ) ],
  'jimmy': [ ( 'hotwheelscar', 'metal' ), ( 'actionFigure', 'plastic' ) ] }

Without having to first retrieve the values_list of tuples as I have done and then iterate over them creating a dictionary myself? The structure of the values being represented in a list of tuples is not important, it could be a nested dictionary or something else but I just want to have all the toys for a kid available by keying the resulting dictionary by kid name.

Thanks in advance.

Possible duplicate of [Annotate a comma-separated list of related items onto Django queryset](https://stackoverflow.com/questions/38017076/annotate-a-comma-separated-list-of-related-items-onto-django-queryset) — Brown Bear, Apr 13 '18 at 17:15
Very cool, I didn't yet know about aggregate functions, thanks. — bwrabbit, Apr 13 '18 at 17:21

score 1 · Accepted Answer · answered Apr 13 '18 at 17:20

The query won't return a dictionary, but you can write it perhaps more succintly with a dictionary comprehension:

{kid.name: list(kid.toy_set.filter(name__in=names).values_list('name', 'material')) 
 for kid in Kid.objects.filter(toy__name__in=names)}

This could be bad for performance, depending on how much data you need to run over, since it runs an extra query per kid to get the toys. If so, running the query you have and processing the list into a dictionary in Python after is likely faster.

Thanks, this solution works well for me since I do not have that many kids but a lot of toys per kid. :) — bwrabbit, Apr 13 '18 at 17:50

django query to retrieve dictionary of children values keyed by parent name?

1 Answers1