我希望在我的查询中包含Cakephp代码

Question

我是Cakephp初学者。我想要这个带cakephp的查询。

原始查询..

SELECT `Advertisement`.*, `Gallery`.`image` FROM `moenatmi_moen`.`advertisements`
AS `Advertisement` LEFT JOIN `moenatmi_moen`.`galleries` AS `Gallery` ON 
(`Gallery`.`advertise_id` = `Advertisement`.`id` AND `Gallery`.`main_ad_image`
= 1) WHERE `Advertisement`.`status` = 1 AND `Advertisement`.`category_id` = 14 AND
((`Advertisement`.`expiry_date` >= '2014-11-18') OR (`Advertisement`.`expiry_date`
IS NULL)) GROUP BY `Advertisement`.`id` ORDER BY `Advertisement`.`id` desc
LIMIT 10

我想要这样的查询..

SELECT `Advertisement`.*, `Gallery`.`image` FROM `moenatmi_moen`.`advertisements`
AS `Advertisement` LEFT JOIN `moenatmi_moen`.`galleries` AS `Gallery` ON
(`Gallery`.`advertise_id` = `Advertisement`.`id` AND `Gallery`.`main_ad_image` = 1)
WHERE `Advertisement`.`status` = 1 AND `Advertisement`.`category_id` IN (select id from
categories where id=14 or parent_id=14) AND ((`Advertisement`.`expiry_date` >=
'2014-11-18') OR (`Advertisement`.`expiry_date` IS NULL)) GROUP BY
`Advertisement`.`id` ORDER BY `Advertisement`.`id` desc LIMIT 10

原始Cakephp代码..

$this->paginate['conditions'] = array(
        'Advertisement.status'=>1,
        'Advertisement.category_id'=>$cat_id,
        'OR'=>array(
               'Advertisement.expiry_date >='=>date('Y-m-d'),
               'Advertisement.expiry_date'=>null
                   )
       );

帮帮我..。谢谢你..

Answer 1

这应该是一个无痛切换。第一个查询在主查询中调用您想要的类别ids。

$cat_id与您在原始查询中使用的变量相同。

$ids = $this->Category->find('all', array(
    'conditions' => array(
        'id' => $cat_id, 
        'parent_id' => $cat_id
        )
    )
);

这里需要注意的重要一点是，此查询将输出典型的嵌套数组。

array(
    array(),
    array(),
    array()
);

classicExtract函数将为您筛选乱七八糟的东西，并为您提供一个包含'id‘列表的单个数组，这正是您的查询所需的。例如：

array(14, 15, 16, 18, 19);

最后，您的主要查询。

$this->paginate['conditions'] = array(
    'Advertisement.status'=>1, 
    'Advertisement.category_id'=> Set::classicExtract($ids, '{n}.Category.id'),
    'OR'=>array(
        'Advertisement.expiry_date >=' => date('Y-m-d'),
        'Advertisement.expiry_date'=>null
    )
);

结论你的新条件看起来就像这样：

$ids = $this->Category->find('all', array(
    'conditions' => array(
        'id' => $cat_id, 
        'parent_id' => $cat_id
        )
    )
);

$this->paginate['conditions'] = array(
    'Advertisement.status'=>1, 
    'Advertisement.category_id'=> Set::classicExtract($ids, '{n}.Category.id'),
    'OR'=>array(
        'Advertisement.expiry_date >=' => date('Y-m-d'),
        'Advertisement.expiry_date'=>null
    )
);