#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.
----------------------------------------------+----------------------------
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.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/053.ba43f2b24ae44ecbecaa2b1165960de3%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
