Django Union Query from multiple databases and Output the result in the specific format

Question

Since I am new to django, I know how to query a single model in Django but I need to query multiple models at the same time.

I need to develop a UNION query in Django with 3 models namely WebQuery,WebReply and BusinessOwners and the output should be of the form below.

{
    "(#conversation_id#)_(#b_id#)": {
        "from": "(#user_id)",
        "email": "(#user_email)",
        "date_time": "#get from db",
        "query": "are you open ?",
        "from_r_id": "(#representative_id)",
        "from_r_name": "(#rep_name)",
        "business_registered": "FALSE"
        "to_business_name": "CCD saket",
        "chat": [{
            "direction": 1,
            "text": "yes sir",
            "date_time": "424 577"
        }, {
            "direction": 0,
            "text": "ok",
            "date_time": "424 577"
        }]
    },

Here is my models.py file.

class Businessowners(models.Model):
    b_id = models.IntegerField()
    userid = models.IntegerField(primary_key=True, db_column='UserID')
    email = models.CharField(max_length=150L)
    b_name = models.CharField(max_length=100L)
    class Meta:
        db_table = 'businessowners'

class Users(models.Model):
    user_id = models.BigIntegerField(primary_key=True)
    username = models.CharField(max_length=20L)
    email = models.CharField(max_length=50L)
    date_time = models.DateTimeField()
    class Meta:
        db_table = 'users'

class WebQuery(models.Model):
    query_id = models.BigIntegerField(unique=True)
    conversation_id = models.CharField(max_length=50L)
    u_id = models.CharField(max_length=50L)
    u_query = models.CharField(max_length=500L)
    sent_to = models.CharField(max_length=500L)
    date_time = models.DateTimeField()
    is_reply = models.IntegerField()
    is_responded = models.IntegerField()
    class Meta:
        db_table = 'web_query'

class WebReply(models.Model):
    reply_id = models.IntegerField(primary_key=True)
    query_id = models.BigIntegerField()
    conversation_id = models.CharField(max_length=50L)
    b_id = models.CharField(max_length=20L)
    u_query = models.CharField(max_length=500L)
    user_id = models.CharField(max_length=20L)
    date_time = models.DateTimeField()
    class Meta:
        db_table = 'web_reply'

If you are generating JSON, you probably want to take a look at the Python Rest Framework and their serialization classes. — Paulo Scardine, Jul 16 '13 at 05:47
Your sample for the desired output looks like valid JSON to me. — Paulo Scardine, Jul 16 '13 at 06:29
What if I just want to query those 3 models, apply some loops in it thereby getting the desired O/P.? — Praful Bagai, Jul 16 '13 at 06:41
what I want to know is how to query 3 models simultaneously, ie , how to apply union query in those 3 models? — Praful Bagai, Jul 16 '13 at 06:42
In order to retrieve related models, the best way is using explicit relationships (models.ForeignKey and/or models.ManyToMany). — Paulo Scardine, Jul 16 '13 at 06:51
I'm commenting and not answering because I cant afford to write a potentially long answer right now. See [select_related()](https://docs.djangoproject.com/en/dev/ref/models/querysets/#select-related) — Paulo Scardine, Jul 16 '13 at 06:59
Nevermind. I'll wait for your answer. Just answer when you get free. Cheers. — Praful Bagai, Jul 16 '13 at 07:07

score 0 · Answer 1 · edited May 23 '17 at 12:21

0

This question is so similar to another which has recently been asked that I recommend you taking a look at Django Union Query.

Basically the way forwards would be to use SQL views if all the sources can be gathered. Then query that view.

edited May 23 '17 at 12:21

Community

1
1

answered Jul 16 '13 at 08:17

Matt Seymour

8,880
7
60
101

Django Union Query from multiple databases and Output the result in the specific format

1 Answers1