SELECT Distinct on a many-to-many model

Using Django Using the ORM
1

Hello

I have this many-to-many model:

class Registers(models.Model):

    class Meta:
        verbose_name_plural = 'Registers'

    lesson_at = models.DateField(help_text="Data da Aula:", verbose_name="Data da Aula:")
    regclasseid = models.ForeignKey(Turmas, on_delete=models.CASCADE, verbose_name="Classe:")
    regalunoid = models.ForeignKey(Alunos, on_delete=models.CASCADE, help_text="Aluno:", verbose_name="Aluno:")
    status = models.CharField(max_length=3, verbose_name="Status:")

    def __int__(self):
        return self.Alunos.nome

My view looks like this:

def my_view(request):
    items = Turmas.objects.all()
    related_records = []

    if 'item_id' in request.GET:
        item_id = request.GET['item_id']
        related_records = Registers.objects.filter(regclasseid=item_id)

    return render(request, 'schooladmin/registers_report.html', {'items': items, 'related_records': related_records})

And the template is renderer like this:

Screenshot 2024-03-04 at 16.59.19
Screenshot 2024-03-04 at 16.59.19952×760 51.8 KB

Here I can see the names of the users are duplicated, which is fine but I just want to show one record, a distinct record.

If I change the view to get only the .disitinct() .values() I can only see the id of the fields:

def my_view(request):
    items = Turmas.objects.all()
    related_records = []
    students = []

    if 'item_id' in request.GET:
        item_id = request.GET['item_id']
        related_records = Registers.objects.filter(regclasseid=item_id)**.values('regalunoid').distinct()**

    return render(request, 'schooladmin/registers_report.html', {'items': items, 'related_records': related_records})

Like this:

Screenshot 2024-03-04 at 17.05.05
Screenshot 2024-03-04 at 17.05.05976×476 19.4 KB

Why is this happening and how can I make the names to show instead of the id’s?

Thanks in advance,

2

The issue is not the distinct, it’s the use of the values clause to have the query only return values (not the objects).

In part, it depends upon the database you’re using. If you’re using PostgreSQL, you don’t need the values clause, you can use distinct on a field. (See the docs for distinct for a complete explanation.)

You could also annotate the name on the rows, order by the name, then use values and distinct.

3

Alright, I think I might have to change to PostgreSQL.

But I did not understand the last phrase. I understand it’s like a workaround but could you please explain me what you mean?

Thanks

4

Less a workaround and more of just an alternative approach.

Winging this, something like:

Registers.objects.filter(regclasseid=item_id). 
    annotate(re_name=F('regalunoid__name')).
    order_by('re_name').values('re_name').distinct()
5

Thank you KenWhitsell, it works just fine :+1: