I like to illustrate my challenges. Hopefully it helps. I have the following models/tables:
- Firm
- Worker
- Project
- Manager
- Task
A worker can be in one ore more firms and render service via one or more managers. Managers run projects and assign tasks to the workers while taking note of the firm that those workers represent.
In Django, I defined the relationship as follows:
from django.db import models
from django.utils import timezone
from django.core.validators import MaxValueValidator, MinValueValidator
# Create your models here.
class Firm(models.Model):
"""A company"""
name = models.CharField(max_length=200)
vision = models.TextField()
def __str__(self):
"""About company"""
return self.name
class Worker(models.Model):
"""A worker in a company"""
first_name = models.CharField(max_length=200)
last_name = models.CharField(max_length=200)
firm = models.ForeignKey(
Firm,
on_delete=models.CASCADE,
related_name="workers",
)
on_duty = models.BooleanField(default=True)
def __str__(self):
"""About worker"""
return "{0}, {1}".format(self.last_name, self.first_name)
class Project(models.Model):
"""A Project in a firm"""
name = models.CharField(max_length=200)
deadline = models.DateTimeField(default=timezone.now)
def __str__(self):
"""About Project"""
return self.name
class Manager(models.Model):
"A consultant managing the project"
first_name = models.CharField(max_length=200)
last_name = models.CharField(max_length=200)
designation = models.CharField(max_length=200)
project = models.ForeignKey(
Project,
on_delete=models.CASCADE,
related_name="projmgrs",
)
firm = models.ForeignKey(
Firm,
on_delete=models.CASCADE,
related_name="firmgrs",
)
def __str__(self):
"""About Manager"""
return self.designation
class Task(models.Model):
"""A Specific Task with paying points"""
title = models.CharField(max_length=200)
point = models.IntegerField()
worker = models.ForeignKey(
Worker,
on_delete=models.CASCADE,
related_name="worktasks",
)
manager = models.ForeignKey(
Manager,
on_delete=models.CASCADE,
related_name="mgrtasks",
)
state = models.IntegerField(
default=0,
validators=[
MaxValueValidator(100),
MinValueValidator(0),
]
)
date = models.DateTimeField(default=timezone.now)
def __str__(self):
"""About Task"""
return "{}%".format(self.state)
I want to be able to generate a table listing each worker, points accrued per task and the totals as illustrated in the diagram. I could iterate the worker and run a query per worker … but I suspect there is a way to pull all that data with one query.
Here is what I could come up with:
Manager.objects.get(pk=1).firm.workers.filter(on_duty__exact=True).prefetch_related("worktasks").annotate(points=Sum("worktasks__point")).values()
I see the points are there in the annotation but there is a problem with the above query. It pulls points for all works done by each worker (that’s every project). How do I filter that down to specific projects?