Converting LEFT OUTER JOIN query to Django orm queryset/query

Question

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 which returns exactly what I am 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 like this:

 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 method for making this work using Django's model and orm structure?

I have been looking around for possible methods for joining the two tables entirely without a relationship but there does not seem to be a clean or efficient way to do this. I have also tried looking for methods to do left outer joins in django, but again documentation is sparse or difficult to decipher.

I know I will probably have to use Q objects to do the or clause I have in there. Additionally I have looked at relationships and it looks like a foreignkey() may work but I am unsure if this is the best method of doing it. Any and all help would be greatly appreciated. Thank you in advance.

** EDIT 1 **

So far Todor has offered a solution that uses a INNER JOIN that works. I may have found a solution HERE if anyone can decipher that mess of inline raw html.

** EDIT 2 **

Is there a way to filter on a field (where something = 'something') like my query above given, Todor's answer? I tried the following but it is still including all records even though my equivalent postresql query is working as expected. It seems I cannot have everything in the where that I do because when I remove one of the or statements and just do a and statement it applies the excluded filter.

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']
    )

---

** EDIT 3 / CURRENT SOLUTION IN PLACE **

To date Todor has provided the most complete answer, using an INNER JOIN, but the hope is that this question will generate thought into how this still may be accomplished. As this does not seem to be inherently possible, any and all suggestions are welcome as they may possibly lead to better solutions. That being said, using Todor's answer, I was able accomplish the exact query I needed:

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']
    )

---

** TLDR **

I would like to convert this PostGreSQL query to the ORM provided by Django WITHOUT using .raw() or any raw query code at all. I am completely open to changing the model to having a foreignkey if that facilitates this and is, from a performance standpoint, the best method. I am going to be using the objects returned in conjunction with django-datatables-view if that helps in terms of design.

Answer 1

Solving it with INNER JOIN

In case you can go with only soapProdAPI's that contain corresponding restProdAPI ( in terms of your join statement -> linked by host or ip). You can try the following:

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"]
)

How to filter more?

Since we are using .extra I would advice to read the docs carefully. In general we can't use .filter with some of the fields inside the select dict, because they are not part of the soapProdAPI and Django can't resolve them. We have to stick with the where kwarg in .extra, and since it's a list, we better just add another element.

    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']

Repeated subquery

If you really need all soapProdAPI's no matter if they have corresponding restProdAPI I can only think of a one ugly example where a subquery is repeated for each field you need.

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)"
    },
)

Answer 2

I think this could be usefull for you! Effectively, you can use Q to construct your query. I try it the Django shell, I create some data and I did something like this:

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

Where s1 is a soapProdAPI.

This is all the code i whote, you can try it and to see if can help you

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))