CODEWITHSUNDEEP
djangodjango-orm
culebrón
culebrón

Reputation: 36453

How can I use a raw query in Django in another queryset?

I have a pure-SQL queryset:

SELECT ft2.user_id, avg(...) figure, count(...) as figure_count
FROM figures_table ft,
     figures_table ft2
WHERE ft.user_id=%(current)s AND
      ft.user_id != ft2.user_id AND
      ft2.user_id IN (%(others)s)
GROUP BY ft2.user_id

I want to make another one:

SELECT %(id)s as id,
       avg(figure * figure_count) figure
FROM (%(subquery)s) subquery

Where %(subquery)s is the first query. When I try doing commands like

User.objects.raw(second_query, {'subquery': first_qs.query})

it raises exceptions: can't adapt type QuerySet. Passing a string also doesn't work, as it becomes quoted in the resulting query. Should I just format the string with the nested query?

Upvotes: 1

Views: 1270

Answers (1)

Ian Clelland
Ian Clelland

Reputation: 44122

Should I just format the string with the nested query?

Yes.

That's essentially what raw SQL is for.

It's complaining because you are trying to put SQL in a variable parameter slot, and the db adapter doesnt know how to do that. (in fact, in other circumstances, what you are doing would be a SQL-injection attack, and is exactly the sort of thing that parameterized queries are designed to prevent)

Just use regular string formatting operations to join your queries, and use that as the source for your next raw queryset.

(do remember to escape any "%" characters in your SQL, if you are using python string interpolation on them, though)

Upvotes: 3

Related Questions