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

Probably you don't need to use annotate but can use regroup tag inside template.

Going from the end. All the information which you want to display on the page can be retrieved from Registration model.

Get all the registrations filtered by date of the event:

registrations = Registration.objects.filter(event__date = date)

Afterwards you have to regroup by user in template.

However I see following problems:

1. I am not sure that regroup tag is working correctly with querysets. So probably you have to convert data into list. However, I have found this answer in which querysets are used.
2. Even if you will regroup you need some logic in template to define the block for the event.

Answer 2

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 %}