Raw query with rank over subquery / params not quoted

Question

My Goal

I need PostgreSQL's rank() window function applied to an annotated queryset from Django's ORM. Django's sql query has to be a subquery in order to apply the window function and this is what I'm doing so far:

queryset = Item.objects.annotate(…)

queryset_with_rank = Items.objects.raw("""
   select rank() over (order by points), *
     from (%(subquery)s)""", { 'subquery': queryset.query }
)

The problem

Unfortunately, the query returned by queryset.query does not quote the parameters used for annotation correctly although the query itself is executed perfectly fine.

Example of returned query

The query returned by queryset_with_rank.query or queryset.query returns the following

"participation"."category" = )
"participation"."category" = amateur)

which I rather expected to be

"participation"."category" = '')
"participation"."category" = 'amateur')

Question

I noticed that the Django documentation states the following about Query.__str__()

Parameter values won't necessarily be quoted correctly, since that is done by the database interface at execution time.

As long as I fix the quotation manually and pass it to Postgres myself, everything works as expected. Is there a way to receive the needed subquery with correct quotation? Or is there an alternative and better approach to applying a window function to a Django ORM queryset altoghether?

Answer 1

As Django core developer Aymeric Augustin said, there's no way to get the exact query that is executed by the database backend beforehand.

I still managed to build the query the way I hoped to, although a bit cumbersome:

# Obtain query and parameters separately
query, params = item_queryset.query.sql_with_params()

# Put additional quotes around string. I guess this is what 
# the database adapter does as well.
params = [
    '\'{}\''.format(p) 
    if isinstance(p, basestring) else p
    for p in params
]

# Cast list of parameters to tuple because I got 
# "not enough format characters" otherwise. Dunno why.
params = tuple(params)

participations = Item.objects.raw("""
    select *, 
           rank() over (order by points DESC) as rank
      from ({subquery}
    """.format(subquery=query.format(params)), []
)