Sort and search column when I'm querying with

Question

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.

Answer 1

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');

Answer 2

Setup a grid view with a data provider and filter model.

In your view:

<?= GridView::widget([
    'dataProvider'=> $dataProvider,
    'filterModel' => $searchModel,
    'columns' => [
        'parties_partyname', 
        'address', 
        'parties_district', 
        'name_manager', 
        'transport', 
        'dlno', 
        'instruction', 
        'con', 
        'district',
        'sale',
        'sell',
        'collection'
    ]
]); ?>

In your controller (replace index with the name of your view file):

$searchModel = new \frontend\modules\districtreport\models\PartiesSearch();
$dataProvider = $searchModel->search(Yii::$app->request->queryParams);

return $this->render('index', [
    'searchModel' => $searchModel,
    'dataProvider' => $dataProvider,
]);

Try removing $dataProvider->setSort() from your search model.

Also, in your detail of your gridview you are using the same variable name for both search models and data providers. They are two separate models so the variables should not be the same.

<?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;

/**
 * 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);

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

        $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;
        }

        $query->andFilterWhere([
            'party_id' => $this->party_id,
            'parties_partyname' => $this->parties_partyname,
            'address' => $this->address,
            'parties_district' => $this->parties_district,
            'name_manager' => $this->name_manager,
            'transport' => $this->transport,
            'dlno' => $this->dlno,
            'instruction' => $this->instruction,
            'con' => $this->con,
            'sell' => $this->sell,
            'collection' => $this->collection,
            'district' => $this->district,
        ]);

        return $dataProvider;
    }
}

Simplified Search Model that works for me:

<?php

namespace common\models\event;

use Yii;
use yii\base\Model;
use yii\data\ActiveDataProvider;
use common\models\Model;

class ModelSearch extends Model
{
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['id', 'name'], '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)
    {
        $query = Model::find()->indexBy('id');

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

        $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;
        }

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

        return $dataProvider;
    }
}

Answer 3

According to yii2's docs you're using an ActiveDataProvider with an ActiveRecord inside. Here's the example docs page for your case.

I think what you want to do is to return $dataProvider->getModels() rather than just return $dataProvider. This will return presumably all rows, since I don't see a pagination mechanism (there could be a default pagination count).

For sorting, you could try sorting on the $query rather than the $dataProvider. Try something like:

$query->orderBy([
    'tsell.sell' => SORT_ASC
]);