Trying to do a full text search query on a database field (non model field), the .annotate(val=RawSQL(...)).filter(val=VAL1) doesn't work.

[vladiibine]

Context: Postgresql 9.3

Within a migration, I use RunSQL to create a new field, of type TSVector, my_custom_field.

I have

from django.db.models.expressions import RawSQL


MyModelQuerySet(QuerySet):
    def my_custom_search(self, arg):
        return self.annotate(matched_full_text_search=RawSQL(
            "SELECT my_table.my_custom_field @@ to_tsquery(%s)",
            params=[arg]).filter(matched_full_text_search=True)

This results in an error. not all arguments converted during string formatting.

The problem doesn't "seem" to come from calling the .annotate method, because hardcoding the parameter returns in the same error.

I also tried to add another field to the model, of type boolean, and do something like this

MyModelQuerySet(QuerySet):
    def my_custom_search(self, arg):
        return self.annotate(matched_full_text_search=RawSQL(
            "SELECT my_table.my_custom_field @@ to_tsquery(%s)",
            params=[arg], output_field=models.NullBooleanField()).filter(new_output_field=True)

And this again resulted in the error not all arguments converted during string formatting.

I tried messing with the RawSQL expression a lot, but I just couldn't filter on the annotated field no matter what.

If it seems I'm providing too much info in the title, that's so the use case for .extra gets understood.

But even this simple example doesn't work.

class MyQuerySer(QuerySet):
    def my_custom_search(self, arg):
        return self.annotate(val=RawSQL('select 3', params=[]).filter(val=3)

If I try to call .first() on this query set, I get again the same error not all arguments converted during string formatting. Indeed there seem to be too many arguments in the params list if I look at the traceback provided by django.

Anyway, I'm not sure whether there's a better workaround other than using the .extras, but at this point it seems I have to do it like this

class MyQuerySer(QuerySet):
    def my_custom_search(self, arg):
        return self.extra(where=["mymodel.myfield @@ to_tsquery(%s)"],
        params=[arg]

[Simon Charette]

I managed to reproduce against master. I wonder if this related to #25367.

[Ryan P Kilby]

We have a very similar usecase, and are running into the same issue when annotating with a RawSQL query. Noticed something very strange about the generated SQL.

Using annotate + extra, you get the expected query

>>> print models.User.objects.only('id') \
>>>     .annotate(val=RawSQL('select 3', params=[])) \
>>>     .extra(where=['val=%s'], params=[3]) \
>>>     .query

SELECT "accounts_user"."id", (select 3) AS "val" FROM "accounts_user" WHERE (val=3)

If you try to use filter, and inspect with sql_with_params, you'll notice that val is substituted with the contents of the RawSQL query.

>>> q = models.User.objects.only('id') \
>>>     .annotate(val=RawSQL('select 3', params=[])) \
>>>     .filter(val=3).query
>>> print q.sql_with_params()[0]
SELECT "accounts_user"."id", (select 3) AS "val" FROM "accounts_user" WHERE (select 3) = %s

Also, this happens:

>>> print q.sql_with_params()[1]
(3, 3)

>>> print q.sql_with_params()[1]
(3, 3, 3, 3)

>>> print q.sql_with_params()[1]
(3, 3, 3, 3, 3, 3)

>>> print q.sql_with_params()[1]
(3, 3, 3, 3, 3, 3, 3, 3)

[Ryan P Kilby]

btw, it looks like the PR for #25506 more or less resolved this ticket. Our tests passed when using the stable/1.8.x branch.

[Tim Graham]

Closing per comment 3 and per the simple test from the description which passes:

from polls.models import Question
from django.db.models.expressions import RawSQL
Question.objects.annotate(val=RawSQL('select 3', params=[])).filter(val=3)

Please reopen with details if it remains an issue.