Union select multiple models in django.

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.

Union select multiple models in django.

More articles from Obytes

Hello world.

Autoscale Celery workers on ECS Fargate based on RabbitMQ metrics.

ABOUT US