Django: how to display the results of a raw SQL query for modeling instances in the list of administrators?

Question

Consider a simple model:

class Item(models.Model): name = models.CharField(...) unit_cost = models.DecimalField(...) unit_price = models.DecimalField(...) 

It has the following admin class:

 class ItemAdmin(admin.ModelAdmin): def queryset(self, request): qs = self.model._default_manager.get_query_set() qs2 = self.model._default_manager.raw(''' SELECT "stock_item"."id", "stock_item"."name", "stock_item"."unit_cost", "stock_item"."unit_price" FROM "stock_item" ''') qs3 = RawQuerySet(''' SELECT "stock_item"."id", "stock_item"."name", "stock_item"."unit_cost", "stock_item"."unit_price" FROM "stock_item" ''', self.model) return qs # WORKS return qs2 # DOESN'T WORK return qs3 # DOESN'T WORK 

I override the queryset () method to control the behavior of the admin list view. I want to execute a raw sql query in queryset (), map the results to the Item model before sending them to the list view. The problem is that returning qs2 or qs3 generates the following error in the template (without exception):

 Database error Something wrong with your database installation. Make sure the appropriate database tables have been created, and make sure the database is readable by the appropriate user. 

Keep in mind that a running raw request in a separate script works, for example:

 items = Item._default_manager.raw(''' SELECT "stock_item"."id", "stock_item"."name", "stock_item"."unit_cost", "stock_item"."unit_price" FROM "stock_item" ''') for item in items: print item.name, item.unit_price # WORKS! 

In fact, I have a big ambition to be able to create a kind of “virtual model” that does not have to have an appropriate database table whose purpose is to encapsulate sql projection queries in my admin class (so that the model can be displayed in it as list of administrators).

I tried a different approach, not using ItemAdmin, but instead using:

 class ItemManager(models.Manager): def get_query_set(self): return Item._default_manager.raw(''' SELECT "stock_item"."id", "stock_item"."name", "stock_item"."unit_cost", "stock_item"."unit_price" FROM "stock_item" ''') 

with:

 class Item(models.Model): objects = ItemManager() etc... 

But now I get a template exception in the admin list view:

"RawQuerySet" does not have the "complex_filter" attribute

What to do? Thanks...

Answer 1

solvable.

The easiest way is to create a database view in sqlite3 (the db backend that I use) and then add a normal model with a name compatible with the view. The model should have a set of fields with similar names for the fields returned by the view. No administrators or managers are needed for my purposes.

View db script creation:

 CREATE VIEW <view_name> AS SELECT etc... 

Viewing a dropping db script (run as a preliminary step if you need to recreate the view):

 DROP VIEW <view_name> 

The only thing you need to be careful when creating the view is to make sure that you set:

 class Meta: managed = False #ordering = ( '-date', ) 

so as not to create a table when syncdb starts.

One more thing, you need to select a unique field in your view as the primary key (and reflect this in the model), otherwise django will complain about something like:

 no such column: stock_itemdbview.id 

Hooray!

Answer 2

Looking good at src , QuerySet and RawQuerySet are similar, but very different classes where QuerySet is superior to RawQuerySet in terms of functionality provided.

If I understand correctly, the last error you are talking about is understandable, since RawQuerySet does not have a complex_filter method, and I believe that you are at some point that calls model.objects.filter , which, it seems to me, causes the above .

It’s also clear how you can iterate over a RawQuerySet because, due to the lack of a better term, the “base model” is still represented in a RawQuerySet, as it would in a QuerySet.

Wouldn't use extra () ?

I am still pondering the first question that will come back if / when I find it.