django queryset excluding entries in second model

I'm making a little vocabulary-quiz app, and the basic model for a word is this:

class Word(models.Model):
    id = models.AutoField(primary开发者_Python百科_key=True)
    word = models.CharField(max_length=80)
    id_image = models.ForeignKey(Image)
    def __unicode__(self):
        return self.word
    class Meta:
        db_table = u'word'

The model for words I'm currently quizzing myself on is this:

class WordToWorkOn(models.Model):
    id = models.AutoField(primary_key=True)
    id_student = models.ForeignKey(Student)
    id_word = models.ForeignKey(Word)
    level = models.IntegerField()
    def __unicode__(self):
        return u'%s %s' % (self.id_word.__unicode__(), self.id_student.__unicode__() )
    class Meta:
        db_table = u'word_to_work_on'

Where "level" indicates how well I've learned it. The set of words I've already learned has this model:

class WordLearned(models.Model):
    id = models.AutoField(primary_key=True)
    id_word = models.ForeignKey(Word, related_name='word_to_learn')
    id_student = models.ForeignKey(Student, related_name='student_learning_word')
    def __unicode__(self):
        return u'%s %s' % (self.id_word.__unicode__(), self.id_student.__unicode__() )
    class Meta:
        db_table = u'word_learned'

When a queryset on WordToWorkOn comes back with too few results (because they have been learned well enough to get moved into WordLearned and deleted from WordToWorkOn), I want to find a Word to add to it. The part I don't know a good way to do is to limit it to Words which are not already in WordLearned.

So, generally speaking, I think I want to do an .exclude() of some sort on a queryset of Words, but it needs to exclude based on membership in the WordLearned table. Is there a good way to do this? I find lots of references to joining querysets, but couldn't find a good one on how to do this (probably just don't know the right term to search for).

I don't want to just use a flag on each Word to indicate learned, working on it, or not learned, because eventually this will be a multi-user app and I wouldn't want to have flags for every user. Hence, I thought multiple tables for each set would be better.

All advice is appreciated.

Firstly, a couple of notes about style.

There's no need to prefix the foreign key fields with id_. The underlying database field that Django creates for those FKs are suffixed with _id anyway, so you'll get something like id_word_id in the db. It'll make your code much clearer if you just call the fields 'word', 'student', etc.

Also, there's no need to specify the id autofields in each model. They are created automatically, and you should only specify them if you need to call them something else. Similarly, no need to specify db_table in your Meta, as this is also done automatically.

Finally, no need to call __unicode__ on the fields in your unicode method. The string interpolation will do that automatically, and again leaving it out will make your code much easier to read. (If you really want to do it explicitly, at least use the unicode(self.word) form.)

Anyway, on to your actual question. You can't 'join' querysets as such - the normal way to do a cross-model query is to have a foreignkey from one model to the other. You could do this:

words_to_work_on = Word.objects.exclude(WordLearned.objects.filter(student=user))

which under the hood will do a subquery to get all the WordLearned objects for the current user and exclude them from the list of words returned.

However, and especially bearing in mind your future requirement for a multiuser app, I think you should restructure your tables. What you want is a ManyToMany relationship between Word and Student, with an intermediary table capturing the status of a Word for a particular Student. That way you can get rid of the WordToWorkOn and WordLearned tables, which are basically duplicates.

Something like:

class Word(models.Model):
    word = models.CharField(max_length=80)
    image = models.ForeignKey(Image)
    def __unicode__(self):
        return self.word

class Student(models.Model):
     ... name, etc ...
     words = models.ManyToManyField(Word, through='StudentWord')

class StudentWord(models.Model):
    word = models.ForeignKey(Word)
    student = models.ForeignKey(Student)
    level = models.IntegerField()
    learned = models.BooleanField()

Now you can get all the words to learn for a particular student:

words_to_learn = Word.objects.filter(studentword__student=student, studentword__learned=False)