问卷调查问卷数据库设计SQL显示questi0n或基于先前答案的选择django数据库

Question

我有一个调查应用程序，我需要限制一个问题可用的答案选择基于用户以前的答案。

为了实现这一点，我认为引入依赖关系表是个好主意。

我觉得我快到了，但我不知道如何为我想要的结果构造正确的查询，或者我是否正在以最简单的方式来处理这个问题。

我的桌子是：

• 用户
• 问题
• 选择
• 回答
• 依赖性
• Choice_Dependencies (多到多通过选择和依赖)

依赖关系引用一个问题和一个选择。每个依赖项都有一个类似于“是男性”的描述字段，因此如果问题1是“您是男性还是女性？”问题1有两个选择，“男性”choice_id=1，“女性”choice_id=2。那么这个依赖记录将引用问题1和选择1。

然后，您可以将这种依赖与许多选择联系起来。

因此，如果问题2是“你最喜欢这些东西中的哪一件？”可供选择的记录是化妆，裙子，汽车，油脂，

汽车和油脂与“是男性”的依赖性有关，而化妆和裙子则与“女性”有关。

我需要弄清楚如何编写一个查询，获取满足每个依赖项的所有选项。

我想的另一种方法是获得任何不能满足的选择，并将这些选择排除在可用的选择之外。也许不是子问询？

问题也与他们所拥有的选择有关。但是我可以处理查询的那个部分。

这是一个django应用程序，但我不在乎是否必须使用原始sql。下面是我的表结构，正如上面用SQL和django模型语法解释的那样。

SQL

CREATE TABLE public.auth_user (
    id serial NOT NULL,
    password varchar(128) NOT NULL,
    last_login timestamp with time zone NOT NULL,
    is_superuser boolean NOT NULL,
    username varchar(30) NOT NULL,
    first_name varchar(30) NOT NULL,
    last_name varchar(30) NOT NULL,
    email varchar(75) NOT NULL,
    is_staff boolean NOT NULL,
    is_active boolean NOT NULL,
    date_joined timestamp with time zone NOT NULL
)

CREATE TABLE public.bny_question (
    id serial NOT NULL,
    section_id integer NOT NULL,
    input_type_id integer NOT NULL,
    "order" integer NOT NULL,
    text text NOT NULL
)

CREATE TABLE public.bny_dependency (
    id serial NOT NULL,
    question_id integer,
    choice_id integer,
    description varchar(255) NOT NULL
)

CREATE TABLE public.bny_choice_dependencies (
    id serial NOT NULL,
    choice_id integer NOT NULL,
    dependency_id integer NOT NULL
)

CREATE TABLE public.bny_choice (
    id serial NOT NULL,
    question_id integer NOT NULL,
    text varchar(255) NOT NULL,
    value varchar(255) NOT NULL,
    blurb text NOT NULL,
    "order" integer NOT NULL
)

CREATE TABLE public.bny_answer (
    id serial NOT NULL,
    user_id integer NOT NULL,
    question_id integer NOT NULL,
    choice_id integer NOT NULL
)

姜戈

class Question(models.Model):
    section = models.ForeignKey('Section')
    input_type = models.ForeignKey('Input_type')
    order = models.IntegerField()
    text = models.TextField()

    class Meta:
        ordering = ['order']

    def get_next(self):
        next = Question.objects.filter(id__gt=self.id)
        if next:
          return next[0]
        return None

    def __unicode__(self):
        return u"%s" % (self.text)

class Dependency(models.Model):
    question = models.ForeignKey('Question', null=True)
    choice = models.ForeignKey('Choice', null=True)
    description = models.CharField(max_length=255, blank=True)

    class Meta:
        verbose_name_plural = "dependencies"

    def __unicode__(self):
        return u"%s [%s - %s]" % (self.description, self.question, self.choice)

class Choice(models.Model):    
    question = models.ForeignKey('Question', related_name='choices')
    dependencies = models.ManyToManyField('Dependency', related_name='dependent_choices', null=True)    
    text = models.CharField(max_length=255)
    value = models.CharField(max_length=255)
    blurb = models.TextField()
    order = models.IntegerField()

    class Meta:
        ordering = ['order']

    def __unicode__(self):
        return u"%s" % (self.value)

class Answer(models.Model):
    user = models.ForeignKey(User, related_name='answers')
    question = models.ForeignKey('Question')
    choice = models.ForeignKey('Choice')

    class Meta:
        unique_together = ('user', 'question',)

    def __unicode__(self):
        return u"%s - %s" % (self.question, self.choice)

Answer 1

哇，好吧，这真的让我大吃一惊，我花了好几个小时在这上面。如果有人知道更好的方法让我知道。

通过使用以下SQL查询，我能够获得所需的确切结果，该查询使用的是NOT IN子查询。

