php - 使用 Zend_Db_Expr 选择 Zend DB

Question

我已经以 Zend 框架方式转换了以下查询

SELECT 
    DISTINCT 
        eva.errata_number, eva.headline, err.owner,err.rootCause, err.description FROM
            v_allevaluations eva, erratas err 
        WHERE
            eva.errata_number=err.errata_number and stateCombined = 'Affected'  && 
                         (Select releases_id in (SELECT release_id 
                        FROM subscription WHERE user_id = 83) OR folder in (SELECT folder
                        FROM subscribed_ns WHERE user_id = 83))

Zend框架查询:

$select = $db->select()
                        ->distinct()
                        ->from(array('eva'=>'v_allevaluations'),array('errata_number', 'headline'))
                        ->join(array('err'=>'erratas'), 'eva.errata_number= err.errata_number',array('owner','rootCause','description'))
                        ->where('stateCombined=?','Affected')  
                        ->where('select releases_id IN (?)', new Zend_Db_Expr("SELECT release_id FROM subscription WHERE user_id = ".$user_id.") OR folder in (SELECT folder 
                                                                                FROM subscribed_ns WHERE user_id = ".$user_id.")"));

当我打印 $select 时，它会打印

 SELECT 
    DISTINCT 
        `eva`.`errata_number`, `eva`.`headline`, `err`.`owner`, `err`.`rootCause`, `err`.`description`
FROM 
    `v_allevaluations` AS `eva` INNER JOIN `erratas` AS `err` ON eva.errata_number= err.errata_number
WHERE 
    (stateCombined='Affected') 
AND 
    (select releases_id IN (SELECT release_id FROM subscription WHERE user_id = 83) OR folder in (SELECT folder FROM subscribed_ns WHERE user_id = 83)))

它在 user_id = 83 之后添加了一个额外的括号。

您能告诉我出了什么问题吗？

Answer 1

我将子查询分配给一个变量并在查询中替换它

 $select = $db->select()
            ->distinct()
            ->from(array('eva'=>'v_allevaluations'),array('errata_number', 'headline'))
            ->join(array('err'=>'erratas'), 'eva.errata_number= err.errata_number',array('owner','rootCause','description'))
            ->where('stateCombined=?','Affected')  
            ->where('select releases_id IN (?)', new Zend_Db_Expr(" ".$get_subscribed_release_ids." ) OR folder in (".$get_subscribed_folders.""));

子查询:

$get_subscribed_release_ids = $db->select()
                                 ->from(array('subscription'), 'release_id')
                                ->where('user_id =?',"$user_id");

$get_subscribed_folders = $db->select()
                             ->from(array('subscribed_ns'), 'folder')
                             ->where('user_id =?',"$user_id");