error "ORDER BY not allowed in subqueries of compound statements". In django while using Union function

Question

"ORDER BY not allowed in subqueries of compound statements." in Django while using Icontains to join two querysets, problem comes when i join a third query set Such as slug with some special characters

My Views;

    if len(query)>78:
        myposts = Blogpost.objects.none()
    else:
        post_title = Blogpost.objects.filter(title__icontains=query)
        posts_content = Blogpost.objects.filter(content__icontains=query)
        posts_overview= Blogpost.objects.filter(overview__icontains=query)
        myposts = post_title.union(posts_content, posts_overview)


    if myposts.count() == 0:
        messages.warning(request, "No search results found. Please enter again.")
    context = {'myposts': myposts,'query':query}
    return render(request, 'blog/search.html', context)```

Answer 1

I think it's a problem related to SQLite which doesn't support some Django operations.
I tested a similar code with an SQLite and a Postgres database and the 1st would fail. So my answer only makes sense if you're using SQLite.
In the documentation, you can read this (without more details. You need to check the SQLite documentation to confirm if that's indeed the case)

In addition, only LIMIT, OFFSET, COUNT(*), ORDER BY, and specifying columns (i.e. slicing, count(), order_by(), and values()/values_list()) are allowed on the resulting QuerySet. Further, databases place restrictions on what operations are allowed in the combined queries. For example, most databases don’t allow LIMIT or OFFSET in the combined queries. https://devdocs.io/django~3.1/ref/models/querysets#django.db.models.query.QuerySet.difference

It seems the issue arise when using the union(), intersection(), and difference() operations.

Two possible solutions:

Q models

myposts = Blogpost.objects.filter(
    Q(title__icontains=query) | Q(content__icontains=query) | Q(content__icontains=query)
)

values_list (not so pretty)

    post_title_ids = list(Blogpost.objects.filter(title__icontains=query).values_list('id', flat=True))
    posts_content_ids = list(Blogpost.objects.filter(content__icontains=query).values_list('id', flat=True))
    posts_overview_ids = list(Blogpost.objects.filter(overview__icontains=query).values_list('id', flat=True))
    ids_list = post_title_ids + posts_content_ids + posts_overview_ids

    myposts = Blogpost.objects.filter(id__in=ids_list)

Answer 2

Similar to the Q models solution provided is to use the | operator:

myposts = (post_title | posts_content | posts_overview).distinct()

The advantage this way is it allows you to union arbitrary querysets rather than redeclaring the filter criteria.