Annotating related and multi-filtered objects in Django

Question

I have a queryset of profiles:

Model:

class Profile(models.Model):
    user = models.OneToOneField(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, unique=True)
    ...

View:

Profile.objects.select_related('user')

Each user/profile can register for multiple events per day:

Models:

class Event(models.Model):

    title = models.CharField(max_length=120)
    date = models.DateField(default=default_event_date)
    ...


class Registration(models.Model):

    event = models.ForeignKey(Event)
    student = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    block = models.ForeignKey(Block, on_delete=models.CASCADE)
    ....

Given a Date how can I annotate (?I think that's what I want?) one Registration object per block (filtered on the user/profile and the Event__Date)

In the end, what I'm trying to output in my template is something like this:

For Date: 19 Dec 2016

User/Profile    Block A      Block B   ...
user1           None         None
user2           Event1       Event2
user3           Event3       None
...

EDIT

Attempt 1. Here's my first attempt to accomplish this. I suspect this is horribly inefficient and would be extremely slow in production, but at least it works. If anyone can provide a more efficient and elegant solution, it would be appreciated! (Note that this also includes a filter on the User's Profile model for homeroom_teacher that was not included in the original question, but I've left here because this is the code that's working)

Registration model manager

class RegistrationManager(models.Manager):

def homeroom_registration_check(self, event_date, homeroom_teacher):
    students = User.objects.all().filter(is_staff=False, profile__homeroom_teacher=homeroom_teacher)
    students = students.values('id', 'username', 'first_name', 'last_name')
    # convert to list of dicts so I can add 'annotate' dict elements
    students = list(students) 

    # get queryset with events? optimization for less hits on db
    registrations_qs = self.get_queryset().filter(event__date=event_date, student__profile__homeroom_teacher=homeroom_teacher)

    # append each students' dict with registration data
    for student in students:
        user_regs_qs = registrations_qs.filter(student_id=student['id'])

        for block in Block.objects.all():
            # add a new key:value for each block
            try:
                reg = user_regs_qs.get(block=block)
                student[block.constant_string()] = str(reg.event)
            except ObjectDoesNotExist:
                student[block.constant_string()] = None

    return students

Template Note that block.constant_string() --> "ABLOCK", "BBLOCK", etc, this is hardcoded in the block.constant_string() method and I'm not sure how to get around this either.

{% for student in students %}
  <tr >
    <td>{{ student.username }}</td>
    <td>{{ student.first_name }}</td>
    <td>{{ student.last_name }}</td>
    <td>{{ student.ABLOCK|default_if_none:'-' }}</td>
    <td>{{ student.BBLOCK|default_if_none:'-' }}</td>
  </tr>
{% endfor %}

Answer 1

To solve the problem of the harcoded names, I'd slightly modify your solution to look like this:

def homeroom_registration_check(event_date, homeroom_teacher):
    students = User.objects.filter(
        is_staff=False,
        profile__homeroom_teacher=homeroom_teacher,
    )
    block_ids = Block.objects.values('id')
    for student in students:
        table_row = []
        for block_id in block_ids:
            try:
                reg = Registration.objects.get(
                    student=student,
                    block=block_id,
                    event__date=event_date,
                )
                table_row.append(reg.event)
            except ObjectDoesNotExist:
                table_row.append(None)
        yield (student, table_row)

I'd take it out from the model manager and put it in views.py or a separate file (like table.py). Seems cleaner to me, but that's just an opinion - you could put this code in the model manager and it would run anyway.

Then in your template:

{% for student, row in homeroom_reg_check %}
    <tr>
        <td>{{ student.username }}</td>
        <td>{{ student.other_data }}</td>
        {% for event in row %}
            <td>{{ event.name|default_if_none:'-' }}</td>
        {% endfor %}
    </tr>
{% endfor %}