SELECT *
FROM public.bny_choice bc
WHERE bc.question_id = 6
  AND bc.id NOT IN
    ( SELECT bc1.id
     FROM public.bny_choice bc1
     INNER JOIN public.bny_question bq ON (bc1.question_id = bq.id)
     INNER JOIN public.bny_choice_dependencies bcd ON (bc1.id = bcd.choice_id)
     INNER JOIN public.bny_dependency bd ON (bcd.dependency_id = bd.id)
     INNER JOIN public.bny_question bq1 ON (bd.question_id = bq1.id)
     INNER JOIN public.bny_answer ba ON (bq1.id = ba.question_id)
     WHERE ba.user_id = 1
       AND ba.choice_id != bd.choice_id)

为了在原生的djagno查询语法中做到这一点，事情变得有点困难。

Django通过与NOT IN中的__in操作符一起使用Choice.objects.exclude()来支持!=，而filter...great...but却不支持!=操作符。

相反，您可以使用Q对象加上一个~

Choice.objects.filter(~Q(dependencies__choice = dependencies__question__answer__choice)

这不起作用，因为Django希望方程两边的1项为文字，如果要将其与同一表中的值进行比较，则必须使用F对象，因此它现在变成.

Choice.objects.filter(~Q(dependencies__choice = F('dependencies__question__answer__choice'))

然而，这并不是一个真正的!=，它实际上等同于以下的SQL.

SELECT •••
FROM "bny_choice"
INNER JOIN "bny_choice_dependencies" ON ("bny_choice"."id" = "bny_choice_dependencies"."choice_id")
INNER JOIN "bny_dependency" ON ("bny_choice_dependencies"."dependency_id" = "bny_dependency"."id")
INNER JOIN "bny_question" ON ("bny_dependency"."question_id" = "bny_question"."id")
INNER JOIN "bny_answer" ON ("bny_question"."id" = "bny_answer"."question_id")
WHERE NOT ("bny_choice"."id" IN
             (SELECT •••
              FROM "bny_choice" U0
              INNER JOIN "bny_choice_dependencies" U1 ON (U0."id" = U1."choice_id")
              INNER JOIN "bny_dependency" U2 ON (U1."dependency_id" = U2."id")
              INNER JOIN "bny_question" U3 ON (U2."question_id" = U3."id")
              INNER JOIN "bny_answer" U4 ON (U3."id" = U4."question_id")
              WHERE U2."choice_id" = U4."choice_id"))
ORDER BY "bny_choice"."order" ASC

如果将排除与=结合使用，则生成完全相同的SQL。

Choice.objects.exclude(dependencies__choice = F('dependencies__question__answer__choice'))

SELECT •••
FROM "bny_choice"
INNER JOIN "bny_choice_dependencies" ON ("bny_choice"."id" = "bny_choice_dependencies"."choice_id")
INNER JOIN "bny_dependency" ON ("bny_choice_dependencies"."dependency_id" = "bny_dependency"."id")
INNER JOIN "bny_question" ON ("bny_dependency"."question_id" = "bny_question"."id")
INNER JOIN "bny_answer" ON ("bny_question"."id" = "bny_answer"."question_id")
WHERE NOT ("bny_choice"."id" IN
             (SELECT •••
              FROM "bny_choice" U0
              INNER JOIN "bny_choice_dependencies" U1 ON (U0."id" = U1."choice_id")
              INNER JOIN "bny_dependency" U2 ON (U1."dependency_id" = U2."id")
              INNER JOIN "bny_question" U3 ON (U2."question_id" = U3."id")
              INNER JOIN "bny_answer" U4 ON (U3."id" = U4."question_id")
              WHERE U2."choice_id" = U4."choice_id"))
ORDER BY "bny_choice"."order" ASC

但我从不放弃所以..。

多亏了asmoore82，我在https://code.djangoproject.com/ticket/5763上读到

类似地，您几乎可以用一个hokey Q(__lt)区Q(__gt)装置来近似__lt。

最后的Django结果..。

x = Choice.objects.filter(
        Q(dependencies__question__answer__user = self.user),
        Q(dependencies__choice__lt = F('dependencies__question__answer__choice')) | Q(dependencies__choice__gt = F('dependencies__question__answer__choice')) # Django workaround for not equals https://code.djangoproject.com/ticket/5763
    ).values('id')        
    self.choices = Choice.objects.filter(question = self.question).exclude(id__in=x)

这给了我我需要的确切结果。

Django发出以下SQL

SELECT •••
FROM "bny_choice"
WHERE ("bny_choice"."question_id" = 6
       AND NOT ("bny_choice"."id" IN
                  (SELECT •••
                   FROM "bny_choice" U0
                   INNER JOIN "bny_choice_dependencies" U1 ON (U0."id" = U1."choice_id")
                   INNER JOIN "bny_dependency" U2 ON (U1."dependency_id" = U2."id")
                   INNER JOIN "bny_question" U3 ON (U2."question_id" = U3."id")
                   INNER JOIN "bny_answer" U4 ON (U3."id" = U4."question_id")
                   WHERE (U4."user_id" = 1
                          AND (U2."choice_id" < U4."choice_id"
                               OR U2."choice_id" > U4."choice_id")))))
ORDER BY "bny_choice"."order" ASC

该查询只需2.89ms和1个查询，而在进行应用程序之前，我要检查每个选项是否满足依赖关系，并在123 in中进行了163次查询