Home > database >  Django listview filter related field lookup by date
Django listview filter related field lookup by date

Time:09-02

I want to fetch only the elements of my VM model related to my Hypervisor model based on the current date, but when it finds them I fetch all the elements related to the parent model

My Models

class Hypervisor(models.Model):
    name = models.CharField(max_length=200)

class VM(models.Model):
    hypervisor = models.ForeignKey(Hypervisor, on_delete=models.CASCADE)
    name = models.CharField(max_length=200)
    cpu = models.CharField(max_length=200)
    ram = models.CharField(max_length=200)
    disk = models.CharField(max_length=200)
    date = models.DateField(null=True)

My view

class vm(LoginRequiredMixin, ListView):
    model = Hypervisor
    template_name = 'vm_list_original.html'
    ordering = ['name']

    def get_queryset(self, *args, **kwargs):
        return Hypervisor.objects.filter(vm__date=datetime.today().strftime('%Y-%m-%d')).distinct()

DDBB

sqlite> select * from budget_vm;
280|TC-CLINDE1-HARD001|4|8192|80|2022-09-01|254
281|TC-CLINDE1-HARD001|4|8192|80|2022-09-02|251

My Template

<tbody>
  {% for hyper in object_list %}
      <tr>
      <td>{{ hyper.name }}</td>
      <td>
        {% for vm in hyper.vm_set.all %}
                    {{ vm.name }}
        {% endfor %}
      </td>
      </tr>
  {% endfor %}
</tbody>

The Result enter image description here

CodePudding user response:

You need to filter the related items as well with a Prefetch object [Django-doc], so:

from django.utils.timezone import now


class vm(LoginRequiredMixin, ListView):
    model = Hypervisor
    template_name = 'vm_list_original.html'
    ordering = ['name']

    def get_queryset(self, *args, **kwargs):
        today = now().date()
        return (
            super()
            .get_queryset(*args, **kwargs)
            .filter(vm__date=today)
            .prefetch_related(
                Prefetch('vm_set', queryset=VM.objects.filter(date=today))
            )
            .distinct()
        )
  • Related