constructing a joined django query

Question

I'm interacting with a legacy db on another system, so the models are written in stone and not very django-ey.

My models.py:

class Site(models.Model):
    site_code = models.CharField(max_length=30, primary_key=True)
    name = models.CharField(unique=True, max_length=300)

class Document(models.Model):
    id = models.IntegerField(primary_key=True)
    site_ref = models.ForeignKey(Site)
    description = models.CharField(max_length=1500)

class DocumentStatusCategory(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(unique=True, max_length=90)

class DocumentStatus(models.Model):
    id = models.IntegerField(primary_key=True)
    document = models.ForeignKey(Document)
    status = models.ForeignKey(DocumentStatusCategory)
    changed_by = models.ForeignKey(User)
    created_at = models.DateTimeField()

In my views.py I want to retrieve a queryset with all the Document objects that belong to a specified Site (say site_ref=mysite) which do not have any related DocumentStatus objects with status=4.

Any idea how I can do this as a single (non-sql intensive) line?

Daniel Roseman · Accepted Answer · 2011-04-05 12:56:10Z

2

Document.objects.filter(site_ref=mysite).exclude(documentstatus__status_id=4)

edited Apr 5, 2011 at 12:56

answered Apr 5, 2011 at 12:39

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Josh Smeaton Over a year ago

Keep forgetting the relation span across a reverse relation. Can't it just be documentstatus__status_id though?

meepmeep Over a year ago

aha, I didn't know you could call the relation like that. Thank you!

Josh Smeaton · Accepted Answer · 2011-04-05 12:46:20Z

1

Document.objects.filter(site_ref=site_obj).exclude(documentstatus_set__in=DocumentStatus.objects.filter(status_id=4))

Not exactly one query, but I don't think that's achievable without going down to raw sql. Two queries isn't bad though I suppose.

I should mention that the above assumes that the reverse relation between Document and DocumentStatus is documentstatus_set. You can explicitly state what the reverse relation is like so:

# inside the DocumentStatus model definition
document = models.ForeignKey(Document, related_name='document_statuses')

Then the query becomes:

Document.objects.filter(site_ref=site_obj).exclude(document_statuses__in=DocumentStatus.objects.filter(status_id=4))

edited Apr 5, 2011 at 12:46

answered Apr 5, 2011 at 12:37

Josh Smeaton

48.8k24 gold badges137 silver badges165 bronze badges

Collectives™ on Stack Overflow

constructing a joined django query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related