Thanks for clarifying this with such a detailed explanation.
I took the first way you have suggested and get things working with
the following manager:
class QuestionManager(models.Manager):
def get_not_answered(self):
cursor = connection.cursor()
cursor.execute("""SELECT questions_question.id
FROM questions_question
LEFT JOIN questions_answer
ON questions_question.id =
questions_answer.question_id
GROUP BY questions_question.id,
questions_answer.id
HAVING questions_answer.id IS NULL""")
rows = cursor.fetchall()
ids = [row[0] for row in rows]
cursor.close()
return self.filter(id__in=ids)
Regards,
oMat
On 6 Eylül, 15:59, "Russell Keith-Magee" <[EMAIL PROTECTED]>
wrote:
> On 9/6/07, omat <[EMAIL PROTECTED]> wrote:
>
>
>
> > Hi,
>
> > For Question & Answer models:
>
> > Question.objects.filter(answer__isnull=False)
>
> > returns the set of questions that has at least one answer, but:
>
> > Question.objects.filter(answer__isnull=True)
>
> > does not return the questions that has no answer.
>
> > Am I missing something?
>
> Somewhat. The problem is that 'isnull' isn't doing what you think it does.
>
> When you run Question.objects.filter(answer__isnull=True), this gets
> turned into an SQL query something like:
>
> SELECT Question.* from Question INNER JOIN Answer WHERE Question.id =
> Answer.question_id WHERE Answer.id IS NULL
>
> In evaluating this query, this generates an internal table containing
> the columns:
> Question.id
> Question.field1
> Question.field2,
> ...
> Answer.id
> Answer.field1
> Answer.field2
>
> This internal table is then truncated, and only the Question columns
> are returned to create Question Django objects.
>
> This internal table will contain a row for every Question+Answer pair
> where the answer points at the question. If there is no answer to join
> with a question, then there will be no answer in the result set.
>
> Hence, when you ask for answer__isnull=False - you get all the rows
> that are returned. If you ask for answer__isnull=True, you get nothing
> - because there can't be an answer-null row that is joined with a
> Question.
>
> Now - I'm sure the next question is "how to I get all questions that
> have at least one answer".
> The best SQL way to do this is with an aggregate clause, exploiting
> GROUP BY and HAVING to establish how many joined objects exist.
> However, Django doesn't currently have good support for aggregate
> clauses (something I'm hoping to rectify soon).
>
> An alternate approach is to do it in two steps: get a list of all
> answers, find the unique question ids (using distinct() and values()
> clauses), and then ask for Question.objects.filter(id__in=[list of
> ids]), or Question.objects.exclude(id__in=[list of ids]).
>
> Yours,
> Russ Magee %-)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
