ModelFormSet, is_valid is checking the whole underlying table

Using Django Forms & APIs
1

Hi everyone, I’m new to the forum, and to Django itself

I decided to use django to create a web interface to an ongoing project (usually I’m doing it with c#, but I wanted an excuse to try to learn Django)

Currently I’m still learning and trying to figure out a lot of things reading documentation and following tutorials on youtube (mostly)…

There is one thing that I can’t figure out. Simply put I have a page where I can set a filter on the records I want to retrieve (AnagraficheSearchPage.html) from the database and clicking the submit button I show the results of the filter (in AnagraficheEditPage.html). The problem is if I modify any of these results and click the save button (in the same AnagraficheEditPage.html), the exact moment I call the is_valid(), Django reads the whole table, and since I can have more than 100K rows it is too slow.

database: SQL server (I can’t change its structure, it’s already in production)

I thing I’m misreading some instructions, but I don’t seem to be able to find the problem.

Below the interesting part of the project (I have a main application called Console and a bunch of different applications, each one with its own function).
I tried to remove all the useless parts, because it’s a little too much to post everything here

Thanks for any help

models.py

from django.db import models
    from django.utils import translation
    
    # Create your models here.
    class Anagrafiche(models.Model):
        idbatch = models.IntegerField(db_column='IDBatch', primary_key=True)  # Field name made lowercase.
        mandante = models.CharField(db_column='Mandante', max_length=64)  # Field name made lowercase.
        societa = models.CharField(db_column='Societa', max_length=64)  # Field name made lowercase.
        codiceclifor = models.CharField(db_column='CodiceCliFor', max_length=64)  # Field name made lowercase.
        nome = models.CharField(db_column='Nome', max_length=256, blank=True, null=True)  # Field name made lowercase.
        cognome = models.CharField(db_column='Cognome', max_length=256, blank=True, null=True)  # Field name made lowercase.
        ragsoc = models.CharField(db_column='RagSoc', max_length=256, blank=True, null=True)  # Field name made lowercase.
#more fields here
        cfisc = models.CharField(db_column='CFisc', max_length=32, blank=True, null=True)  # Field name made lowercase.
        piva = models.CharField(db_column='PIVA', max_length=32, blank=True, null=True)  # Field name made lowercase.
        codicedestinatariosdi = models.CharField(db_column='CodiceDestinatarioSDI', max_length=64, blank=True, null=True)  # Field name made lowercase.
        iderrore = models.IntegerField(db_column='IDErrore', blank=True, null=True)  # Field name made lowercase.
    
        class Meta:
            managed = False
            #SQL Schemas :)
            db_table = 'enerp].[Anagrafiche'
            unique_together = (('idbatch', 'mandante', 'societa', 'codiceclifor'),)
            verbose_name = "Anagrafica"
            verbose_name_plural = "Anagrafiche"
    
        def __str__(self):
            if self.cognome != None:
                return self.codiceclifor + ' ' +  self.cognome + ' ' +  self.nome
            else:
                if self.ragsoc != None:
                    return self.codiceclifor + ' ' +  self.ragsoc
                else:
                    return self.codiceclifor

forms.py

from django import forms
from django.db.models import query
from django.utils.translation import gettext_lazy as _

from .models import Anagrafiche

class AnagraficheSearchForm(forms.Form):
    idbatch       = forms.IntegerField(required=True, label='ID Batch')
    iderrore      = forms.CharField(max_length=256, required=False, label='Errore')
    codiceclifor  = forms.CharField(max_length=64, required=False, label='Codice Cliente')
    denominazione = forms.CharField(max_length=512, required=False, label='Denominazione')
    cfisc         = forms.CharField(max_length=16, required=False, label='Codice Fiscale')
    piva          = forms.CharField(max_length=11, required=False, label='Partita Iva')


class AnagraficheEditForm(forms.ModelForm):
    class Meta:
        model: Anagrafiche
        fields = ['idbatch', 'mandante', 'societa', 'codiceclifor', 'nome', 'cognome', 'ragsoc', 'cfisc', 'piva', 'codicedestinatariosdi', 'iderrore']
        labels = {
            'idbatch':               _('ID Batch'),
            'mandante':              _('Mandante'),
            'societa':               _('Societa'),
            'codiceclifor':          _('Codice Cliente'),
            
            'nome':                  _('Nome'),
            'cognome':               _('Cognome'),
            'ragsoc':                _('Ragione Sociale'),
            'cfisc':                 _('Codice Fiscale'),
            'piva':                  _('Partita Iva'),
            'codicedestinatariosdi': _('Codice SDI'),
            'iderrore':              _('Errore'),
        }
        widgets = {
            'idbatch':               forms.HiddenInput(),
            'mandante':              forms.HiddenInput(),
            'societa':               forms.HiddenInput(),
            'codiceclifor':          forms.HiddenInput(),
            }

views.py

from django.db.models import Q
from django.db.models.query import QuerySet
from django.forms.models import modelformset_factory
from django.shortcuts import render



from .models import Anagrafiche
from .forms import AnagraficheSearchForm, AnagraficheEditForm



def ENERP(request):
    return render(request, 'index.html')



def AnagraficheSearchView(request):
    if request.method == 'GET':
        asf = AnagraficheSearchForm(data=request.GET)

        if asf.is_valid():
            if asf.cleaned_data['idbatch'] != None:
                qs = Anagrafiche.objects.filter(idbatch__exact=asf.cleaned_data['idbatch'])
            
            if asf.cleaned_data['denominazione'] != '':
                qs = qs.filter(Q(cognome__contains=asf.cleaned_data['denominazione']) | Q(nome__contains=asf.cleaned_data['denominazione']) | Q(ragsoc__contains=asf.cleaned_data['denominazione']))
                
            if  asf.cleaned_data['cfisc'] != '':
                qs = qs.filter(cfisc__exact=asf.cleaned_data['cfisc'])
                
            if  asf.cleaned_data['piva'] != '':
                qs = qs.filter(piva__exact=asf.cleaned_data['piva'])

            return render(request, 'AnagraficheSearchPage.html', {'AnagraficheSearchForm' : asf})
        else:
            return render(request, 'AnagraficheSearchPage.html', {'AnagraficheSearchForm' : asf})
    else:
        return render(request, 'AnagraficheSearchPage.html')



def AnagraficheEditView(request):
    formset = modelformset_factory(Anagrafiche, form=AnagraficheEditForm, extra=0)
    
    if request.method == 'GET':
        if request.GET.get('queryset') is None:
            qs = Anagrafiche.objects.all()

            if request.GET.get('idbatch') is not None:
                qs = qs.filter(Q(idbatch__exact=request.GET.get('idbatch')))

            if request.GET.get('denominazione') is not None:
                qs = qs.filter(Q(cognome__contains=request.GET.get('denominazione')) |
                               Q(nome__contains=request.GET.get('denominazione')) |
                               Q(ragsoc__contains=request.GET.get('denominazione')))

            if request.GET.get('cfisc') != '':
                qs = qs.filter(cfisc__exact=request.GET.get('cfisc'))
                   
            if request.GET.get('piva') != '':
                qs = qs.filter(piva__exact=request.GET.get('piva'))

        aefs = formset(request.POST or None, request.FILES or None, queryset=qs)        

        context = {'AnagraficheEditFormSet' : aefs}
        return render(request, 'AnagraficheEditPage.html', context)

    elif request.method == 'POST':
        aefs = formset(request.POST or None, request.FILES or None)

        if 'AnagraficheEditSaveButton' in request.POST:
            if aefs.is_valid(): # this become a select * from table with 16 million record
                aefs.save()

        context = {'AnagraficheEditFormSet' : aefs}
        return render(request, 'AnagraficheEditPage.html', context)

    else:
        asf = AnagraficheSearchForm()
        context = {'AnagraficheSearchForm' : asf}
        return render(request, 'AnagraficheSearchPage.html', context)

urls.py

from django.urls import path
from . import views

urlpatterns = [
    path('', views.ENERP),
    path('AnagraficheSearch', views.AnagraficheSearchView),
    path('AnagraficheEdit', views.AnagraficheEditView),
]

template AnagraficheEditPage.html

{% extends "layout.html" %}



{% block maincontent %}

<div class="container">
    <div class="container-lg">
        {% load crispy_forms_tags %}
        <form id="AnagraficheEditForm" action="AnagraficheEdit" method="POST">
            {% csrf_token %}

            {{ AnagraficheEditFormSet.management_form|crispy }}

            <div class="d-grid gap-5">
                {% for aef in AnagraficheEditFormSet %}
                {{ aef.idbatch }}
                {{ aef.mandante }}
                {{ aef.societa }}
                {{ aef.codiceclifor }}
                <div class="card">
                    <div class="card-body">
                        <h6 class="card-subtitle mb-2 text-muted">
                            <a href="#{{ aef.codiceclifor.value }}')">{{ aef.codiceclifor.value }}</a> -
                            {% if aef.cognome.value != None and aef.nome.value != None %}
                            {{ aef.cognome.value }} {{ aef.nome.value }}
                            {% elif aef.ragsoc.value != '' %}
                            {{ aef.ragsoc.value }}
                            {% endif %}
                        </h6>
                        <div class="row">
                            <div class="col-6">
                                {{ aef.cognome|as_crispy_field }}
                            </div>

                            <div class="col-6">
                                {{ aef.nome|as_crispy_field }}
                            </div>
                        </div>

                        <div class="row ma-1">
                            <div class="col-12">
                                {{ aef.ragsoc|as_crispy_field }}
                            </div>
                        </div>

                        <div class="row mt-1">
                            <div class="col-4">
                                {{ aef.cfisc|as_crispy_field }}
                            </div>

                            <div class="col-4">
                                {{ aef.piva|as_crispy_field }}
                            </div>

                            <div class="col-4">
                                {{ aef.codicedestinatariosdi|as_crispy_field }}
                            </div>
                        </div>

                        <div class="row mt-1">
                            <div class="col-4">
                                {{ aef.iderrore|as_crispy_field }}
                            </div>

                            <div class="col-8">
                                {{ aef.iderrore|as_crispy_field }}
                            </div>
                        </div>
                    </div>
                </div>

                {% if forloop.last == True %}
                <div class="row justify-content-md-center mb-5">
                    <div class="col-md-auto">
                        <button id="AnagraficheEditSaveButton" name="AnagraficheEditSaveButton" type="submit" class="btn btn-primary">Salva</button>
                    </div>
                </div>
                {% endif %}
                
                {% empty %}
                <div class="card">
                    <div class="card-body">
                        <h6 class="card-subtitle mb-2 text-muted">Nessun anagrafica corrispondente ai criteri di ricerca</h6>
                    </div>
                </div>
                {% endfor %}
            </div>

        </form>
    </div>


</div>

{% endblock %}
2

That is because you have:

which has no filters on the queryset being used to generate that formset. You need to supply the queryset parameter in your formset definition to limit the number of rows being retrieved.

See Changing the queryset.

(I see you have it in your GET portion of the view when you’re actually creating the formset instance, but not in the POST section of that view.)

3

Hi Ken

thanks for the point, I tried to follow the instructions, but I think I’m still missing something

I created a new ModelFormSet class like this one

class AnagraficheEditFormSet(BaseModelFormSet):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        if 'queryset' in kwargs:
            self.queryset = kwargs['queryset']

and update the views.py

def AnagraficheEditView(request):
    if request.method == 'GET':
        if request.GET.get('queryset') is None:
            qs = Anagrafiche.objects.all()

            if request.GET.get('idbatch') is not None:
                qs = qs.filter(Q(idbatch__exact=request.GET.get('idbatch')))
                

            if request.GET.get('denominazione') is not None:
                qs = qs.filter(Q(cognome__contains=request.GET.get('denominazione')) |
                               Q(nome__contains=request.GET.get('denominazione')) |
                               Q(ragsoc__contains=request.GET.get('denominazione')))

            if request.GET.get('cfisc') != '':
                qs = qs.filter(cfisc__exact=request.GET.get('cfisc'))
                   
            if request.GET.get('piva') != '':
                qs = qs.filter(piva__exact=request.GET.get('piva'))

formset=AnagraficheEditFormSet, extra=0)
        formset = modelformset_factory(Anagrafiche, form=AnagraficheEditForm, formset=AnagraficheEditFormSet, extra=0)

        aefs = formset(request.POST or None, queryset=qs)

        context = {'AnagraficheEditFormSet' : aefs
                  ,'queryset': qs }

        return render(request, 'ENERP/AnagraficheEdit.html', context)

    elif request.method == 'POST':
        formset = modelformset_factory(Anagrafiche, form=AnagraficheEditForm, formset=AnagraficheEditFormSet, extra=0)

        # I'm pretty sure the problem is here, but I don't know what to specify as a second parameter.
        aefs = formset(request.POST or None)


        if 'AnagraficheEditSaveButton' in request.POST:
            if aefs.is_valid():
                aefs.save()

        context = {'AnagraficheEditFormSet' : aefs
                  ,'queryset': qs }
        return render(request, 'ENERP/AnagraficheEdit.html', context)

    else:
        asf = AnagraficheSearchForm()
        context = {'AnagraficheSearchForm' : asf}
        return render(request, 'ENERP/AnagraficheSearch.html', context)

