当我在 yii2 中使用 findbysql 查询时对列进行排序和搜索 [英] Sort and search column when I'm querying with findbysql in yii2
本文介绍了当我在 yii2 中使用 findbysql 查询时对列进行排序和搜索的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在搜索四个表并加入它们并得到我想要的输出.但无法对输出进行排序或过滤.请告诉我如何按地区或销售范围或收集范围搜索它.PartySearch 模型是 -
db;/*** PartySearch 代表关于`frontend\modules\districtreport\models\Parties` 的搜索表单背后的模型.*/类 PartySearch 扩展了 Party{/*** @inheritdoc*/公共函数规则(){返回 [[['party_id'], '整数'],[['party_partyname', 'address', 'parties_district', 'name_manager', 'transport', 'dlno', 'instruction', 'con', 'district','sale','sell','collection']， '安全的']，];}/*** @inheritdoc*/公共功能场景(){//绕过父类中的scenario()实现返回模型::场景();}/*** 创建应用了搜索查询的数据提供者实例** @param 数组 $params** @return ActiveDataProvider*/公共函数搜索($params){$sql = '选择tsell.district 作为区，tsell.totalsale 作为销售，合并(tcollection.collection,0)作为集合从(选择区，合并(sell.sale,0) 作为总销售额从`地区`左连接(选择当事人_区，账单日，sum(billamount) 作为销售从`账单`在 bills.bills_partyname =party.parties_partyname 上离开加入派对按party_district分组)作为出售在 sell.parties_district = Districts.district) 上作为 tsell左连接(选择当事人_区，支付日期，COALESCE(sum(payment_amount),0) 作为集合从`支付`在 Payment.payment_partyname = party.parties_partyname 上离开加入派对按party_district分组)作为tcollection在 tsell.district = tcollection.parties_district';$query = Party::findBySql($sql);//添加应始终适用于此处的条件$dataProvider = new ActiveDataProvider(['查询' =>$查询，//'排序'=>['defaultOrder' =>['区'=>SORT_DESC]]]);$dataProvider->setSort(['属性' =>[卖"=>['asc' =>['卖' =>SORT_ASC],'desc' =>['卖' =>SORT_DESC],'标签' =>'卖'],'集合' =>['asc' =>['集合' =>SORT_ASC],'desc' =>['集合' =>SORT_DESC],'标签' =>'收藏'],'区' =>['asc' =>['tsell.district' =>SORT_ASC],'desc' =>['tsell.district' =>SORT_DESC],'标签' =>'区']]]);$this->load($params);如果 (!$this->validate()) {//如果您不想在验证失败时返回任何记录，请取消注释以下行//$query->where('0=1');返回 $dataProvider;}//网格过滤条件$query->andFilterWhere(['party_id' =>$this->party_id,]);$query->andFilterWhere(['like', 'parties_partyname', $this->parties_partyname])->andFilterWhere(['like', 'address', $this->address])->andFilterWhere(['like', 'parties_district', $this->parties_district])->andFilterWhere(['like', 'name_manager', $this->name_manager])->andFilterWhere(['like', 'transport', $this->transport])->andFilterWhere(['like', 'dlno', $this->dlno])->andFilterWhere(['like', 'instruction', $this->instruction])->andFilterWhere(['like', 'con', $this->con])->andFilterWhere(['like', 'sell', $this->sell])->andFilterWhere(['like', 'collection', $this->collection])->andFilterWhere(['like', 'district', $this->district]);返回 $dataProvider;}}派对模式
60],[['地址', '指令'], '字符串', 'max' =>100],[['party_district'], 'string', 'max' =>20],[['name_manager', 'transport', 'dlno'], 'string', 'max' =>30],[['con'], 'string', 'max' =>10],[['party_partyname'], 'unique'],[['name_manager'], 'exist', 'skipOnError' =>真，'targetClass' =>Managers::className(), 'targetAttribute' =>['name_manager' =>'manager_managername']],[['con'], 'exist', 'skipOnError' =>真，'targetClass' =>Console::className(), 'targetAttribute' =>['con' =>'安慰']]，[['party_district'], 'exist', 'skipOnError' =>真，'targetClass' =>Districts::className(), 'targetAttribute' =>['party_district' =>'区']]，];}/*** @inheritdoc*/公共函数attributeLabels(){返回 ['party_id' =>'派对ID','party_partyname' =>'Parties Partyname','地址' =>'地址'，'party_district' =>'派对区','name_manager' =>'名称管理器','运输' =>'运输'，'dlno' =>'Dlno','说明' =>'操作说明'，'con' =>'康',];}公共函数 getDistricts(){返回 $this->hasOne(Districts::className(), ['district' => 'parties_district']);}公共函数 getBills(){返回 $this->hasMany(Bills::className(), ['bills_partyname' => 'parties_partyname']);}公共函数 getPayment(){return $this->hasMany(Payment::className(), ['payment_partyname' => 'party_partyname']);}}index.php
title = 'Parties';$this->params['breadcrumbs'][] = $this->title;?><div class="party-index"><h1><?= Html::encode($this->title) ?></h1><?php//echo $this->render('_search', ['model' => $searchModel]);?><!-- <p><?= Html::a('Create Party', ['create'], ['class' => 'btn btn-success']) ?></p>--><!-- <div class="custom-filter">日期范围:<输入名称=开始"/><输入名称=结束"/>--><?= GridView::widget(['数据提供者' =>$数据提供者，'过滤器模型' =>$搜索模型，'出口' =>错误的，'列' =>[[//['类' =>'yii\grid\SerialColumn'],'类' =>'kartik\grid\ExpandRowColumn','价值' =>函数($model，$key，$index，$column){返回 GridView::ROW_COLLAPSED;},'细节' =>函数($model，$key，$index，$column){$searchModel = new ExpartiesSearch();$searchModel->地区 = $model-> 地区；$dataProvider = $searchModel->search(Yii::$app->request->queryParams);return Yii::$app->controller->renderPartial('_exparties', ['搜索模型' =>$搜索模型，'数据提供者' =>$数据提供者，]);},],//['类' =>'yii\grid\SerialColumn'],'区'，//[//'属性' =>'日期'，//'值' =>'tsell.date',//'过滤器' =>\yii\jui\DatePicker::widget(['language' => 'ru', 'dateFormat' => 'dd-MM-yyyy']),//'格式' =>'html',//],'卖'，'收藏'，//['类' =>'yii\grid\ActionColumn'],],]);?>
在这张图片中我们可以看到，虽然有卖出排序，但实际上并没有对数据进行排序.在图 2 中我们可以看到，尽管数据 assam 被传递到了下一层 kartik expandrow，但它并没有过滤.
解决方案
根据 http://www.yiiframework.com/doc-2.0/yii-db-activerecord.html#findBySql%28 无法对带有 findbysql 的代码进行排序或过滤.但是，它可以通过构建查询以下页面进行排序 - http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html查询将类似于 -
$subQuery1 = (new Query())->select(['parties_district','billdate','sum(billamount) as sale'])->from ('party')->;join('LEFT JOIN','bills','bills.bills_partyname = party.parties_partyname')->groupby('parties_district')->where('billdate != "NULL"');$subQuery2 = (new Query())->select(['district','coalesce(sell.sale,0) as totalsale'])->from('districts')->leftJoin(['sell' => $subQuery1],'sell.parties_district = Districts.district');$subQuery3 = (new Query())->select(['parties_district','payment_date','COALESCE(sum(payment_amount),0) as collection'])->from('payment')->join('LEFT JOIN','party','payment.payment_partyname =party.parties_partyname')->groupby('party_district');$query = (new Query())->select(['tsell.district as district','tsell.totalsale as sell','coalesce(tcollection.collection,0) as collection'])->from(['tsell'=> $subQuery2])->leftJoin(['tcollection' => $subQuery3],'tcollection.parties_district = tsell.district');I'm searching four tables and joined them and got the output I want. But unable to sort or filter the output. Please tell me how I can search it by district or a sell range or collection range. PartiesSearch model is -
<?php
namespace frontend\modules\districtreport\models;
use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use frontend\modules\districtreport\models\Parties;
use frontend\modules\districtreport\models\Bills;
use frontend\modules\districtreport\models\Payment;
use yii\db\Query;
use yii\db\Command;
$query = \Yii::$app->db;
/**
* PartiesSearch represents the model behind the search form about `frontend\modules\districtreport\models\Parties`.
*/
class PartiesSearch extends Parties
{
/**
* @inheritdoc
*/
public function rules()
{
return [
[['party_id'], 'integer'],
[['parties_partyname', 'address', 'parties_district', 'name_manager', 'transport', 'dlno', 'instruction', 'con', 'district','sale','sell','collection'], 'safe'],
];
}
/**
* @inheritdoc
*/
public function scenarios()
{
// bypass scenarios() implementation in the parent class
return Model::scenarios();
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$sql = 'select
tsell.district as district,
tsell.totalsale as sell,
coalesce(tcollection.collection,0) as collection
from
(SELECT
district,
coalesce(sell.sale,0) as totalsale
FROM `districts`
left join
(SELECT
parties_district,
billdate,
sum(billamount) as sale
FROM `bills`
left join parties on bills.bills_partyname = parties.parties_partyname
group by parties_district) as sell
on sell.parties_district = districts.district) as tsell
left join
(SELECT
parties_district,
payment_date,
COALESCE(sum(payment_amount),0) as collection
FROM `payment`
left join parties on payment.payment_partyname = parties.parties_partyname
group by parties_district) as tcollection
on tsell.district = tcollection.parties_district';
$query = Parties::findBySql($sql);
// add conditions that should always apply here
$dataProvider = new ActiveDataProvider([
'query' => $query,
//'sort'=> ['defaultOrder' => ['district'=>SORT_DESC]]
]);
$dataProvider->setSort([
'attributes' => [
'sell' => [
'asc' => ['sell' => SORT_ASC],
'desc' => ['sell' => SORT_DESC],
'label' => 'Sell'
],
'collection' => [
'asc' => ['collection' => SORT_ASC],
'desc' => ['collection' => SORT_DESC],
'label' => 'Collection'
],
'district' => [
'asc' => ['tsell.district' => SORT_ASC],
'desc' => ['tsell.district' => SORT_DESC],
'label' => 'District'
]
]
]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
// grid filtering conditions
$query->andFilterWhere([
'party_id' => $this->party_id,
]);
$query->andFilterWhere(['like', 'parties_partyname', $this->parties_partyname])
->andFilterWhere(['like', 'address', $this->address])
->andFilterWhere(['like', 'parties_district', $this->parties_district])
->andFilterWhere(['like', 'name_manager', $this->name_manager])
->andFilterWhere(['like', 'transport', $this->transport])
->andFilterWhere(['like', 'dlno', $this->dlno])
->andFilterWhere(['like', 'instruction', $this->instruction])
->andFilterWhere(['like', 'con', $this->con])
->andFilterWhere(['like', 'sell', $this->sell])
->andFilterWhere(['like', 'collection', $this->collection])
->andFilterWhere(['like', 'district', $this->district]);
return $dataProvider;
}
}
Parties Model
<?php
namespace frontend\modules\districtreport\models;
use Yii;
/**
* This is the model class for table "parties".
*
* @property integer $party_id
* @property string $parties_partyname
* @property string $address
* @property string $parties_district
* @property string $name_manager
* @property string $transport
* @property string $dlno
* @property string $instruction
* @property string $con
*/
class Parties extends \yii\db\ActiveRecord
{
public $sale;
public $district;
public $sell;
public $collection;
public $bills;
public $partyname;
public $billdate;
//public $sale;
/**
* @inheritdoc
*/
public static function tableName()
{
return 'parties';
}
/**
* @inheritdoc
*/
public function rules()
{
return [
[['parties_partyname', 'parties_district', 'name_manager'], 'required'],
[['parties_partyname'], 'string', 'max' => 60],
[['address', 'instruction'], 'string', 'max' => 100],
[['parties_district'], 'string', 'max' => 20],
[['name_manager', 'transport', 'dlno'], 'string', 'max' => 30],
[['con'], 'string', 'max' => 10],
[['parties_partyname'], 'unique'],
[['name_manager'], 'exist', 'skipOnError' => true, 'targetClass' => Managers::className(), 'targetAttribute' => ['name_manager' => 'manager_managername']],
[['con'], 'exist', 'skipOnError' => true, 'targetClass' => Console::className(), 'targetAttribute' => ['con' => 'console']],
[['parties_district'], 'exist', 'skipOnError' => true, 'targetClass' => Districts::className(), 'targetAttribute' => ['parties_district' => 'district']],
];
}
/**
* @inheritdoc
*/
public function attributeLabels()
{
return [
'party_id' => 'Party ID',
'parties_partyname' => 'Parties Partyname',
'address' => 'Address',
'parties_district' => 'Parties District',
'name_manager' => 'Name Manager',
'transport' => 'Transport',
'dlno' => 'Dlno',
'instruction' => 'Instruction',
'con' => 'Con',
];
}
public function getDistricts()
{
return $this->hasOne(Districts::className(), ['district' => 'parties_district']);
}
public function getBills()
{
return $this->hasMany(Bills::className(), ['bills_partyname' => 'parties_partyname']);
}
public function getPayment()
{
return $this->hasMany(Payment::className(), ['payment_partyname' => 'parties_partyname']);
}
}
index.php
<?php
use yii\helpers\Html;
use kartik\grid\GridView;
//use kartik\widgets\DatePicker;
use kartik\daterange\DateRangePicker;
use kartik\form\ActiveForm;
use dosamigos\datepicker\DatePicker;
use frontend\modules\districtreport\models\ExpartiesSearch;
/* @var $this yii\web\View */
/* @var $searchModel frontend\modules\districtreport\models\PartiesSearch */
/* @var $dataProvider yii\data\ActiveDataProvider */
$this->title = 'Parties';
$this->params['breadcrumbs'][] = $this->title;
?>
<div class="parties-index">
<h1><?= Html::encode($this->title) ?></h1>
<?php // echo $this->render('_search', ['model' => $searchModel]); ?>
<!-- <p>
<?= Html::a('Create Parties', ['create'], ['class' => 'btn btn-success']) ?>
</p> -->
<!-- <div class="custom-filter">
Date range:
<input name="start" />
<input name="end" />
</div> -->
<?= GridView::widget([
'dataProvider' => $dataProvider,
'filterModel' => $searchModel,
'export' => false,
'columns' => [
[
//['class' => 'yii\grid\SerialColumn'],
'class' => 'kartik\grid\ExpandRowColumn',
'value' => function($model, $key, $index, $column){
return GridView::ROW_COLLAPSED;
},
'detail' => function($model, $key, $index, $column){
$searchModel = new ExpartiesSearch();
$searchModel-> district = $model->district;
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);
return Yii::$app->controller->renderPartial('_exparties', [
'searchModel' => $searchModel,
'dataProvider' => $dataProvider,
]);
},
],
//['class' => 'yii\grid\SerialColumn'],
'district',
// [
// 'attribute' => 'date',
// 'value' => 'tsell.date',
// 'filter' => \yii\jui\DatePicker::widget(['language' => 'ru', 'dateFormat' => 'dd-MM-yyyy']),
// 'format' => 'html',
// ],
'sell',
'collection',
//['class' => 'yii\grid\ActionColumn'],
],
]); ?>
</div>
In this picture we can see that though the sell sort is there but it's not sorting the data actually. In picture 2 we can see that though the data assam is passed to the next level of kartik expandrow, it's not filtering.
解决方案
According to http://www.yiiframework.com/doc-2.0/yii-db-activerecord.html#findBySql%28 code with findbysql cannot be sorted or filtered. However, it can be sorderd by building query following page - http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html The query will be like -
$subQuery1 = (new Query())->select(['parties_district','billdate','sum(billamount) as sale'])->from ('parties')->join('LEFT JOIN','bills','bills.bills_partyname = parties.parties_partyname')->groupby('parties_district')->where('billdate != "NULL"');
$subQuery2 = (new Query())->select(['district','coalesce(sell.sale,0) as totalsale'])->from('districts')->leftJoin(['sell' => $subQuery1],'sell.parties_district = districts.district');
$subQuery3 = (new Query())->select(['parties_district','payment_date','COALESCE(sum(payment_amount),0) as collection'])->from('payment')->join('LEFT JOIN','parties','payment.payment_partyname = parties.parties_partyname')->groupby('parties_district');
$query = (new Query())->select(['tsell.district as district','tsell.totalsale as sell','coalesce(tcollection.collection,0) as collection'])->from(['tsell'=> $subQuery2])->leftJoin(['tcollection' => $subQuery3],'tcollection.parties_district = tsell.district');
这篇关于当我在 yii2 中使用 findbysql 查询时对列进行排序和搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
