php - 具有自定义值的 CActiveDataProvider

Question

在我的 Yii 项目中，我需要按一些自定义值搜索和排序数据。我有 'users' 表，我需要每个 User 实例都有 month_profit 属性，该属性应该结合 SQL 数据和我自己的大量计算。目前我的用户模型中有:

public $month_profit;

public function search($pageSize = 10, $defaultOrder = '`t`.`reg_date` DESC')
{
    $criteria = new CDbCriteria;
    $criteria->with = array('money');
    $requests_table = Requests::model()->tableName();
    $requests_count_sql = "(SELECT COUNT(*) FROM $requests_table rt WHERE rt.partner_id = t.id) ";

    $referrals_table = Referrals::model()->tableName();
    $referrals_count_sql = "(SELECT COUNT(*) FROM $referrals_table reft WHERE reft.user_id = t.id) ";
    $referrals_payed_sql = "(SELECT COUNT(*) FROM $referrals_table reft WHERE reft.user_id = t.id AND reft.status = 'Оплачено') ";
    //$month profit_sql = ???;

    $criteria->select = array(
        '*',
        $requests_count_sql . "as requests_count",
        $referrals_count_sql . "as referrals_count",
        $referrals_payed_sql . "as referrals_payed_count",
        $month_profit_sql . "as month_profit",
    );

    $criteria->compare($requests_count_sql, $this->requests_count);
    $criteria->compare($referrals_count_sql, $this->referrals_count);
    $criteria->compare($referrals_payed_sql, $this->referrals_payed_count);
    $criteria->compare($month_profit_sql, $this->month_profit);

    $criteria->compare('t.id', $this->id);
    $criteria->compare('t.reg_date', $this->reg_date, true);
    $criteria->compare('username', $this->username, true);
    $criteria->compare('password', $this->password, true);
    $criteria->compare('site', $this->site, true);
    $criteria->compare('status', $this->status, true);

    return new CActiveDataProvider(get_class($this), array(
        'criteria' => $criteria,
        'pagination' => array( 'pageSize' => $pageSize ),
        'sort' => array(
            'defaultOrder' => $defaultOrder,
            'attributes' => array(
                'id' => array(
                    'asc' => '`t`.`id` ASC',
                    'desc' => '`t`.`id` DESC',
                ),
                'email' => array(
                    'asc' => '`t`.`email` ASC',
                    'desc' => '`t`.`email` DESC',
                ),
                'requests_count' => array(
                    'asc' => 'requests_count ASC',
                    'desc' => 'requests_count DESC',
                ),
                'referrals_count' => array(
                    'asc' => 'referrals_count ASC',
                    'desc' => 'referrals_count DESC',
                ),
                'referrals_payed_count' => array(
                    'asc' => 'referrals_payed_count ASC',
                    'desc' => 'referrals_payed_count DESC',
                ),
                'money' => array(
                    'asc' => 'money.profit',
                    'desc' => 'money.profit DESC',
                ),
                'fullProfit' => array(
                    'asc' => 'money.full_profit',
                    'desc' => 'money.full_profit DESC',
                ),
                '*',
            ),
        )
    ));
}

例如我在我的用户模型中有一个关系:

public function relations()
{
    return array(
        'clients' => array( self::HAS_MANY, 'Referrals', 'user_id'),

假设我的 month_profit 将等于:过去 30 天内注册的用户 clients 的数量 * 150。我需要以某种方式将此数据传递给 search( ) 并创建 CDbCriteria 以按 month_profit 对用户进行排序。这是真的吗？ :) 我应该创建另一个函数来计算所有内容然后传递给 search() 吗？到目前为止，我所有的尝试都以失败告终。

Answer 1

我想您需要以下内容，基于本指南 http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview

首先在您的用户模型中对关系进行统计查询:

'month_profit' => array(self::STAT, 'User', 'id', 'select'=>'COUNT(*) * 150', 'condition'=>'DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= reg_date'),

然后在规则的搜索场景中将“month_profit”属性标记为安全。

public function rules() {
    return array(

        ...

        array('username, ... , month_profit', 'safe', 'on' => 'search' ),   
    );
}

在 User search() 方法中需要的地方添加所有这些:

$user_table = User::model()->tableName();
$month_profit_sql = "(SELECT COUNT(*) FROM user_table WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= reg_date)";

$criteria->select = array(
    '*',
    $requests_count_sql . "as requests_count",
    $referrals_count_sql . "as referrals_count",
    $referrals_payed_sql . "as referrals_payed_count",
    $month_profit_sql . "as month_profit",
);

...
$criteria->compare($month_profit_sql, $this->month_profit);

return new CActiveDataProvider(get_class($this), array(
    'criteria' => $criteria,
    'pagination' => array( 'pageSize' => $pageSize ),
    'sort' => array(
        'defaultOrder' => $defaultOrder,
        'attributes' => array(
            'id' => array(
                'asc' => '`t`.`id` ASC',
                'desc' => '`t`.`id` DESC',
            ),

            ...

            'month_profit' => array(
                'asc' => 'month_profit ASC',
                'desc' => 'month_profit DESC',
            ),

            '*',
        ),
    )
));

最后，修改你的网格:

$this->widget('zii.widgets.grid.CGridView', array(
    'dataProvider' => $model->search(),
    'filter' => $model,
    'columns' => array(
        'username',

        ... ,

        'month_profit',
        array(
            'class'=>'CButtonColumn',
        ),
    ),
));

请让我知道这是否有效。

编辑如果这不起作用，请尝试不使用统计查询。