Link Data From One Table To Another Which Are Joined By An Intermediate Table

user_13402819 0
Viewed 192

I've had a lot of difficulty designing my db but have boiled my problem down to the core issue.

Students can enroll in Packages. Packages are groups of many Modules.

When a Student enrolls in a Package I need an Extra_Data table to be automatically generated for EACH Module in the Package, linked to the Student. This will hold data unique to one Module and one Student.

class Student(models.Model):
    package = models.ManyToManyField(Package, blank=True)

class Module(models.Model):
    task_type = models.CharField(max_length=200)
    topic = models.CharField(max_length=200)

class Package(models.Model):
    individual_modules = models.ManyToManyField(Module, blank=True)
    name = models.CharField(max_length=200)

How I display Packages:

{% for package in user.student.module_packages.all %}
<table>
    <th>Module</th>
    <th>Task Type</th>
    <th>Topic</th>
    #<th>Complete</th> not yet implemented
    #<th>User Files</th> not yet implemented
    #<th>Teachers Files</th> not yet implemented

    <h3 class="account-heading">{{ package.name }}</h2>
    
    {% for module in package.individual_modules.all %}
    <tr>
        <td>{{ module.id }}</td>
        <td>{{ module.task_type }}</td>
        <td>{{ module.topic }}</td>
        #<td>{{ module.extra_data.module_complete }}</td> this is an example of 
        # how I would expect to be able to handle the extra_data tables contents, 
        # though its turning out to be a real challenge
    </tr>
    {% endfor %}

{% endfor %}

I have tried many things, including using a Django through relationship between Student and Module, going 'through' Extra_Data. This didn't work as it directly links Students with Modules - when a Student is given a Package in Django Admin no Extra_Data tables are created for the Modules in the Package.

-- I think this is irrelevant to my main issue, but Students can also enroll in individual Modules --

Thank you very much.

relational-database database-schema database-design django
1 Answers

How about, instead of saving the modules and packages under Student you save all the data for this relationship under ExtraModuleData or a better name StudentModule. This keeps the student's module history separate from the Student model.

So, like:

# a lot of this is short-hand
class StudentModule(models.Model):
    student = ForeignKey(Student)
    module = ForeignKey(BaseModule)
    package = ForeignKey(Package, required=False)
    files = ManyToMany(StudentFile, related_name='uploads')
    completed = Boolean()

You can list out the individual modules and packages, then loop through them using StudentModule's module and package fields to 'select' the active ones.

Within the Django Admin Student context, that means you'd only need to add an inline for StudentModule, with each row giving the select box for modules and packages. A StudentModule can have an empty package field if it's not under a package. Preventing the need to save student package data separately.

For the uploads and other many-to-many data, I'd recommend saving them as their own models like below. This would be easier to wrangle outside the admin, since you can just query by student_module_id or even student_module__student__id if you don't have module id. And within the admin you can add them via inlines.

class StudentFile(models.Model):
    file = FileField()
    student_module = ForeignKey(StudentModule)
    ..other data

Just to add extra context, your HTML can then be (assuming you passed a st_modules = StudentModule.objects.filter(student=user) variable to context.)

{% for sm in st_modules %}
   {{ sm.module.id }}
   {{ sm.module.task }}
   {{ sm.module.topic }}
   {% if sm.package }} {{ sm.package.name }} {% endif %}
   {% for file in sm.uploads.all }} {{ file.name }} {% endfor %}
{% endfor %}

If you need to group the modules by package, a useful tag Django templates have is regroup. You can use this like so:

{% regroup st_modules by package as package_list %}
{% for package, modules in package_list %}
  {{ package.grouper }} # package name if you need it
  {% for m in modules %}
     {{ m.module.id }} # etc
  {% endfor %}

Hope that helps.

user_13063360
Related