CODEWITHSUNDEEP
sqldjangojoin
Eddified
Eddified

Reputation: 3154

Django LEFT OUTER JOIN on TWO columns where one isn't a foreign key

I have two models like so:

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 notable about the third model mentioned above (Partner).

I'd 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 do custom joins, and I tried passing in a tuple of the column names to join in place of the column name to join, and that didn't work. The Partner table shouldn't need to be included in the resulting sql query but I will accept an answer that does include it as long as it effectively does what I'm trying to do with one query.

Upvotes: 4

Views: 2604

Answers (3)

xavierskip
xavierskip

Reputation: 441

I also meet a similar question.but finally,I found I asked a wrong question to be solve.

in the Django ORM,the condition of SQL join is base on what the models.Model fields defined.

there are Many-to-one relationships (ForeignKey),Many-to-many relationships(ManyToManyField),One-to-one relationships(OneToOneField).

in your situation.ObjectLockModel and ObjectImportQueueModel have the same part of fields, the partnerfield and object_idfield.yon should use One-to-one relationships.

you can change your Model like this:

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

order of Model is import,OneToOneField argument model must come first.

>>> 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.createlock a object ObjectImportQueue.objects.filter(lock__isnull=True) select object don't be lock.

if you use the appropriate relationships, generate the ORM query will be easy.In Django，Define the relationships during you build the Model is better than use Query statement to relation the relationships between tables.

Upvotes: 1

Ghislain Leveque
Ghislain Leveque

Reputation: 968

I just found a solution to this problem.

You have to create a view that does the join for you

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
  )

Then make a django model for that view

class ImportQueueLock(models.Model):

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

Then make a ManyToMany on your Django model from ObjectLock to ObjectImportQueue through ImportQueueLock

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

and you will be able to do

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

Upvotes: 0

Tom
Tom

Reputation: 22841

If you're using Django 1.2+ and know the SQL you want, you could always fall back to a Raw Query.

Upvotes: 2

Related Questions