错误“复合语句的子查询中不允许 ORDER BY".在 Django 中使用 Union 函数 [英] error "ORDER BY not allowed in subqueries of compound statements". In django while using Union function

问题描述

复合语句的子查询中不允许使用 ORDER BY.";在 Django 中使用 Icontains 加入两个查询集时，当我加入第三个查询集时出现问题，例如带有一些特殊字符的 slug

"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

我的观点；

    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)```

推荐答案

我认为是 SQLite 不支持某些 Django 操作的问题.
我用 SQLite 和 Postgres 数据库测试了类似的代码，第一个会失败.所以我的回答只有在你使用 SQLite 时才有意义.
在文档中，您可以阅读此内容(没有更多详细信息.您需要查看 SQLite 文档以确认是否确实如此)

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)

此外，结果 QuerySet 只允许 LIMIT、OFFSET、COUNT(*)、ORDER BY 和指定列(即切片、count()、order_by() 和 values()/values_list()).此外，数据库对组合查询中允许的操作进行了限制.例如，大多数数据库不允许在组合查询中使用 LIMIT 或 OFFSET.https://devdocs.io/django~3.1/ref/models/querysets#django.db.models.query.QuerySet.difference

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

似乎在使用 union()、intersection() 和 difference() 操作时会出现问题.

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

两种可能的解决方案:

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

values_list(不太漂亮)

    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)

这篇关于错误“复合语句的子查询中不允许 ORDER BY".在 Django 中使用 Union 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！