Django: how to map the results of a raw sql query to model instances in admin list view?

Question

Consider the 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 am overriding the queryset() method so as to control the behavior of the admin list view. I want to perform a raw sql query in queryset(), map the results back 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 throwing an exception):

Database error
Something's 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.

Mind you that running the raw query 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 bigger ambition, to be able to create a sort of a 'virtual model' that should not have a corresponding database table the purpose of which is to encapsulate sql projection queries in its admin class (so that the model can be displayed in it's admin list view).

I have tried another approach, not using ItemAdmin at all 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 am getting a template exception in admin list view:

'RawQuerySet' object has no attribute 'complex_filter'

What to do? Thanks...

try removing the quotations in you queries as the do not appear to be used in the [documentation](https://docs.djangoproject.com/en/dev/topics/db/sql/) — T I, Dec 31 '11 at 11:36

Rabih Kodeih · Accepted Answer · 2011-12-31T23:03:52.713

Solved.

The simplest approach was to create a database view in sqlite3 (the db backend I'm using) and then add a normal model with a name compatible with the view. The model must have a set of fields with similar names to those of fields returned by the view. No admins or managers are necessary for my purposes.

View creation db script:

CREATE VIEW <view_name> AS SELECT etc...

View dropping db script (run as a preliminary step in case you need to re-create the view):

DROP VIEW <view_name>

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

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

so as not to create the table when syncdb is run.

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

no such column: stock_itemdbview.id

Cheers!

To avoid the need to drop the view, you may also consider `CREATE TEMPORARY VIEW` — DavidM, Aug 22 '14 at 20:11

score 0 · Answer 2 · answered Dec 31 '11 at 12:35

Well looking at the src, QuerySet and RawQuerySet are similar but very different classes where a QuerySet trumps a RawQuerySet in terms of provided functionality.

If I understand this correctly the last error you mention is understandable as a RawQuerySet does not have the complex_filter method and I presume you are at somepoint calling model.objects.filter which I believe invokes the aforementioned.

It is also understandable how you can iterate over a RawQuerySet, because, for want of a better term, the 'core model' is still represented in a RawQuerySet as it would be in a QuerySet.

Would using extra() not suffice?

I'm still pondering over the first issue will come back if/when I find one.

Django: how to map the results of a raw sql query to model instances in admin list view?

2 Answers2