How to better format Django filter query for 1000 entries using SQLite3 database? (Expression tree-CodePudding

The Django models I have are Component and Product. Components have Products as their Foreign Key. I am retrieving a search parameter from request.GET, called "components," that has a list of substrings that can belong to any Component. Based on the Components matched, I want to retrieve all Products that have this Component and return it back to the client. There are roughly 12000 Components and 3000 Products in my SQLite database

I have been filtering out Products with the "|" operator for each of the Component's Product's id number, which works fine when searching for specific Component substrings, such as "Lactose" and "Bacterium."

However, when I search for shorter substrings, such as "ac," I get the error: "OperationalError at /search, Expression tree is too large (maximum depth 1000)".

From what I can understand, it's because the database is executing a multitude of union queries, and more than 1000 of those queries causes that error.

I would like to know how to fix or workaround this error. Is there a better way to use Django's filter queries?

Here is my models.py:

from django.db import models

class Product(models.Model):
    id_number = models.IntegerField(primary_key=True)
    product_name = models.CharField(max_length=200)

class Component(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE)
    name = models.CharField(max_length=200)

Here is my excerpt from views.py:

query = Product.objects.all()
if 'components' in request.GET and len(request.GET['components']) > 0:
    component_queries = request.GET['components'].strip().split(",") # List of all components as strings
    components = Component.objects.all() # Roughly 12000 entries in total, roughly 1700 entries for a worst case after filtering
    for component_query in component_queries:
        components &= (Component.objects.filter(name__icontains=component_query)
    if len(components) == 0:
        query = Product.objects.none()
    specified_products = Product.objects.none()
    for component in components:
        specified_products |= Product.objects.filter(id_number__icontains=component.product.id_number)
    query &= specified_products

To verify the error was in the excerpt, I limited the amount of components in the for loop to less than 1000 and the search engine worked fine.

CodePudding user response：

You can filter with:

from django.db.models import Q

component_queries = request.GET['components'].strip().split(',')

Product.objects.filter(
    Q(*[
       Q(component__name__icontains=component_query)
       for component_query in component_queries
    ])
)

The above will however only retrieve Products that have a componant that matches for all component_querys.

If you want to find products that have at least one component for which the string matches, you work with:

from django.db.models import Q

component_queries = request.GET['components'].strip().split(',')

Product.objects.filter(
    Q(*[
       Q(component__name__icontains=component_query)
       for component_query in component_queries
       ],
       _connector=Q.OR
    )
).distinct()

The .distinct() [Django-doc] will prevent retrieving the Product that many times as there are matching components.