but again, in the POST section I don’t know how to set the queryset again.
When I’m in the GET section I still have access to the filters set in the previous form, but once the edit page is displayed I cannot access the request.GET and in request.POST there is no queryset. Also in the AnagraficheEditFormSet there is no queryset

I don’t know if it is worth adding these informations, but since I need to connect to an SQL server, I had to use an older version of django (python 3.9, django 3.0.14 since it’s the only combination that works with django-mssql). Reading the official documentation abount modelformset_factory and changing the query, I haven’t noticed anything about “bugs” on the version I’m using

4

It’s not a bug.

There isn’t one automatically in your GET, either - you create it in the view. You need to do (effectively) the same in your POST.

You need to somehow pass the query data used for the GET back out to the form to be resubmitted by the POST. One common way to do that is by including those parameters as hidden fields on the form. (The problem with that is that they can be altered by a malicious user.)
Or, you could save those parameters in the user’s session or some other cache or persistence mechanism.

Either way, it isn’t something that Django is going to handle for you automatically.

5

Hi Ken
Thanks again

That’s exactly what I was misinterpreting.
I tried your suggestion, to “save” the filtering field in the session
method = GET → save the filtering option in session
method = GET → filter the interesting record (like before)
method = POST → filter the interesting record (with saved filter in session) and save to database

now the is_valid method is correctly checking the only record I want to update, but when I try to save back to database, I discovered that django doesn’t support primary key with multiple field, so it tries to update all the records again (looking at the query sent to SQL it is something like update … where idbatch = 1 :frowning: