Fortunately; I found the missing part, in documentation it say (Changing
the queryset
<https://docs.djangoproject.com/en/3.0/topics/forms/modelforms/#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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/5116b61c-ff98-45a8-89a9-eae0ab44a1b7o%40googlegroups.com.
