CODEWITHSUNDEEP
djangopostgresql
Erik
Erik

Reputation: 5701

Django: Iterate over QuerySet of a database view

I have a Postgres database view which combines some data from other tables, and I have a Django model to reflect this view (simplified for this question):

class MyModel(models.Model):
    othermodel = models.ForeignKey(MyOtherModel, related_name='cached_points')
    num_points = models.IntegerField(default=0)

    class Meta(object):
        managed = False

Note the managed = False statement to make sure Django doesn't try to create or alter the table.

All works fine with using this model, except for iterating over it. When I do, for example:

for item in MyModel.objects.all():
    ....

I get the following exception:

django.db.utils.ProgrammingError: column app_mymodel.id does not exist LINE 1: SELECT "app_mymodel"."id", "app_mymodel...

I understand the error, as there is indeed no column 'id' in this (virtual) table, but the iterator uses it to iterate over the results.

How can I iterate over this QuerySet without a primary key column?

Thanks, Erik

Upvotes: 0

Views: 3369

Answers (3)

Erik
Erik

Reputation: 5701

I managed to iterate over the results by creating a dictionary of the values instead of accessing model fields, by using:

items = MyModel.objects.all()
for item in items.values('field1', 'field2', ..):
    item['field1'] ...  # Access the data

I'm sure there are other solutions, but this was the easiest for my use case.

Thanks for pointing me in the right direction, @gbs & @Alasdair.

Upvotes: 2

gbs
gbs

Reputation: 1325

A workaround is to define an arbitrary unique id field in your view using for instance row_number(), e.g. by prepending the query of the view like this:

CREATE VIEW app_mymodel AS
SELECT row_number() OVER() as id, ...

I assume django will happily use that id to iterate your QuerySet.

BIG WARNING LABEL: This assumes that ordering is not important. Any attempt to do more complex handling of QuerySets (e.g. unions or joins on this id) will return bogus results as django won't be able to tell the rows apart. This is also true at the DB level anyway though, so if you need this functionality then you should enforce some kind of consistent ordering across queries by adding an ORDER BY directive inside the OVER clause.

Upvotes: 0

Alasdair
Alasdair

Reputation: 308779

Django requires each model to have one primary key.

If you don't specify primary_key, Django will add an id field. In your case, that field doesn't exist, because the model isn't managed, so you get the error.

If there is a primary key on the model, you can set primary_key=True for that field. If other_model is unique, you could use that.

class MyModel(models.Model):
    othermodel = models.ForeignKey(MyOtherModel, primary_key=True, related_name='cached_points')
    ...

I'm not aware of any other work around.

Upvotes: 2

Related Questions