Django - create dictionary of associated objects

Question

I'm attempting to build a query which will return a list of all technologies and the score of each user associated with each technology. This is based on the following models:

class Technology(models.Model):
    technology = models.CharField(max_length=64)

class Result(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    technology = models.ForeignKey(technology, on_delete=models.CASCADE)
    skill_level = models.ForeignKey(skill, to_field='level', on_delete=models.CASCADE)

class Skill(models.Model):
    level = models.IntegerField(unique=True)

Essentially I'd like to see somthing similar to the following:

{technology: Cisco, users: {Alex: {skill_level: 1}}, {James: {skill_level: 2}}

I'm not sure whether this can be acheived with a simple query or whether custom joins will need to be done?

I'm also not sure whether I should be building this on the Technology model or the Result model?

How is the `Skill` object connected to a `User` object? for example `select * from user where skill_level>2`? — jmunsch, Sep 16 '18 at 00:37
@jmunsch Hey - Thanks for the reply. It's connected only via the Result model currently as the result holds each users skill level against each technology? Does the question make sense with what I'm attempting to achieve? — Alex, Sep 16 '18 at 00:50
@jmunsch I should be able to acheive what I'm looking to acheive using a queryset on the result model. However, within the template the code would be extremely unmanageable. If I'm able to create the dataset as I'd like then it would be two simple for loops. I've also added an image here to try and explain what I'm trying to acheive (Warning: Created in MS Paint): https://i.imgur.com/s14snwZ.png — Alex, Sep 16 '18 at 00:56
So like `Result.objects.filter(technology__exact='Cisco').filter(skill__gte=1)`? — jmunsch, Sep 16 '18 at 01:00
@jmunsch Kind of, I'd like to see a list of all technologies (Not just Cisco) and all skill levels. However, I'd like to create an array of all users scores for each technology? — Alex, Sep 16 '18 at 01:02
@jmunsch or alternatively a list of all users with their scores for each technology. Which would be more or less the same. Thanks again for the reply — Alex, Sep 16 '18 at 01:04

score 1 · Accepted Answer · answered Sep 16 '18 at 02:50

maybe something similar to this (see towards the bottom of the tests.py file):

models.py:

from django.db import models

class User(models.Model):
    name = models.CharField(max_length=64)

class Technology(models.Model):
    technology = models.CharField(max_length=64)

class Skill(models.Model):
    level = models.IntegerField(unique=True)

class Result(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    technology = models.ForeignKey(Technology, on_delete=models.CASCADE)
    skill_level = models.ForeignKey(Skill, to_field='level', on_delete=models.CASCADE)

tests.py:

from django.test import TestCase
from .models import Result, Technology, User, Skill
from django.forms.models import model_to_dict

class SimpleTest(TestCase):
    def test_join(self):
        u = []
        s = []
        for x in range(0,2):
            _u = User(name='User{}'.format(str(x)))
            _s = Skill(level=x)
            _u.save()
            _s.save()
            u.append(_u)
            s.append(_s)
        t = []
        for x in range(0, 4):
            _t = Technology(technology='blah{}'.format(str(x)))
            _t.save()
            t.append(_t)
        for x in range(0, 8):
            r = Result(user=u[x%2], technology=t[x%4], skill_level=s[x%2])
            r.save()

        x = Technology.objects.select_related()
        print(x.query)
        for item in x:
            print(item.result_set.values())
            print(item.result_set.all().query)
            for ok in item.result_set.all():
                print(model_to_dict(ok))

test output:

➜  mysite python manage.py test
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
SELECT "mysite_technology"."id", "mysite_technology"."technology" FROM "mysite_technology"
<QuerySet [{'id': 1, 'user_id': 1, 'technology_id': 1, 'skill_level_id': 0}, {'id': 5, 'user_id': 1, 'technology_id': 1, 'skill_level_id': 0}]>
SELECT "mysite_result"."id", "mysite_result"."user_id", "mysite_result"."technology_id", "mysite_result"."skill_level_id" FROM "mysite_result" WHERE "mysite_result"."technology_id" = 1
{'id': 1, 'user': 1, 'technology': 1, 'skill_level': 0}
{'id': 5, 'user': 1, 'technology': 1, 'skill_level': 0}
<QuerySet [{'id': 2, 'user_id': 2, 'technology_id': 2, 'skill_level_id': 1}, {'id': 6, 'user_id': 2, 'technology_id': 2, 'skill_level_id': 1}]>
SELECT "mysite_result"."id", "mysite_result"."user_id", "mysite_result"."technology_id", "mysite_result"."skill_level_id" FROM "mysite_result" WHERE "mysite_result"."technology_id" = 2
{'id': 2, 'user': 2, 'technology': 2, 'skill_level': 1}
{'id': 6, 'user': 2, 'technology': 2, 'skill_level': 1}
<QuerySet [{'id': 3, 'user_id': 1, 'technology_id': 3, 'skill_level_id': 0}, {'id': 7, 'user_id': 1, 'technology_id': 3, 'skill_level_id': 0}]>
SELECT "mysite_result"."id", "mysite_result"."user_id", "mysite_result"."technology_id", "mysite_result"."skill_level_id" FROM "mysite_result" WHERE "mysite_result"."technology_id" = 3
{'id': 3, 'user': 1, 'technology': 3, 'skill_level': 0}
{'id': 7, 'user': 1, 'technology': 3, 'skill_level': 0}
<QuerySet [{'id': 4, 'user_id': 2, 'technology_id': 4, 'skill_level_id': 1}, {'id': 8, 'user_id': 2, 'technology_id': 4, 'skill_level_id': 1}]>
SELECT "mysite_result"."id", "mysite_result"."user_id", "mysite_result"."technology_id", "mysite_result"."skill_level_id" FROM "mysite_result" WHERE "mysite_result"."technology_id" = 4
{'id': 4, 'user': 2, 'technology': 4, 'skill_level': 1}
{'id': 8, 'user': 2, 'technology': 4, 'skill_level': 1}
.
----------------------------------------------------------------------
Ran 1 test in 0.011s

OK
Destroying test database for alias 'default'...

source for model_to_dict()
How do I convert a Django QuerySet into list of dicts?

Thank you so much! Technology.objects.select_related() is exactly what I needed. I think my issue previously was that I was attempting to use .values() and filter() which was extending the list of technologies. I've been pulling hair out for the past ~8 hours trying to get this to work simply and efficiently — Alex, Sep 16 '18 at 04:36
The only issue I have now is that if you use a filter such as Technology.objects.select_related().filter(result__user__is_active=True) then it returns a list that's N times as long as the many of users? Not sure how I can filter the data now? — Alex, Sep 16 '18 at 05:03

Django - create dictionary of associated objects

1 Answers1