MySQL 多个 AND 语句条件 - CakePHP

Question

只有两个 AND 语句中的第二个正在为我阅读。我相信我遇到了嵌套问题，但目前不确定:

...'conditions' => array(
            "AND" => array(
                array($ApplicantAge . ' BETWEEN Age.Min_Age AND Age.Max_age'),
                'Zips.title'       => $Zip,
                'Applicant.amount' => array($comboType, $memberCount)
            ),
            "AND" => array(
                array($PsSpouseAge . ' BETWEEN Age.Min_Age AND Age.Max_age'),
                'PlanDetail.company_id' => '27',
                'Zips.title'            => $Zip,
                'Applicant.amount'      => array($comboType, $memberCount)
            )
        ),...

如您所见，我正在尝试获取所有记录 $ApplicantAge 。 ' 介于 Age.Min_Age 和 Age.Max_age 之间，且仅限特定记录 WHERE 'PlanDetail.company_id' => '27 和 $PsSpouseAge 。 '在 Age.Min_Age 和 Age.Max_age 之间'),...

这是我的 SQL 输出:

WHERE ((18 BETWEEN `Age`.`Min_Age` AND `Age`.`Max_age`) OR (`Zips`.`title` = '98605') OR (`Applicant`.`amount` IN ('as', '2'))) AND ((24 BETWEEN `Age`.`Min_Age` AND `Age`.`Max_age`) AND (`PlanDetail`.`company_id` = 27) AND (`Zips`.`title` = '97378') AND (`Applicant`.`amount` IN ('as', '2')))

就目前情况而言，'PlanDetail.company_id' => '27' 是最重要的，并且只有满足此 'PlanDetail.company_id' => '27' 的结果强>标准正在显示

我已经从事这个工作有一段时间了，所以一切都开始在眼睛 View 中混在一起。感谢您注意到我的 SQL 逻辑的任何问题。

现在这是我的完整加入和条件:

 $options = array(
        'joins'      => array(
            array(
                'table'      => 'plans_zips',
                'alias'      => 'PZips',
                'type'       => 'inner',
                'foreignKey' => false,
                'conditions' => array('Plan.id = PZips.plan_id')
            ),
            array(
                'table'      => 'zips',
                'alias'      => 'Zips',
                'type'       => 'inner',
                'foreignKey' => false,
                'conditions' => array('Zips.id = PZips.zip_id')
            )
        ),
        'conditions' => array(
            'OR' => array(
                'AND' => array(
                    array($ApplicantAge . ' BETWEEN Age.Min_Age AND Age.Max_age'),
                    'Zips.title'       => $Zip,
                    'Applicant.amount' => array($comboType, $memberCount),
                    'NOT'              => array(
                        'PlanDetail.company_id' => 27
                    )
                ),
                array($PsSpouseAge . ' BETWEEN Age.Min_Age AND Age.Max_age')
            )
        ),

Answer 1

数组键是唯一的，不能有两个“AND”键。此案例为covered in the manual ，只需将它们包装在另一个数组中:

array(
    array('and' => array(…)),
    array('and' => array(…))
)

因为默认情况下条件是AND，但您可以完全忽略它。 (a AND b AND c) AND (d AND e AND f) 与 a AND b AND c AND d AND e AND f 相同。

在我看来，您发布的条件可以简化为:

'conditions' => array(
    'Zips.title'            => $Zip,
    'Applicant.amount'      => array($comboType, $memberCount),
    'PlanDetail.company_id' => 27,
    "$ApplicantAge BETWEEN Age.Min_Age AND Age.Max_age",
    "$PsSpouseAge BETWEEN Age.Min_Age AND Age.Max_age"
)

---

显然你想要这样的东西:

'conditions' => array(
    'or' => array(
        'and' => array(
            "$ApplicantAge BETWEEN Age.Min_Age AND Age.Max_age",
            'Zips.title'       => $Zip,
            'Applicant.amount' => array($comboType, $memberCount),
            'not' => array(
                'PlanDetail.company_id' => 27
            )
        ),
        "$PsSpouseAge BETWEEN Age.Min_Age AND Age.Max_age"
    )
)