带有连接，分组依据和复杂where子句的yii cdbcriteria

itnelo 发表于 Dev

Itnelo

我有一个具有大数据记录过滤器的yii驱动的应用程序，其中包含大而复杂的逻辑。我编写了正确的SQL查询并对其进行了测试。

SELECT
      c.`customer_id`       AS 'customer_id'
    , c.`status_id`         AS 'status_id'
    , c.`customer_name`     AS 'customer_name'
    , BIT_OR(r2c.`role_id`) AS 'roles_mask'
FROM
    `customers` AS c
LEFT JOIN
    `roles2customers` AS r2c
ON
    c.`customer_id` = r2c.`customer_id`
WHERE
        IF(   :status_mask = 0
            , c.`status_id` = 0
            , c.`status_id` & :status_mask <> 0
        )
    AND c.`customer_id` <> :customer_id
    AND c.`customer_name` LIKE concat(:customer_name, '%')
GROUP BY
    c.`customer_id`
HAVING
    IF(   :role_mask = 0
        , BIT_OR(r2c.`role_id`) = 0
        , BIT_OR(r2c.`role_id`) & :role_mask <> 0
    )
ORDER BY
    c.`customer_name` ASC
LIMIT
    :offset, :count

此查询可使用简单的ActiveRecord方法findAllBySql正常工作：

$filtered_records = ModelClass::model()->findAllBySql( $sql, $params );

但是我的问题是使用CDbCriteria编写应用程序后端。因此，我需要构建正确的条件对象并将其传递给findAll方法：

$criteria = new CDbCriteria();
$filtered_records = ModelClass::model()->findAll( $criteria );

我打开CDBCriteria参考并看到了许多简单的示例，但是..在我的情况下，如何通过条件指定JOIN和HAVING操作？

我的代码：

$criteria = new CDbCriteria();
$criteria->condition = "
  c.customer_id AS 'customer_id',
  c.status_id AS 'status_id',
  c.customer_name AS 'customer_name',
  BIT_OR(r2c.role_id) AS 'roles_mask'
";
$criteria->offset = $offset;
$criteria->limit = $limit;
// how can I make valid r2c relation and group by via criteria? 
// and all another sql parts...

阿尔特

的GROUP BY和HAVING部分在相同的方式，直接地连接condition，offset和limit：

$criteria->group ='t.customer_id'
$criteria->having='IF(   :role_mask = 0
    , BIT_OR(r2c.`role_id`) = 0
    , BIT_OR(r2c.`role_id`) & :role_mask <> 0
)'

该LEFT JOIN是一个比较复杂的，但可以做到用with：

$criteria->with=array(
  'roles2customers'=>array(
    'alias'=>'r2c',
    'joinType'=>'LEFT JOIN',
    'condition'=>'
          IF(   :status_mask = 0
            , t.`status_id` = 0
            , t.`status_id` & :status_mask <> 0
          )
      AND t.`customer_id` <> :customer_id
      AND t.`customer_name` LIKE concat(:customer_name, '%')
    ',
  )
)

这依赖于你设置的基本关系roles2customers中ModelClass。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。