Re: Forcing Django to use INNER JOIN instead of LEFT OUTER JOIN

On Thu, Jul 24, 2014 at 1:17 PM, Frankline <fraogongi@gmail.com> wrote:
> I have implemented search in my Django application to allow searching by
> more than one field. This results in Django always using a LEFT OUTER JOIN,
> which in my case generates the wrong results. However, when I change the SQL
> generated from a LEFT OUTER JOIN to an INNER JOIN, it returns the correct
> result.
>
> I am thinking it has to do with the way Q object in my code below.
>
> 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
> )
>
> How can I ensure that an INNER JOIN is used instead of a LEFT OUTER JOIN in
> the case of my code above?
>
>
> http://stackoverflow.com/questions/24927062/forcing-django-to-use-inner-join-instead-of-left-outer-join
>

Can you reduce that code to a single line test case please.

Eg, "Foo.objects.filter(Q(...) | Q(...)) is making a LEFT OUTER JOIN, why"

I suspect that you are OR'ing lookups that span the same relationship.

Cheers

Tom

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAFHbX1LP87H11rX_%2Ba6ysvfCa36fCQkVsMgJOH9QggKp9CkXtg%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.