gridview使用hasmany关联获取groupby数据分类结果,并能做排序及搜索 [ 2.0 版本 ]

数据表结构

其中一个用户可以购买多个产品，由username关联buyer

members

userid 用户id
username 用户名
mobile 手机号 对手机号进行groupby
truename 真名

trades

itemid 自增主键
buyer 买家
total 购买数量
amount 总额
status 交易状态

使用gii生成所需的模型Trades,Members;创建members的curd操作gridview

在Members模型里写入关联

class Members extends \app\models\Members
{
    public function getTrades()
    {
        return $this->hasMany(Trades::className(), ['buyer'=>'username']);
    }
}

在MembersSearch里写入查询语句及对应的属性值

class BigMembersSearch extends MembersSearch
{
    public $tradesCount;
    public $tradesTotal;
    public $tradesAmount;

    public $amountStart;
    public $amountEnd;
    // ...(其他需要自定义的属性，为gridview列的结果)

    public function rules()
    {
        $data = parent::rules();
        $data[] = [['tradesCount', 'tradesTotal', 'tradesAmount', 'amountStart', 'amountEnd'], 'double'];
        return $data;// 加入对应规则，否则不能进行索引及排序
    }

    public function search($params)
    {
        // 查询方法详细
        $query = self::find();
        $query->select([
            self::tableName().".userid",
            self::tableName().".username",
            self::tableName().".truename",
            self::tableName().".mobile",
            "COUNT(".Trades::tableName().".itemid) AS tradesCount",
            "SUM(".Trades::tableName().".total) AS tradesTotal",
            "SUM(".Trades::tableName().".amount) AS tradesAmount",
        ])->joinWith('trades')->groupBy([
            self::tableName().".mobile",
        ])->where([
            "LENGTH(".self::tableName().".mobile)"=>11,
            Trades::tableName().".status"=>[2, 3, 4],
        ]);

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
        ]);

        $dataProvider->sort->attributes['tradesCount'] = [
            'asc' => ["tradesCount" => SORT_ASC],
            'desc' => ["tradesCount" => SORT_DESC],
            'label' => 'Trades Count',
        ];
        $dataProvider->sort->attributes['tradesTotal'] = [
            'asc' => ["tradesTotal" => SORT_ASC],
            'desc' => ["tradesTotal" => SORT_DESC],
            'label' => 'Trades Total',
        ];
        $dataProvider->sort->attributes['tradesAmount'] = [
            'asc' => ["tradesAmount" => SORT_ASC],
            'desc' => ["tradesAmount" => SORT_DESC],
            'label' => 'Trades Amount',
        ];

        $this->load($params);

        if (!$this->validate()) {
            return $dataProvider;
        }

        $query->andFilterWhere([
            'userid' => $this->userid,
        ]);

        $query->andFilterWhere(['like', 'username', $this->username])
            ->andFilterWhere(['like', 'truename', $this->truename])
            ->andFilterWhere(['like', 'mobile', $this->mobile])

        // 对结果集需要用having处理
        $query->having(['>', 'tradesCount', 0]);
        $query->andHaving(['>', 'tradesTotal', 0]);
        $query->andHaving(['>', 'tradesAmount', 0]);
        if($this->tradesCount){
            $query->andHaving(['tradesCount'=>$this->tradesCount]);
        }
        if($this->tradesTotal){
            $query->andHaving(['tradesTotal'=>$this->tradesTotal]);
        }
        if($this->tradesAmount){
            $this->tradesAmount = (double)$this->tradesAmount;
            $query->andHaving(['tradesAmount'=>$this->tradesAmount]);
        }
        if($this->amountStart){
            $this->amountStart = (double)$this->amountStart;
            $query->andHaving(['>=', 'tradesAmount', $this->amountStart]);
        }
        if($this->amountEnd){
            $this->amountEnd = (double)$this->amountEnd;
            $query->andHaving(['<=', 'tradesAmount', $this->amountEnd]);
        }

        return $dataProvider;
    }
}

在视图中添加需要的列

$columns = [
        [
            'class' => \kartik\grid\SerialColumn::className(),
        ],

        'userid',
        'username',
        'truename',
        'mobile',
        [
            'attribute'=>'tradesCount',
            'format'=>'integer',
            'hAlign'=>'right',
            'width'=>'100px',
            'pageSummary'=>true
        ],
        [
            'attribute'=>'tradesTotal',
            'format'=>'integer',
            'hAlign'=>'right',
            'width'=>'100px',
            'pageSummary'=>true
        ],
        [
            'attribute'=>'tradesAmount',
            'format'=>'integer',
            'hAlign'=>'right',
            'width'=>'100px',
            'pageSummary'=>true,
            'filter'=>Html::input('text', 'MembersSearch[amountStart]', $searchModel->amountStart, ['class'=>'form-control col-lg-6']).Html::input('text', 'MembersSearch[amountEnd]', $searchModel->amountEnd, ['class'=>'form-control col-lg-6']),
        ],

        [
            'class' => \kartik\grid\ActionColumn::className(),
        ],
]

结果预览