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
-~----------~----~----~----~------~----~------~--~---
