How would I do this without an explicit SQL JOIN?

Question

I have a model called Question and another one called Answer.

class Question(models.Model):
    text = models.CharField(max_length=140)

class Answer(models.Model): 
    user = models.ForeignKey(User)
    question = models.ForeignKey(Question)
    uncertain = models.BooleanField()
    text = models.CharField(max_length=30)

Now I'd like to have a QuerySet questions which is equivalent to Question.objects.all() but including the currently logged in user's answers. I'm almost completely sure that this could be accomplished by an explicit JOIN in SQL, but I'm certain there is a better way to do this using Django's ORM.

So how would I do that?

EDIT: To clarify, I'd like to be able to loop through the QuerySet, e.g. for q in questions and then be able to see whether or not the user has answered it through something like q.answered which could be a boolean, or q.answer which could be the Answer object itself (or None).

Answer 1

Are you looking for something like this?

[ item.question for question in Answer.objects.filter(user=request.user) ]

Or perhaps you want to do a

questions = Question.objects.all()
for question in questions:
    question.answer = question.answer_set.filter(user=request.user)

I guess I'm not understanding the question and which direction you want to go...

EDIT: Ok, based on your edit, perhaps the latter... you can see if it's an empty list or tack on a .count() and see if the value is greater than 0.

Answer 2

I think you're overthinking things. Essentially what you want to know (as far as I can tell - correct me if I'm wrong) is which questions have been answered by a given user, and which questions haven't (the complement). So:

user_answered_questions = set([answer.question for answer in user.answer_set.all()])

Then unanswered questions are everything else. So:

user_unanswered_questions = set(Question.objects.all()) - answered_questions

The point of doing it this way rather than uzi's solution is that you should use what you know sooner rather than later. If you filter the answer_set for every question by user, your initial query will be selecting every single answer and every single question in these tables. By starting from the known data (i.e. the user) we are selecting only answers and questions related to that user.

EDIT:

So if you then want to combine the two sets into a single list, there are a bunch of ways you could do this for instance by creating a list of tuples:

questions = [(q, q in user_answered_questions) for q in Question.objects.all()]

Then you can iterate over questions, retaining the order of the queryset:

for q in questions:
    print "it is %s that this user answered the \"%s\" question" % (q[1], q[0])

It's not pretty but you get the point - you've already determined what you wanted to determine, it's just a matter of contorting it to whatever data structure you happen to need.

Answer 3

What is wrong with

Question.objects.filter(answer__user=current_user).select_related()