Geek Answers Handbook

Convert LEFT OUTER JOIN request to Django orm queryset / query

Given PostgreSQL 9.2.10, Django 1.8, python 2.7.5 and the following models:

class restProdAPI(models.Model):
    rest_id = models.PositiveIntegerField(primary_key=True)
    rest_host = models.CharField(max_length=20)
    rest_ip = models.GenericIPAddressField(default='0.0.0.0')
    rest_mode = models.CharField(max_length=20)
    rest_state = models.CharField(max_length=20)


class soapProdAPI(models.Model):
    soap_id = models.PositiveIntegerField(primary_key=True)
    soap_host = models.CharField(max_length=20)
    soap_ip = models.GenericIPAddressField(default='0.0.0.0')
    soap_asset = models.CharField(max_length=20)
    soap_state = models.CharField(max_length=20)

And the following raw query that returns exactly what I'm looking for:

SELECT
    app_restProdAPI.rest_id, app_soapProdAPI.soap_id, app_restProdAPI.rest_host, app_restProdAPI.rest_ip, app_soapProdAPI.soap_asset, app_restProdAPI.rest_mode, app_restProdAPI.rest_state
FROM
    app_soapProdAPI
LEFT OUTER JOIN
    app_restProdAPI
ON
    ((app_restProdAPI.rest_host = app_soapProdAPI.soap_host)
OR
    (app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip))
WHERE 
    app_restProdAPI.rest_mode = 'Excluded';

Which returns as follows:

 rest_id | soap_id |   rest_host   |     rest_ip    | soap_asset | rest_mode | rest_state
---------+---------+---------------+----------------+------------+-----------+-----------
   1234  |  12345  | 1G24019123ABC | 123.123.123.12 |  A1234567  | Excluded  |     Up

What would be the best way to create this work using the Django model and the orm structure?

I was looking for possible methods for joining two tables completely without a relationship, but there seems to be no clean or efficient way to do this. I also tried looking for methods to do left outer joins in django, but again the documentation is sparse or difficult to decrypt.

, , , Q , . , , , foreignkey() , , . . .

** 1 **

, INNER JOIN. , , - raw html.

** 2 **

( - = "-" ), , ? , , postresql- . , , , a .

soapProdAPI.objects.extra(
    select = {
        'rest_id'    : 'app_restprodapi.rest_id',
        'rest_host'  : 'app_restprodapi.rest_host',
        'rest_ip'    : 'app_restprodapi.rest_ip',
        'rest_mode'  : 'app_restprodapi.rest_mode',
        'rest_state' : 'app_restprodapi.rest_state'
    },
    tables = ['app_restprodapi'],
    where  = ['app_restprodapi.rest_mode=%s \
               AND app_restprodapi.rest_host=app_soapprodapi.soap_host \
               OR app_restprodapi.rest_ip=app_soapprodapi.soap_ip'],
    params = ['Excluded']
    )

** 3/ **

Todor , INNER JOIN, , , . , -, , , . , , , :

restProdAPI.objects.extra(
    select = {
        'soap_id'    : 'app_soapprodapi.soap_id',
        'soap_asset' : 'app_soapprodapi.soap_asset'
    },
    tables = ['app_soapprodapi'],
    where  = ['app_restprodapi.rest_mode = %s',
              'app_soapprodapi.soap_host = app_restprodapi.rest_host OR \
               app_soapprodapi.soap_ip   = app_restprodapi.rest_ip'
    ],
    params = ['Excluded']
    )

** TL;DR **

PostGreSQL ORM, Django .raw() . , , . , django-datatables-view, .

+4
2

INNER JOIN

soapProdAPI's, restProdAPI ( → ip). :

soapProdAPI.objects.extra(
    select = {
        'rest_id'   : "app_restProdAPI.rest_id",
        'rest_host' : "app_restProdAPI.rest_host",
        'rest_ip'   : "app_restProdAPI.rest_ip",
        'rest_mode' : "app_restProdAPI.rest_mode",
        'rest_state': "app_restProdAPI.rest_state"
    },
    tables = ["app_restProdAPI"],
    where = ["app_restProdAPI.rest_host = app_soapProdAPI.soap_host \
              OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip"]
)

?

.extra, . .filter select dict, soapProdAPI, Django . where kwarg .extra, , .

    where = ["app_restProdAPI.rest_host = app_soapProdAPI.soap_host \
              OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip",
             "app_restProdAPI.rest_mode=%s"
    ],
    params = ['Excluded']

soapProdAPI's , restProdAPI, , subquery , .

soapProdAPI.objects.extra(
    select = {
        'rest_id'   : "(select rest_id from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)",
        'rest_host' : "(select rest_host from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)",
        'rest_ip'   : "(select rest_ip from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)",
        'rest_mode' : "(select rest_mode from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)",
        'rest_state': "(select rest_state from app_restProdAPI where app_restProdAPI.rest_host = app_soapProdAPI.soap_host OR app_restProdAPI.rest_ip = app_soapProdAPI.soap_ip)"
    },
)
+4

, ! , Q . Django, , - :

restProdAPI.objects.filter(Q(rest_host=s1.soap_host)|Q(rest_ip=s1.soap_ip))

s1 soapProdAPI.

whote, , .

from django.db.models import Q
from core.models import restProdAPI, soapProdAPI

s1 =  soapProdAPI.objects.get(soap_id=1)

restProdAPI.objects.filter(Q(rest_id=s1.soap_id)|Q(rest_ip=s1.soap_ip))
+1

More articles:


All Articles