1

I have word model and phrase model

class Word(models.Model): 
        checked = models.BooleanField(default=False)

class Phrase(models.Model):
    words = models.ManyToManyField(Word, null=True,related_name = "phrases")

Word model has attribute checked and many to many connection to phrase

I need to perfom a difficult for me query:

  • We are considering that phrase is checked if it has at least one checked word.

  • How do I find all words that has only checked phrases and doesn't have unchecked phrases?

Right now I am doing using cycle through all words in db, but this is not very effective, so I am looking for more efficient way to do this.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
user2950593
  • 9,233
  • 15
  • 67
  • 131

1 Answers1

0

We can do this in two steps:

non_checked_phrases=Phrase.objects.exclude(word__checked=True)
non_checked_words = Word.objects.exclude(phrases__in=non_checked_phrases)

First we thus obtain a QuerySet of the Phrases where no of the related words are checked. Next we obtain a QuerySet where we eclude all words that have a related phrase in the previously generated QuerySet.

This will generate a query like:

SELECT word.*
FROM word
WHERE NOT (word.id IN
    (SELECT pw1.`word_id`
     FROM phrase_word AS pw1
     WHERE pw1.phrase_id IN
         (SELECT p.`id`
          FROM `auth_group` AS p
          WHERE NOT (V0.`id` IN
              (SELECT pw2.`phrase_id`
               FROM phrase_word AS pw2
               INNER JOIN word AS w2 ON pw2.word_id = w2.id
               WHERE w2.is_active = True
             )
         ))
    )
)
Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • okay, I have a problem with exclude - can you look at this?: https://stackoverflow.com/questions/51754025/django-performance-issue-with-exclude – user2950593 Aug 08 '18 at 19:11