Re: Escape percent sign in QuerySet.extra() method

[Chris Cogdon]

On Sunday, December 2, 2012 1:55:05 AM UTC-8, Martin Svoboda wrote:

Hello,
I'm want to use functionality of postgresql module pg_trgm in django. pg_trgm uses special operator % (percent sign). I don't know how should I escape it in query extra method.

# pure SQL
SELECT content, similarity(content, 'string') AS sml
  FROM table
  WHERE content % 'str'
  ORDER BY sml DESC, content;

# This throws exception IndexError 'tuple index out of range'
# I tried to escape percent sign with %%, or \%, but I always get same exception
objects = MyModel.objects.extra(
    select={'rank': 'similarity(content, %s)'},
    select_params=[content],
    where='content % %s',
    params=[content],
    order_by=['-rank']
)

# Raw query works ok
objects = MyModel.objects.raw('''SELECT *, similarity(content, %s) AS sml FROM table WHERE content %% %s ORDER BY sml DESC''', [content, content])

Can you help me how write percent sign in extra() method?

While this should not fix anything, can you try breaking it down like so.

objects = MyModel.objects.extra(
    where='content % %s',
    params=[content],
)

objects = objects.extra(

    select={'rank': 'similarity(content, %s)'},
    select_params=[content],
    order_by=['-rank']
)

That'll give us a hint at which of the two parts are giving us a problem.