When I checked the queries generated, it appears that the isnull
lookups result in INNER JOINs for my case.
On 6 Eylül, 16:38, omat <[EMAIL PROTECTED]> wrote:
> Malcolm, these are the models:
>
> class Question(models.Model):
> question = models.CharField(max_length=150)
> slug = models.SlugField(max_length=150,
> editable=False)
> verb = models.ForeignKey(Verb)
> added = models.DateTimeField(editable=False)
> user = models.ForeignKey(User)
>
> class Answer(models.Model):
> question = models.ForeignKey(Question)
> answer = models.PositiveSmallIntegerField(choices=((1, 'Yes'), (2,
> 'No')))
> user = models.ForeignKey(User)
> added = models.DateTimeField(editable=False)
>
> On 6 Eylül, 16:34, omat <[EMAIL PROTECTED]> wrote:
>
> > 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
-~----------~----~----~----~------~----~------~--~---
