MySQL multiple AND statement conditions - CakePHP

Only the 2nd of the two AND statements are reading for me. I believe I am running into a nesting issue, but not sure at this point:

...'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)
            )
        ),...

As you can see I am trying to get ALL records $ApplicantAge . ' BETWEEN Age.Min_Age AND Age.Max_age AND ONLY specific records WHERE 'PlanDetail.company_id' => '27 AND $PsSpouseAge . ' BETWEEN Age.Min_Age AND Age.Max_age'),...

Here is my SQL output:

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')))

As it stands, the 'PlanDetail.company_id' => '27', is overriding and only the results that meet this 'PlanDetail.company_id' => '27' criteria are showing

I've been at this for a while, so its all starting to mash up in eye view.. Thanks for noting any problems with my SQL logic.

Here is my full joins and conditions now:

 $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')
            )
        ),

Array keys are unique, you can't have two "AND" keys. This case is covered in the manual, just wrap them in another array:

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

Since conditions are AND by default though you could leave it out completely. (a AND b AND c) AND (d AND e AND f) is the same as a AND b AND c AND d AND e AND f.

Looks to me like the condition you posted could be simplified to this:

'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"
)

Apparently you want something like this though:

'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"
    )
)

I was able to get it working this way (and enhanced by adding a second query):

'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(array('PlanDetail.company_id' => array('27','3')))),
                array(
                    $PsSpouseAge . ' BETWEEN Age.Min_Age AND Age.Max_age',
                    'Zips.title'            => $Zip,
                    'Applicant.amount'      => array($comboType, $memberCount),
                    'PlanDetail.company_id' => '27'),
                array(
                    $OdsSpouseAge . ' BETWEEN Age.Min_Age AND Age.Max_age',
                    'Zips.title'            => $Zip,
                    'Applicant.amount'      => array($comboType, $memberCount),
                    'PlanDetail.company_id' => '3'))
    ),

The resultant SQL is:

WHERE 
((((18 BETWEEN `Age`.`Min_Age` AND
`Age`.`Max_age`) AND
(`Zips`.`title` = '98258') AND
(`Applicant`.`amount` IN ('as', '2')) AND
(NOT (`PlanDetail`.`company_id` IN (27, 3)))))
OR
(((50 BETWEEN `Age`.`Min_Age` AND
`Age`.`Max_age`) AND
(`Zips`.`title` = '98258') AND
(`Applicant`.`amount` IN ('as', '2')) AND
(`PlanDetail`.`company_id` = 27)))
OR
(((50 BETWEEN `Age`.`Min_Age`AND
`Age`.`Max_age`) AND
(`Zips`.`title` = '98258') AND
(`Applicant`.`amount` IN ('as', '2')) AND
(`PlanDetail`.`company_id` = 3))))

I am open to any suggested optimization ideas for this. But this is the only way I could get it working.