Django - create dictionary of associated objects

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?

Technology

Result

Skill

User

select * from user where skill_level>2

Result.objects.filter(technology__exact='Cisco').filter(skill__gte=1)

1 Answer
1

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'...


related:

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy