0

everybody.

I work with Django 1.3 and Postgres 9.0. I have very complex sql query which extends simple model table lookup with some extra fields. And it wrapped in table function since it is parameterized.
A month before I managed to make it work with the help of raw query but RawQuerySet lacks a lot of features which I really need (filters, count() and clone() methods, chainability).
The idea looks simple. QuerySet lets me to perform this query:

SELECT "table"."field1", ... ,"table"."fieldN" FROM "table"

whereas I need to do this:

SELECT "table"."field1", ... ,"table"."fieldN" FROM proxy(param1, param2)

So the question is: How can I do this? I've already started to create custom manager but can't substitute model.db_table with custom string (because it's being quoted and database stops to recognize the function call).
If there's no way to substitute table with function I would like to know if I can create QuerySet from RawQuerySet (Not the cleanest solution but simple RawQuerySet brings so much pain in... one body part).

gorodechnyj
  • 691
  • 8
  • 25

1 Answers1

1

If your requirement is that you want to use Raw SQL for efficiency purposes and still have access to model methods, what you really need is a library to map what fields of the SQL are the columns of what models.

And that library is, Unjoinify

lprsd
  • 84,407
  • 47
  • 135
  • 168
  • Yeah, OK. This looks simple enough but I have no problem mapping sql output to model instances. What I need is to have QuerySet functionality in RawQuerySet. And I found something I can use: [How to quack like a queryset](http://ramenlabs.com/2010/12/08/how-to-quack-like-a-queryset/). Any opinions about this technique? – gorodechnyj Jul 06 '11 at 11:11
  • Interesting. But if you are writing a Custom SQL anyway, why would you use a filter and exclude on that? Whats the use case? – lprsd Jul 06 '11 at 11:22
  • Let's say I have some goods and these goods are being sold in many shops. In my function I calculate best offer, offer with minimal price and so on... All of this data I can get in single sql which is faster than making 10 extra database calls per product after I get product list. And still this is just a product list, so I can make .filter(manufacturer_id__in=[13,3564]) and apply some filters. – gorodechnyj Jul 06 '11 at 11:49