我已经在我的Django应用程序中实现了搜索,以允许按多个字段搜索。这导致Django始终使用左外连接(LEFT OUTER JOIN),在我的情况下生成错误的结果。但是,当我将从LEFT OUTER JOIN生成的SQL更改为INNER JOIN时,它返回正确的结果。
我认为这与以下代码中的Q对象有关。
如何确保在上述代码中使用 INNER JOIN 而不是 LEFT OUTER JOIN?
我认为这与以下代码中的Q对象有关。
from django.db import models, transaction
...
def construct_search(field_name):
if field_name.startswith('^'):
return "%s__istartswith" % field_name[1:]
elif field_name.startswith('='):
return "%s__iexact" % field_name[1:]
elif field_name.startswith('@'):
return "%s__search" % field_name[1:]
else:
return "%s__icontains" % field_name
class CoreSearchMixin(object):
"""Subclasses must define search_fields = [field_1, ...field_n]
where the field is a string, the name of a field, and can contain the following prefix characters:
'^': the search field must start with the search term, case insensitive
'=': the search field must exactly equal the search term, case insensitive
'@': full-text search
If no prefix is given, any string that contains the search field will match.
"""
search_fields = None
search_form_class = SearchForm
@cachedproperty
def search_form(self):
return self.search_form_class(getattr(self.request, self.request.method))
def get_query_help_message(self):
"""Returns a comma separated list of fields that are used in the search, to help the user
create a search.
"""
fields = []
if self.search_fields:
for search_field in self.search_fields:
field = get_field_from_path(self.model, search_field)
fields.append(field.verbose_name.title())
return ",".join(fields)
def get_filtered_queryset(self, queryset):
if self.search_form.is_valid():
self.query = self.search_form.cleaned_data['q']
else:
self.query = None
if self.search_fields and self.query:
orm_lookups = (construct_search(str(search_field).replace('.', '__'))
for search_field in self.search_fields)
chained_or_queries = None
for bit in self.query.split():
or_queries = (models.Q(**{orm_lookup: bit})
for orm_lookup in orm_lookups)
if chained_or_queries:
chained_or_queries = itertools.chain(chained_or_queries, or_queries)
else:
chained_or_queries = or_queries
return queryset.filter(reduce(operator.or_, chained_or_queries))
else:
return queryset
def get_context_data(self, **kwargs):
return super(CoreSearchMixin, self).get_context_data(
search_form=self.search_form,
query_help_message=self.get_query_help_message(),
search_fields=self.search_fields,
**kwargs
)
如何确保在上述代码中使用 INNER JOIN 而不是 LEFT OUTER JOIN?