CODEWITHSUNDEEP
djangodjango-models
Giovanni Di Milia
Giovanni Di Milia

Reputation: 14041

DJANGO: how to extract all elements from a table that don't appear in another table

I have to admit that I got pretty rusty with django, so I'm sorry if the question is too simple.

I have a couple of models like the followings:

class Monograph(models.Model):
    monid = models.AutoField(primary_key=True)
    title = models.CharField("Title", max_length=2000)
    .............

class Isbn(models.Model):
    isbnid = models.AutoField(primary_key=True)
    monid = models.ForeignKey(Monograph, db_column='monid')
    isbnnum = models.CharField("ISBN", max_length=20)
    .............

What I want to extract is the list of Monographs that don't appear in the table of ISBN.

In SQL I would write this query like:

SELECT * FROM Monograph WHERE monid NOT IN (SELECT monid FROM Isbn)

Any idea?

Thanks!

Upvotes: 0

Views: 129

Answers (1)

Anton Strogonoff
Anton Strogonoff

Reputation: 34122

One way would be:

Monograph.objects.exclude(monid__in=Isbn.objects.values_list('monid', flat=True))

See docs for values_list() and __in field lookup.

Another way would be (I think this version would hit DB only once):

Monograph.objects.filter(isbns=None)

You'll need to specify related_name='isbns' on the foreign key from Isbn to Monograph for this to work. (See docs for details on backward relationships.)

Upvotes: 2

Related Questions