Re: Django modelformset is_valid method retrieves entire model from database

Fortunately; I found the missing part, in documentation it say (Changing the queryset):

By default, when you create a formset from a model, the formset will use a queryset that includes all objects in the model (e.g., Author.objects.all()). You can override this behavior by using the queryset argument:

I was not changing changing the queryset before validating the formset which caused Django to retrieve the entire underlying model and include all objects in the model. I should have used the same queryset used to generate the formset in creating the form that will be validated. The validation should go like this:

qs = prs.objects.filter(Q(cid = 1243) | Q(pid = 1243))
form=prelations(data=datana, queryset = qs)

On Wednesday, July 29, 2020 at 2:25:30 AM UTC+3, Abdulrahman Alahaideb wrote:

I have a modelformset when is_valid method is called the entire model objects of the underlying form is retrieved from the database which is not efficient if the database table contains hundred of thousands records, I believe Django was not designed with this deficiency and there must be something wrong with my code, When is_valid is called these queries are sent to the database, the first query has no WHERE clause which means it will retrieve the entire table!:

[{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'},  {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},    **{'sql': 'SELECT `npr`.`rid`, `npr`.`PID`, `npr`.`rel`, `npr`.`CID` FROM `npr` ORDER BY `npr`.`rid` ASC', 'time': '0.037'}**    {'sql': 'SELECT `person_details`.`ID`, `person_details`.`firstName` FROM `person_details` WHERE `person_details`.`ID` = 198 LIMIT 21', 'time': '0.001'},  {'sql': 'SELECT `person_details`.`ID`, `person_details`.`firstName` FROM `person_details` WHERE `person_details`.`ID` = 1243 LIMIT 21', 'time': '0.000'},  {'sql': 'SELECT `npr`.`rid`, `npr`.`PID`, `npr`.`rel`, `npr`.`CID` FROM `npr` WHERE `npr`.`rid` = 1377 LIMIT 21', 'time': '0.000'},  {'sql': 'SELECT (1) AS `a` FROM `person_details` WHERE `person_details`.`ID` = 198 LIMIT 1', 'time': '0.000'},  {'sql': 'SELECT (1) AS `a` FROM `person_details` WHERE `person_details`.`ID` = 1243 LIMIT 1', 'time': '0.000'},  {'sql': 'SELECT (1) AS `a` FROM `npr` WHERE (`npr`.`CID` = 1243 AND `npr`.`PID` = 198 AND NOT (`npr`.`rid` = 1377)) LIMIT 1', 'time': '0.000'},  {'sql': 'SELECT `person_details`.`ID`, `person_details`.`firstName` FROM `person_details` WHERE `person_details`.`ID` = 200 LIMIT 21', 'time': '0.000'},  {'sql': 'SELECT `person_details`.`ID`, `person_details`.`firstName` FROM `person_details` WHERE `person_details`.`ID` = 1243 LIMIT 21', 'time': '0.004'},  {'sql': 'SELECT `npr`.`rid`, `npr`.`PID`, `npr`.`rel`, `npr`.`CID` FROM `npr` WHERE `npr`.`rid` = 1378 LIMIT 21', 'time': '0.000'},  {'sql': 'SELECT (1) AS `a` FROM `person_details` WHERE `person_details`.`ID` = 200 LIMIT 1', 'time': '0.000'},  {'sql': 'SELECT (1) AS `a` FROM `person_details` WHERE `person_details`.`ID` = 1243 LIMIT 1', 'time': '0.000'},  {'sql': 'SELECT (1) AS `a` FROM `npr` WHERE (`npr`.`CID` = 1243 AND `npr`.`PID` = 200 AND NOT (`npr`.`rid` = 1378)) LIMIT 1', 'time': '0.000'}]

The models

   class PersonDetails(models.Model):          id = models.AutoField(db_column='ID', primary_key=True)          firstname = models.CharField(db_column='firstName', max_length=20)                class Meta:              managed = True              db_table = 'person_details'                  class Npr(models.Model):          rid = models.AutoField(db_column='rid', primary_key=True)          pid = models.ForeignKey(PersonDetails, on_delete=models.CASCADE, db_column='PID', related_name='pid')          rel = models.CharField(max_length=1)          cid = models.ForeignKey(PersonDetails, on_delete=models.CASCADE, db_column='CID', related_name='cid')                class Meta:              managed = True              db_table = 'npr'              unique_together = (('pid', 'cid'),)

and Form definition:

class prelation(ModelForm):      class Meta:          model = Npr          fields = ['pid', 'rel', 'cid']          widgets = {'pid':HiddenInput(), 'rel':HiddenInput(), 'cid':HiddenInput()}    prelations = modelformset_factory(Npr, form = prelation, can_delete=True, extra=1)

I am using the Django shell to test because I want to make the question concise and avoid the complexities of the full application and both gave the same results.

>>> from tstapp.forms import prelations  >>>   >>>   >>> datana={'form-TOTAL_FORMS':'2',  ... 'form-INITIAL_FORMS':'2',  ... 'form-MIN_NUM_FORMS':'0',  ... 'form-MAX_NUM_FORMS':'1000',  ... 'form-0-pid':'198',  ... 'form-0-rel':'F',  ... 'form-0-cid':'1243',  ... 'form-0-rid':'1377',  ... 'form-0-DELETE':'None',  ... 'form-1-pid':'200',  ... 'form-1-rel':'M',  ... 'form-1-cid':'1243',  ... 'form-1-rid':'1378',  ... 'form-1-DELETE':'None'}  >>>   >>> form=prelations(data=datana)  >>> connection.queries  []  >>> form.is_valid()  True  >>> connection.queries  [{'sql': 'SELECT @@SQL_AUTO_IS_NULL', 'time': '0.000'}, {'sql': 'SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED', 'time': '0.000'},

 {'sql': 'SELECT `npr`.`rid`, `npr`.`PID`, `npr`.`rel`, `npr`.`CID` FROM `npr` ORDER BY `npr`.`rid` ASC', 'time': '0.037'}, {'sql': '

How to optimize the queries sent to database when is_valid method is called against a modelformset?

I am using Django Version: 3.0.4, Python version: 3.7.3, Database: 10.3.22-MariaDB-0+deb10u1-log

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/5116b61c-ff98-45a8-89a9-eae0ab44a1b7o%40googlegroups.com.