Backwards incompatibility with fancy PostgreSQL queries and QS-RF

[Barry Pederson]

Using PostgreSQL 8.3 and fulltext indexing, before QS-RF I was able to
execute queries like:

-----
q = 'hello world'
queryset = Entry.objects.extra(
select={
'snippet': "ts_headline(body, query)",
'rank': "ts_rank_cd(body_tsv, query, 32)",
},
tables=["plainto_tsquery(%s) as query"],
where=["body_tsv @@ query"],
params=[q]
)
-----

After updating to include QS-RF, it seems that the 'tables' parameter
to the extra() method is copied without parameter substitution
straight into the SQL 'FROM' clause, and with the entire thing
quoted. So that before I was getting something like

SELECT FROM plainto_tsquery('hello world') as query,
blog_entry, ....

but now, it's ending up as something like:

SELECT FROM "plainto_tsquery(%s) as query", blog_entry, ....

any thoughts on the best way to do this now with QS-RF?

In the meantime as a workaround, I'm just repeating the call to the
PostgreSQL plainto_tsquery() function 3 times, as in:

-----
q = 'hello world'
queryset = Entry.objects.extra(
select={
'snippet': "ts_headline(body, plainto_tsquery(%s))",
'rank': "ts_rank_cd(body_tsv, plainto_tsquery(%s), 32)",
},
where=["body_tsv @@ query"],
params=[q],
select_params=[q, q]
)
-----

which I have to think isn't quite as efficient (if it is, that'd be
good to know).

Barry

[James Bennett]

On Wed, Apr 30, 2008 at 11:43 PM, Barry Pederson
<barry.p...@gmail.com> wrote:
> any thoughts on the best way to do this now with QS-RF?

Well, the official database API docs say [1]:

"In some rare cases, you might wish to pass parameters to the SQL
fragments in extra(select=...)`. For this purpose, use the
select_params parameter. Since select_params is a sequence and the
select attribute is a dictionary, some care is required so that the
parameters are matched up correctly with the extra select pieces. In
this situation, you should use a
django.utils.datastructures.SortedDict for the select value, not just
a normal Python dictionary."

And the branch notes for queryset-refactor say [2]:

"Still on extra(select=...)... if you want to substitute parameters
into these extra selection columns, use the select_params argument to
extra(). The params argument is only applied to the extra where
conditions."

And the backwards-incompatible changes page [3] points to the above
notes on the branch description page.

[1] http://www.djangoproject.com/documentation/db-api/#extra-select-none-where-none-params-none-tables-none-order-by-none-select-params-none
[2] http://code.djangoproject.com/wiki/QuerysetRefactorBranch#Other
[3] http://code.djangoproject.com/wiki/BackwardsIncompatibleChanges#Queryset-refactormerge

--
"Bureaucrat Conrad, you are technically correct -- the best kind of correct."

[Barry Pederson]

On Apr 30, 11:52 pm, "James Bennett" <ubernost...@gmail.com> wrote:
> On Wed, Apr 30, 2008 at 11:43 PM, Barry Pederson
>

> <barry.peder...@gmail.com> wrote:
> >  any thoughts on the best way to do this now with QS-RF?
>
> Well, the official database API docs say [1]:
>
> "In some rare cases, you might wish to pass parameters to the SQL
> fragments in extra(select=...)`.

Yes, I am doing that in my second example, and that works fine.

The point of my post is more that it doesn't seem you can pass
parameters to the "tables" argument to extra() anymore, where before
you could.

[offspinner]

I think I posted a similar issue almost simultaneously with Barry
here. This affects MySQL 5.0.41 too.
See my post:
http://groups.google.com/group/django-users/browse_frm/thread/831667d32f01f5d2