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.
