How to filter hay results using db query

Question

I need to do a text search on my model and filter using db queries at the same time.

For example:

class MyModel(models.Model): text = models.TextField() users = models.ManyToMany(User) class MyModelIndexIndex(indexes.SearchIndex, indexes.Indexable): text = indexes.CharField(document=True, model_attr='text') def get_model(self): return MyModel 

So, I want to filter out all MyModel objects by the user and some text through a full-text search. Smth like this:

 qs = MyModel.objects.filter(users=request.user) sqs = MyModelIndex.objects.filter(text=request.GET['q']) intersection = some_magic_function(qs, sqs) 

or

 intersection = some_other_magic_function( qs_kwargs={'users': request.user}, sqs_kwargs={'text': request.GET['q']} ) 

Of course, the desired db queries can be much more complicated.

I see some possible solutions, all with serious flaws:

• Make an intersection in django: extract identifiers from qs and use them in sqs filter or vice versa. Problem: performance. We can get around this by using pagination and make intersections only for this page and its predecessors. In this case, we lose the total amount (

• Indicate all related m2m fields. Problem: performance, repetitive functions (I believe db will do such queries much better), db functions like annotations, etc.

• Do not use haystack (Go for mysql or posgresql inline full-text search.

I believe that I missed something obvious. The case seems to be quite common. Is there a traditional solution?

Answer 1

In general, it (possibly) cannot solve your problem using only one request. For example, if you use ElasticSearch as a search backend mechanism and MySQL for django models, MySQL and ElasticSearch will not be able to interact to create a single common query.

However, a workaround should occur if you use a common SQL database for your Django models and your Haystack hosting engine. You will need to create a custom hay mechanism that will analyze the request and filter the available models.

For example, to change the behavior of SimpleSearchBackend, all you have to do is fix the search method:

 class CustomSimpleSearchBackend(haystack.backends.SimpleSearchBackend): def search(self, query_string, **kwargs): ... if query_string: for model in models: ... if 'users' in kwargs: qs = qs.filter(users=kwargs['users']) ... class CustomSimpleEngine(haystack.backends.BaseEngine): backend = CustomSimpleSearchBackend query = haystack.backends.simple_backend.SimpleSearchQuery 

And in settings.py:

 HAYSTACK_CONNECTIONS = { 'default': { 'ENGINE': 'myapp.backends.CustomSimpleEngine', }, } 

Depending on the firewall used, the required patch will, of course, be different, but I suspect that it should not be too difficult to implement.