Union select multiple models in django.

Mo Mughrabi
May 6th, 2016 · 2 min read

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 = 0
3 PUBLISHED = 1
4 DEFAULT_STATUS = PUBLISHED
5 STATUSES = (
6 (PUBLISHED, _('Published')),
7 (DRAFT, _('Draft')),
8 )
9
10 headline = models.CharField(_('Headlines'), max_length=200, )
11 slug = models.SlugField(null=True, blank=True, editable=False)
12
13 price_before = models.DecimalField(help_text=_('The price before bargain and/or original price'), max_digits=8, decimal_places=3)
14
15 price_after = models.DecimalField(help_text=_('New price or new bargain'), max_digits=8, decimal_places=3)
16
17 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)
22
23 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')
26
27 view_count = generic.GenericRelation(ViewCount)
28 ratings = generic.GenericRelation(Rate)
29 images = generic.GenericRelation(Image)
30
31 def __unicode__(self):
32 return u'%s' % self.headline
33
34 class Promotion(models.Model):
35 DRAFT = 0
36 PUBLISHED = 1
37 DEFAULT_STATUS = PUBLISHED
38 STATUSES = (
39 (PUBLISHED, _('Published')),
40 (DRAFT, _('Draft')),
41 )
42
43 headline = models.CharField(_('Headlines'), max_length=200, )
44 slug = models.SlugField(null=True, blank=True, editable=False)
45
46 highlight = models.CharField(_('Highlight'), max_length=100, help_text=_('Highlight label'))
47
48 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)
53
54 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')
57
58 view_count = generic.GenericRelation(ViewCount)
59 ratings = generic.GenericRelation(Rate)
60 images = generic.GenericRelation(Image)
61
62 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 SELECT
2 *, (
3 SELECT
4 COALESCE (SUM(rate), 0)
5 FROM
6 rank_rate
7 WHERE
8 object_id = x."id"
9 AND content_type_id = x."ct_id"
10 ) AS "content_rank"
11 FROM
12 (
13 SELECT
14 "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 SELECT
36 ID
37 FROM
38 django_content_type
39 WHERE
40 model = 'bargain'
41 ) AS ct_id
42 FROM
43 bargain_bargain b
44 UNION
45 SELECT
46 "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 SELECT
68 ID
69 FROM
70 django_content_type
71 WHERE
72 model = 'promotion'
73 ) AS ct_id
74 FROM
75 promotion_promotion P
76 ) 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 """
3
4 DRAFT = 0
5 PUBLISHED = 1
6 DEFAULT_STATUS = PUBLISHED
7 STATUSES = (
8 (PUBLISHED, _('Published')),
9 (DRAFT, _('Draft')),
10 )
11
12 headline = models.CharField(_('Headlines'), max_length=200, )
13 slug = models.SlugField(null=True, blank=True, editable=False)
14
15 price_before = models.DecimalField(help_text=_('The price before and/or original price'), max_digits=8, decimal_places=3)
16
17 price_after = models.DecimalField(help_text=_('New price or new bargain'), max_digits=8, decimal_places=3)
18
19 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)
25
26 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')
29
30 source = models.CharField(max_length=100, )
31 content_rank = models.IntegerField(max_length=100, )
32
33 class Meta:
34 managed = False # this will prevent table from executing when running syncdb
35 db_table = 'list_item'
36
37 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.

More articles from Obytes

Hello world.

OBytes started as one man operation and expanded to extremely talented pool of resources. Obytes started from.

December 13th, 2015 · 1 min read

Store app secrets on ENV vars using AWS Secrets Manager and Terraform.

How to manage secrets of an application running on ECS as environment variables using AWS Secrets Manager and Terraform.

February 12th, 2020 · 2 min read

ABOUT US

Our mission and ambition is to challenge the status quo, by doing things differently we nurture our love for craft and technology allowing us to create the unexpected.