Django ORM排序以精确/突出匹配排在首位 [英] Django ORM orderby exact / prominent match to be on top
问题描述
我需要根据Django ORM中的匹配长度对结果进行排序。
I need to order the results based on the length of match in Django ORM.
我有一个郊区
在 name
字段中包含位置详细信息的表。
I have a Suburb
table with location details in name
field.
我需要按照精确匹配/最突出的匹配来搜索具有给定文本和顺序的表
I have a requirement to search the table with given text and order by exact match / most prominent match to be the top
例如:
1)如果搜索字符串为 America,则结果应为[America,South America,North America ..]
在这种情况下,我们找到了一个完全匹配项,它必须是第一个元素。
1) if search string is 'America' then the result should be [America, South America, North America ..] in this case we found a complete match, which has to be the first element.
2)如果搜索为 port
,则结果应为['Port Melbourne''Portsea', East Airport
]
2) if search is port
then the result should be ['Port Melbourne' 'Portsea', East Airport
]
我们发现端口在定界符之前是完全匹配的。
in this case we found port to be a complete match before the delimiter.
我知道我可以使用多个查询并将它们连接起来,例如一个用于完全匹配,另一个用于部分匹配,然后在部分匹配上将它们与排除一起使用,例如
I'm aware that i can use several queries and join them, like one for exact match and another for partial match and then join them with exclude on partial match Like
search_list= [x.name for x in Suburb.objects.filter(name=search)]
# Then
search_list += [x.name for x in Suburb.objects.filter(name__iregex=r"[[:<:]]{0}".format(search)).exclude(name__in=search_list)]
我可以这样继续下去。但是想知道我们是否有更好的方法。
I can go on like this. But wanted to know if we have any better way.
有什么线索吗?
预先感谢
推荐答案
基于功能
postgres的解决方案(应该在mysql中工作,但不能测试):
solution for postgres (and should work in mysql, but not testing):
from django.db.models import Func
class Position(Func):
function = 'POSITION'
arg_joiner = ' IN '
def __init__(self, expression, substring):
super(Position, self).__init__(substring, expression)
Suburb.objects.filter(
name__icontains=search).annotate(
pos=Position('name', search)).order_by('pos')
编辑:根据Tim Graham的修正,在 Django文档-避免SQL注入。
according to Tim Graham's fix, recommended in Django docs - Avoiding SQL injection.
这篇关于Django ORM排序以精确/突出匹配排在首位的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!