[Django] #25427: 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.
Django
field), the .annotate(val=RawSQL(...)).filter(val=VAL1) doesn't work.
----------------------------------------------+----------------------------
Reporter: vladiibine | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.8
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
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]
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25427>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
field), the .annotate(val=RawSQL(...)).filter(val=VAL1) doesn't work.
Reporter: vladiibine | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by charettes):
* needs_docs: => 0
* needs_better_patch: => 0
* version: 1.8 => master
* needs_tests: => 0
* stage: Unreviewed => Accepted
Comment:
I managed to reproduce against master. I wonder if this related to #25367.
--
Ticket URL: <https://code.djangoproject.com/ticket/25427#comment:1>
Django
field), the .annotate(val=RawSQL(...)).filter(val=VAL1) doesn't work.
Reporter: vladiibine | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by rpkilby):
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)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25427#comment:2>
Django
field), the .annotate(val=RawSQL(...)).filter(val=VAL1) doesn't work.
Reporter: vladiibine | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by rpkilby):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25427#comment:3>
Django
field), the .annotate(val=RawSQL(...)).filter(val=VAL1) doesn't work.
Reporter: vladiibine | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* status: new => closed
* resolution: => duplicate
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25427#comment:4>