CODEWITHSUNDEEP
sqldjangopostgresql
Jaf
Jaf

Reputation: 309

Querying with objects.extra in django

How can i query with to_tsquery for partial words match

For example records

'hello old world'
'hello world'
'hi welcome'
'hi'

Here i wanted to return all records which includes words 'hello' or 'welcome'

SELECT * FROM accounts_order
WHERE name_tsvector @@ to_tsquery('english','hello | welcome');

This returns properly. Here i tried to implement using django 'objects.extra' query 

queryset = Order.objects.extra(where=['name_tsvector @@ to_tsquery(%s|%s)'], params=['hello','welcome'])

This query is nor working,got an exception

operator is not unique: unknown | unknown
LINE 1: ...nts_order" WHERE name_tsvector @@ to_tsquery(E'olmin'|E'20')
                                                            ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

How can i pass this params part as a list?

Upvotes: 1

Views: 1279

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324295

It appears that you want the | within the string, ie a boolean OR in the tsquery:

regress=> select to_tsquery('english', 'olmin|20');
   to_tsquery   
----------------
 'olmin' | '20'
(1 row)

Django is expanding %s to E'string', so you can't write %s|%s; as you've seen that expands to E'string1'|E'string2' which is interpreted as a boolean OR on the two strings. You must either:

  • Concatenate the two strings and | in Django with (eg) params=['hello'+'|'+'welcome'] and a single (%s) argument; or
  • Get Pg to concatenate the two strings with a literal |, eg (%s||'|'||%s)

I'd recommend the first option; it requires you to change the parameters you pass from Python but it produces vastly simpler SQL.

The original is invalid, it's trying to perform a boolean OR on two string literals:

regress=> select to_tsquery('english', 'olmin'|'20');
ERROR:  operator is not unique: unknown | unknown
LINE 1: select to_tsquery('english', 'olmin'|'20');
                                            ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

Upvotes: 1

Related Questions