Querying using Q objects

Question

Hi I'm a newbie to Django. I'm trying to implement a search feature like this.

query_results = Shops.objects.filter\
                            (Q(shop_name__icontains=search_text)\
                            |Q(state__state_name__icontains=search_text)\
                            |Q(city__city_name__icontains=search_text)).distinct()

I would like to search Shops based on the shop_name, state_name and city_name. State and city fields are foreign keys. For some 'Shops' stateandcity are null. However, shop_name contains the search_text. So I'm not getting those 'Shops' by running this query. Any help on this is appreciated.

Answer 1

I suspect that when you use the state__state_name and city__city_name relations Django makes INNER JOIN to these tables. Inner join removes the shop without existing relation from the result.

As a workaround for this issue you can try something like this:

states = State.objects.filter(state_name__icontains=search_text)
cities = City.objects.filter(city_name__icontains=search_text)
query_results = Shops.objects.filter(Q(shop_name__icontains=search_text)
                                    |Q(state__in=states)
                                    |Q(city__in=cities)).distinct()