Opened 4 years ago

Closed 4 years ago

#25427 closed Bug (duplicate)

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.

Reported by: vladiibine Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: QuerySet.extra
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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]

Change History (4)

comment:1 Changed 4 years ago by Simon Charette

Triage Stage: UnreviewedAccepted
Version: 1.8master

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

comment:2 Changed 4 years ago by 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)

comment:3 Changed 4 years ago by 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.

comment:4 Changed 4 years ago by Tim Graham

Resolution: duplicate
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top