ProgProg

I’m surprised this feature isn’t promoted more (maybe because it involves raw SQL?). You can hook a Django model to a database view. It can come in very useful when you’re trying to aggregate common information from various models.

Problem to solve

Say I have the following models: Album, Artist, and Dvd on a music site that accepts submissions. Before submissions are shown on the site they must be approved. So each of them has a boolean saying whether they are approved or not, something like:

approved = models.BooleanField(default=False)

Let’s complicate the situation a little to make for a better example. Say, the equivalent field for the Dvd model is called “not_unapproved¹”. Also, Albums and Dvds have a foreign key to record label², something like:

record_label = models.ForeignKey(EvilRIAAMember)

Our goal is to have a model called Unapproved which aggregates the Album, Artist, Dvd objects that have not been approved. It should provide access to the ‘approved’ and ‘record_label’ fields. Then I can placate my site editors who are clamoring for an easy way to find all unapproved content regardless of content type.

Step 1: View definition

UNAPPROVED_VIEW = """ 
CREATE OR REPLACE VIEW unapproved_view AS
  SELECT
    (SELECT nextval('unapproved_sequence')) as id,
    o.id AS object_id,
    ct.id AS content_type_id,
    o.approved AS approved,
    o.record_label_id AS record_label_id
  FROM
    album as o,
    (SELECT id FROM django_content_type where model='album') as ct
  WHERE
    NOT o.approved
UNION
  SELECT
    (SELECT nextval('unapproved_sequence')) as id,
    o.id AS object_id,
    ct.id AS content_type_id,
    o.not_unapproved AS approved,
    o.record_label_id AS record_label_id
  FROM
    dvd as o,
    (SELECT id FROM django_content_type where model='dvd') as ct
  WHERE
    NOT o.not_unapproved
UNION
  SELECT
    (SELECT nextval('unapproved_sequence')) as id,
    o.id AS object_id,
    ct.id AS content_type_id,
    o.approved AS approved,
    NULL AS record_label_id
  FROM
    artist as o,
    (SELECT id FROM django_content_type where model='artist') AS ct
  WHERE
    NOT o.approved
""" 

Put that definition in app/models/unapproved.py, which is where the model will be going as well.

Those familiar with db views should probably skip on ahead to the next section.

Ok, what this gives you is a view called ‘unapproved_view’ which has the following fields:

• id
• object_id
• content_type_id
• approved
• record_label_id

Since artists do not have a record label foreign key, we specifically NULL it out. Note that we are only selecting rows that are not approved. If you’re trying this out with your own models, remember that for a UNION to work you need to have matching types.

The “nextval(‘unapproved_sequence’)” part is just to give each row in the unapproved_view a unique ID.

You may be tempted to optimize by replacing (SELECT id FROM django_content_type where model='artist') AS ct with a hard-coded content_type_id value, but that is much less safe. I’d rather have the overhead of those extra queries and not have to worry about content type ids changing on me.

Step 2: Model File

In app/models/unapproved.py:

  class Unapproved(models.Model):
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    content_object = generic.GenericForeignKey()
    approved = models.BooleanField(default=false)
    record_label = models.ForeignKey(EvilRIAAMember, null=True)

    class Meta:
      db_table = "unapproved_view" 

This isn’t the whole file of course, fill in the rest: imports, app_label, etc.

Things to note:

• this model has “null=True” for the record_label definition to cater for Artists not having one.
• 0.96 and below should use models.GenericForeignKey instead of generic.GenericForeignKey

Anyway you now have a model file hooked to the view. You’ll have to create that view in your database first, of course (run the contents of the UNAPPROVED_VIEW variable from Step 1). Don’t forget about creating the sequence:

CREATE SEQUENCE unapproved_sequence START 1;

Step 3: Use the darn thing already

Now if you add the necessary “class Admin” boilerplate to the model, you’ll find that you can access this model in the Django admin. While you can’t view individual objects and they’re read-only, you do get a handy list view.

You can use this model in (Django) views and templates though. You can now do things like Unapproved.objects.filter(...). And with the GenericForeignKey you have easy access to the actual content object. Writing a view that displays all unapproved objects and lets user change the “approved” value to True is trivial once you have this set up, so I won’t get the details of that.

Hope the above explanation helped. This is a very useful technique since it allows you to practice DRY and handle common properties and behavior across your models in a single place.

---

¹ Turns out the coding for Dvd model was outsourced to someone whose native language favors double negatives.

² Yes, wise guys, in real life artists have record labels too, but since these can possibly change over time, for the purposes of this demo they don’t!