How to create "swapped" version of an existing lookup?

Using Django
1

For a bit of context, let’s say I have this model:

class SavedSearch(models.Model):
    keywords = models.CharField(max_length=200)

And I also have a piece of text that’s coming from somewhere else:

text = "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua."

I want to find all SavedSearch objects whose keywords would match my text, something like this:

SavedSearch.objects.filter(keywords__reversesearch=text)

In my mental model, this should be fairly easy by simply subclassing the SearchLookup from contrib.postgres and swapping its lhs and rhs somehow.

Has anyone ever done something similar? Are there potential problems with this approach that I’m not foreseeing? Or maybe a better approach than this altogether?

Thanks :sparkles:

2

Can you not use .annotate() to avoid creating a custom lookup?

SavedSearch.objects.annotate(
    text=Value(text, output_field=TextField())
).filter(text__search=F('keywords'))
3

Interesting approach, I hadn’t thought of it. Unfortunately, it doesn’t seem to work and I don’t really understand the error message well enough to figure out why:

Traceback (most recent call last):
  File "<venv>/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedFunction: operator does not exist: tsvector @@ character varying
LINE 1: ...cididunt ut labore et dolore magna aliqua.', '')) @@ ("tests...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
4

I think you want might actually want a SearchVector in the annotate, around the Value

https://docs.djangoproject.com/en/3.0/ref/contrib/postgres/search/#searchvector

5

I tried this:

SavedSearch.objects.annotate(search=SearchVector(Value(text, output_field=TextField()))).filter(search=F('keywords'))

But I’m getting the same error as before (operator does not exist: tsvector @@ character varying)

6

I think I’m finally starting to understand what all the various parts (tsvector, tsquery, …) do and I think I’ve got something that seems to work (at least it’s returning the results I’m expecting):

SavedSearch.objects.annotate(search=SearchVector(Value(text, output_field=TextField()))).filter(search=Cast('keywords', output_field=SearchQueryField()))

The trick was to cast the keywords to a datatype that postgres expects (tsquery) using SearchQueryField. It’s not exactly pretty but I should be able to work with this, and maybe use that as a starting point to package it up in a custom lookup or maybe a database function.

Thanks for the help!

7

Aha!

I’m sure it would be useful in django.contrib.postgres as reversesearch or whatever.

8

I ended up deeper into the rabbit hole and found two separate issues in Django that made implementing such a lookup harder than I thought: https://github.com/django/django/pull/12525