时间戳

首页 > 解决方案 > OrWhere 声明原则 1

doctrine - OrWhere 声明原则 1

问题描述

用一个 andWhere 语句解决了这个问题

$query->andWhere("(SELECT COUNT(*) as result FROM EvaluateurCandidature ec4 WHERE ec.candidature_id_ca = ec4.candidature_id_ca AND ec4.role_evaluateur = 'rapporteur' AND ec4.statut_evaluation = ? OR ec.candidature_id_ca = ec4.candidature_id_ca AND ec4.role_evaluateur = 'rapporteur' AND ec4.statut_evaluation = ? ) >= '2'" , array('en cours' , 'terminée'));

我尝试在查询中创建 OrWhere 语句，但是当我这样做时，它会返回不依赖 c.id_ap 的对象。

有人知道我如何在一行中使用 orWhere 实现查询吗？我试过这个没有成功

$query->andWhere("(SELECT COUNT(*) as result FROM EvaluateurCandidature ec2 WHERE ec.candidature_id_ca = ec2.candidature_id_ca AND ec2.role_evaluateur = 'evaluateur' AND ec2.statut_evaluation = 'en cours' OR ec2.statut_evaluation = 'terminée ') >= '2'");

我只需要检查 ec2.statut_evaluation = 'en cours' 或 'terminée' 中是否有超过 2 个结果

这里是实际的查询：

$query = Doctrine_Query::create()
            ->from('Candidature c')
            ->leftJoin('c.EvaluateurCandidature ec')
            ->where('c.id_stca = 5');

 $query->andWhere('c.id_ap=?', array($this->id_ap));

      $query->andWhere("(SELECT COUNT(*) as result FROM EvaluateurCandidature ec2 WHERE ec.candidature_id_ca = ec2.candidature_id_ca AND ec2.role_evaluateur = 'evaluateur' AND ec2.statut_evaluation = 'en cours') >= '2'");

      $query->orWhere("(SELECT COUNT(*) as result FROM EvaluateurCandidature ec3 WHERE ec.candidature_id_ca = ec3.candidature_id_ca AND ec3.role_evaluateur = 'evaluateur' AND ec3.statut_evaluation = 'terminée') >= '2'");

标签： doctrine

解决方案

对于您的问题，您需要对OR条件进行分组以避免此类问题，这是因为运算符优先级，AND绑定之前OR，因此您的查询被评估为

c.id_stca = some AND  (SELECT ...) >= 2 OR (SELECT ...) >= 2

它应该被归类为

c.id_stca = some AND ( (SELECT ...) >= 2 OR (SELECT ...) >= 2 )

对于学说 1，我猜想 n 查询生成器你可以把它写成

$query = Doctrine_Query::create()
            ->from('Candidature c')
            ->leftJoin('c.EvaluateurCandidature ec')
            ->where('c.id_stca = 5')
            ->andWhere('c.id_ap=?', array($this->id_ap))
            ->andWhere( 
                $query->expr()->orX(
                    $query->andWhere("(SELECT COUNT(*) as result 
                                       FROM EvaluateurCandidature ec2 
                                       WHERE ec.candidature_id_ca = ec2.candidature_id_ca 
                                       AND ec2.role_evaluateur = 'evaluateur' 
                                       AND ec2.statut_evaluation = 'en cours') >= '2'"),
                    $query->andWhere("(SELECT COUNT(*) as result 
                                       FROM EvaluateurCandidature ec3 
                                       WHERE ec.candidature_id_ca = ec3.candidature_id_ca 
                                       AND ec3.role_evaluateur = 'evaluateur' 
                                       AND ec3.statut_evaluation = 'terminée') >= '2'")
                )
            );

推荐阅读