how to use SQL wildcard % with Queryset extra>select?

I'm using the extra() modifier in a view.

(See http://docs.djangoproject.com/en/1.1/ref/models/querysets/#extra-select-none-where-none-params-none-tables-none-order-by-none-select-params-none )

Here's the the code:

def viewname(request)
开发者_JAVA技巧    ...
    exact_matchstrings=[]
    exact_matchstrings.append("(accountprofile.first_name LIKE '" + term + "')")
    exact_matchstrings.append("(accountprofile.first_name LIKE '" + term + '\%' +  "')")

    extraquerystring = " + ".join(exact_matchstrings)

    return_queryset = return_queryset.extra(
        select = { 'match_weight': extraquerystring },
        )

The two append statements above are almost completely alike except that the second adds a % SQL wildcard character. This is causing an error; the statement without the % causes no problems. What's going on with the %? I'm surprised that django thinks this character is not defined, since it's in SQL specification. For example, the following SQL statement executes just fine:

select (first_name like "Car") + (first_name like "Car%") from accountprofile;

But trying to run it via the extra() modifier in my view code and evaluating the resulting queryset gives me an error. I think "%" needs to be escaped, so I tried that already. Any ideas?

Just ran into this issue ourselves doing a extra query with LIKE. To escape a % you need to do %%

Percentage sign not working

It looks like you are missing some quotes from the 2nd string. And I'm not sure that you need to escape the percent (%) unless this is required by django.

_matchstrings.append("(accountprofile.first_name LIKE '" + term + "%" +  "')")