Django ORM helps in overcoming most of the typical SQL requirements, selecting, inserting, updating and deleting. All works perfectly as long as you are performing within the ORM guidelines. Sooner or later, your application logic tends to grow which will push you to write nested and complicated queries.
Django ORM provides some decent API to help you cope. Yet, in some of my projects, I was faced with the challenge of hanlding similar models which in design should be seperated. Yet, they need to be queried and viewed in one context.
For this example, I will use a project that I’ve worked on recently which is a portal to host promotions and bargains, they share some attribute but they are very different in logic and operation. With that, you need to build a listing page to see a combination of both, some programmers try to combine the query using dict tools but for me, the best approach is to use view based models.
The idea is to convert the below model into a view to be queried and managed for select from one interface
1class Bargain(models.Model):2 DRAFT = 03 PUBLISHED = 14 DEFAULT_STATUS = PUBLISHED5 STATUSES = (6 (PUBLISHED, _('Published')),7 (DRAFT, _('Draft')),8 )910 headline = models.CharField(_('Headlines'), max_length=200, )11 slug = models.SlugField(null=True, blank=True, editable=False)1213 price_before = models.DecimalField(help_text=_('The price before bargain and/or original price'), max_digits=8, decimal_places=3)1415 price_after = models.DecimalField(help_text=_('New price or new bargain'), max_digits=8, decimal_places=3)1617 status = models.PositiveIntegerField(choices=STATUSES, default=DEFAULT_STATUS)18 start_date = models.DateField(_('Start date'), )19 due_date = models.DateField(_('Due date'))20 description = HTMLField(_('Description'), null=True, blank=True)21 created_at = models.DateTimeField(auto_now_add=True)2223 owner_ct = models.ForeignKey(ContentType, verbose_name='content type')24 owner_id = models.PositiveIntegerField('owner id')25 owner_object = generic.GenericForeignKey('owner_ct', 'owner_id')2627 view_count = generic.GenericRelation(ViewCount)28 ratings = generic.GenericRelation(Rate)29 images = generic.GenericRelation(Image)3031 def __unicode__(self):32 return u'%s' % self.headline3334 class Promotion(models.Model):35 DRAFT = 036 PUBLISHED = 137 DEFAULT_STATUS = PUBLISHED38 STATUSES = (39 (PUBLISHED, _('Published')),40 (DRAFT, _('Draft')),41 )4243 headline = models.CharField(_('Headlines'), max_length=200, )44 slug = models.SlugField(null=True, blank=True, editable=False)4546 highlight = models.CharField(_('Highlight'), max_length=100, help_text=_('Highlight label'))4748 status = models.PositiveIntegerField(choices=STATUSES, default=DEFAULT_STATUS)49 start_date = models.DateField(_('Start date'), )50 due_date = models.DateField(_('Due date'))51 description = HTMLField(_('Description'), null=True, blank=True)52 created_at = models.DateTimeField(auto_now_add=True)5354 owner_ct = models.ForeignKey(ContentType, verbose_name='content type')55 owner_id = models.PositiveIntegerField('owner id', db_index=True)56 owner_object = generic.GenericForeignKey('owner_ct', 'owner_id')5758 view_count = generic.GenericRelation(ViewCount)59 ratings = generic.GenericRelation(Rate)60 images = generic.GenericRelation(Image)6162 def __unicode__(self):63 return u'%s' % self.headline
The above two models have a lot of similarity, now we need to build a view which will be used to union the two models. In order to do that, you could create the view directly into yout database or you could build the view into an SQL file and use signals to make sure the view executes everytime you run syncdb (Will write an article later about custom SQL execution)
I wrote the below query which will union the two table and indicate from which table it was unioned to help in deterimining where this record from (bargain or promotion?)
1CREATE OR REPLACE VIEW list_item as SELECT2 *, (3 SELECT4 COALESCE (SUM(rate), 0)5 FROM6 rank_rate7 WHERE8 object_id = x."id"9 AND content_type_id = x."ct_id"10 ) AS "content_rank"11 FROM12 (13 SELECT14 "b"."id",15 "b"."headline",16 "b"."headline_en",17 "b"."headline_ar",18 "b"."slug",19 "b"."price_before",20 "b"."price_after",21 NULL AS "highlight",22 NULL AS "highlight_en",23 NULL AS "highlight_ar",24 "b"."status",25 "b"."start_date",26 "b"."due_date",27 "b"."description",28 "b"."description_en",29 "b"."description_ar",30 "b"."owner_ct_id",31 "b"."owner_id",32 "b"."created_at",33 'bargain' AS "source",34 (35 SELECT36 ID37 FROM38 django_content_type39 WHERE40 model = 'bargain'41 ) AS ct_id42 FROM43 bargain_bargain b44 UNION45 SELECT46 "p"."id",47 "p"."headline",48 "p"."headline_en",49 "p"."headline_ar",50 "p"."slug",51 NULL AS "price_before",52 NULL AS "price_after",53 "p"."highlight",54 "p"."highlight_en",55 "p"."highlight_ar",56 "p"."status",57 "p"."start_date",58 "p"."due_date",59 "p"."description",60 "p"."description_en",61 "p"."description_ar",62 "p"."owner_ct_id",63 "p"."owner_id",64 "p"."created_at",65 'promotion' AS "source",66 (67 SELECT68 ID69 FROM70 django_content_type71 WHERE72 model = 'promotion'73 ) AS ct_id74 FROM75 promotion_promotion P76 ) x;
Once you have executed your view with all required attribute, now you work on mapping your attribute to a model in django and make sure the model remains unmanaged so it won’t execute for creation
1Meta: managed=False
below is example of how my model in the end looked like
1class Item(models.Model):2 """ Item model based on view """34 DRAFT = 05 PUBLISHED = 16 DEFAULT_STATUS = PUBLISHED7 STATUSES = (8 (PUBLISHED, _('Published')),9 (DRAFT, _('Draft')),10 )1112 headline = models.CharField(_('Headlines'), max_length=200, )13 slug = models.SlugField(null=True, blank=True, editable=False)1415 price_before = models.DecimalField(help_text=_('The price before and/or original price'), max_digits=8, decimal_places=3)1617 price_after = models.DecimalField(help_text=_('New price or new bargain'), max_digits=8, decimal_places=3)1819 highlight = models.CharField(_('Highlight'), max_length=100, help_text=_('Highlight label'))20 status = models.PositiveIntegerField(choices=STATUSES, default=DEFAULT_STATUS)21 start_date = models.DateField(_('Start date'), )22 due_date = models.DateField(_('Due date'))23 description = HTMLField(_('Description'), null=True, blank=True)24 created_at = models.DateTimeField(auto_now_add=True)2526 owner_ct = models.ForeignKey(ContentType, verbose_name='content type')27 owner_id = models.PositiveIntegerField('owner id')28 owner_object = generic.GenericForeignKey('owner_ct', 'owner_id')2930 source = models.CharField(max_length=100, )31 content_rank = models.IntegerField(max_length=100, )3233 class Meta:34 managed = False # this will prevent table from executing when running syncdb35 db_table = 'list_item'3637 def __unicode__(self):38 return u'%s - %s' % (self.source, self.id)
Now the benefit of using view based model than joining queryset together is tremedous when you have performance concerns. Its easier to tune your view to the optimum performance, it also works well with pagination and ordering. When joining two queryset together, you run the risk of iterating over all your queryset to fix the ordering and eventually paginate a list, while with the view set, these will happen directly in the SQL engine.
If you have any comments or questions do not hesitate to ask.