Re: 'too many SQL variables' error with ModelMultipleChoiceField

Hi Lukas,

I haven't looked into the problem in detail, but it doesn't surprise me that it exists.

It's also probable that it's something that is new to 1.4 (or, at least, that it manifests in slightly different ways in 1.4). One of the features added in 1.4 is bulk insertion of data. This means that you can use a single SQL statement to insert all your m2m relation; however, the downside is that on SQLite, there is a limit of 500 insertions that can be done at any one time (2 values per insertion).

This is logged as ticket #17788. There have been some discussions about a fix for this problem that will break the bulk insertion into batches. I suspect that when this problem is fixed, your problem will go away.

That said, I would also suggest you have a think about how you represent this widget in your UI. The approach you're describing is going to be very slow for the end user -- every time they load the page, they're going to have to download the HTML for a widget 1000 choices; when they send their selections, they're going to have to POST up to 1000 choices back to the server. This all takes time, and won't result in a particularly responsive web page.

If you've got a situation where there are 1000 options, but usually only a small number -- say, O(10) -- are selected, then perhaps an AJAX autocomplete widget of some kind would be a better approach. If the user is going to select a lot more options, then you might need to use a more exotic widget that avoids the need to transfer full lists back and forth.

Yours,
Russ Magee %-)

On Monday, 23 April 2012 at 5:38 AM, Lukas Zilka wrote:

> Hello,
>
> I have a form with ModelMultipleChoiceField on it, and I am getting
> 'DatabaseError: too many SQL variables' (using SQLite) when the user
> picks more than 1000 entries in the selection widget and posts the
> form.
>
> The problem seems to be the method clean of ModelMultipleChoiceField,
> which tries to select objects from the database simply by the IN SQL
> clause (e.g. SELECT * FROM projects WHERE id IN (1,2,3)). When the
> number of numbers in the IN argument rises over 1000 the too many SQL
> variables happens.
>
> I am using the most recent version of Django (1.5.dev17922), though I
> think it is irrelevant because similar issues happened even with older
> versions.
>
> I have always worked around this problem by custom temporary models
> that I used for joins of more complex queries. But more and more it
> seems to me that, either there already is a systematic solution to
> this problem that I am missing (which I hope somebody could point out
> to me), or there at least needs to be a need for it -- how do you
> address similar situations? -- in which case I can start thinking how
> to propose something general that could later be incorporated into
> Django.
>
> Thanks for any suggestions or help.
>
> --
> You received this message because you are subscribed to the Google Groups "Django users" group.
> To post to this group, send email to django-users@googlegroups.com (mailto:django-users@googlegroups.com).
> To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com (mailto:django-users+unsubscribe@googlegroups.com).
> For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.