Django LEFT OUTER JOIN on two columns, where one is not a foreign key

Question

I have two models:

class ObjectLock(models.Model):
    partner = models.ForeignKey(Partner)
    object_id = models.CharField(max_length=100)

    class Meta:
        unique_together = (('partner', 'object_id'),)

class ObjectImportQueue(models.Model):
    partner = models.ForeignKey(Partner)
    object_id = models.CharField(max_length=100)
    ... # other fields
    created = models.DateTimeField(auto_now_add = True)
    modified = models.DateTimeField(auto_now = True, db_index=True)

    class Meta:
        ordering = ('modified', 'created')

There is nothing remarkable about the third model mentioned above (Partner).

I would like to get something like:

SELECT * FROM ObjectImportQueue q LEFT OUTER JOIN ObjectLock l ON
q.partner_id=l.partner_id AND q.object_id=l.object_id WHERE l.object_id
IS NULL and l.partner_id IS NULL;

I came across this page that tells how to make custom joins, and I tried passing column names in the tuple to join the place of the column name for the join, and that didn't work. The partner table should not be included in the resulting sql query, but I agree with the answer, which includes it if it efficiently performs what I'm trying to do with a single query.

Answer 1

. , , , .

ORM Django, SQL , models.Model.

"--" (ForeignKey), " " (ManyToManyField), - (OneToOneField).

. ObjectLock ObjectImportQueue , partner object_id field.yon "--".

:

class ObjectImportQueue(models.Model):
    partner = models.ForeignKey(Partner)
    object_id = models.CharField(max_length=100)
    created = models.DateTimeField(auto_now_add = True)
    modified = models.DateTimeField(auto_now = True, db_index=True)

    def __unicode__(self):
        return u"%s:%s" % (self.partner, self.object_id)

    class Meta:
        ordering = ('modified', 'created')

class ObjectLock(models.Model):
    lock = models.OneToOneField(ObjectImportQueue, null=True)

    class Meta:
        unique_together = (('lock',),)

, OneToOneField .

>>> p1 = Partner.objects.get(pk=1)
>>> p2 = Partner.objects.get(pk=2)
>>> Q1 = ObjectImportQueue.objects.create(partner=p1,object_id='id_Q1')
>>> Q2 = ObjectImportQueue.objects.create(partner=p2,object_id='id_Q2')
>>> ObjectImportQueue.objects.filter(lock__isnull=True)
[<ObjectImportQueue: Partner object:id_Q1>, <ObjectImportQueue: Partner object:id_Q2>]
>>> L1 = ObjectLock.objects.create(lock=Q1)
>>> ObjectImportQueue.objects.filter(lock__isnull=True)
[<ObjectImportQueue: Partner object:id_Q2>]

ObjectLock.objects.create ObjectImportQueue.objects.filter(lock__isnull=True) .

, ORM . Django , Query .

Answer 2

Django 1.2+ SQL, , .

Answer 3

.

,

CREATE VIEW ImporQueueLock AS (
  SELECT q.id, l.id
    FROM ObjectImportQueue q
    LEFT OUTER JOIN ObjectLock l
        ON q.partner_id=l.partner_id AND q.object_id=l.object_id
  )

django

class ImportQueueLock(models.Model):

    queue = models.ForeignKey(ObjectImportQueue, db_column='q')
    lock = models.ForeignKey(ObjectLock, db_column='l')

ManyToMany Django ObjectLock ObjectImportQueue ImportQueueLock

class ObjectLock(models.Model):
    partner = models.ForeignKey(Partner)
    object_id = models.CharField(max_length=100)
    queue = models.ManyToManyField(ObjectImportQueue, through = ImportQueueLock)

ObjectLock.objects.filter(importqueuelock__objectimportqueue__ .....)