Get Value And Than Get Data For Another Table In Cgridview

Yii 1.1 General Discussion
1

Hi all, I have table

Status

id

kode

status

Punish

id

id_punish <-- FK

nilai

berlaku_punish

I have query like this :


SELECT status.kode, status.status, m1.berlaku_punish, m1.nilai

FROM STATUS

LEFT JOIN punish m1 ON status.id = m1.id_status

AND m1.id = (

SELECT MAX( m2.id )

FROM punish m2

WHERE m2.id_status = status.id )

I was googling… for custom column cgridview with this condition, but not solved. :(

And my view for admin is : (this show data berlaku_punish and nilai but not with condition for max id)


<?php $this->widget('bootstrap.widgets.TbGridView', array(

	'id' => 'join-status-punish-grid',

	'dataProvider' => $model->search(),

        'type' => 'striped bordered condensed',

	'filter' => $model,

	'columns' => array(

		array(

                            'header' => 'Kode',

                            'name' => 'kode',

                            'htmlOptions'=>array('width'=>'100px'),

                    ),

		'status',

                'punishes.id_status', // i have relation in has_one

		'punishes.id_status',

		

		array(

			'class'=>'bootstrap.widgets.TbButtonColumn',

                        'template'=>'{tambah}  {detail}',

//                        'header'=>'T. Operasi',

                        'buttons'=>array(

                            'tambah' => array

                            (

                                'icon'=>'icon-plus-sign',

                                'url'=>'Yii::app()->createUrl("master/2/punish/create", array("id"=>$data->id))',

                            ),

                            

                            'detail' => array

                            (

                                'icon'=>'icon-time',

                                'url'=>'Yii::app()->createUrl("master/2/punish/create", array("id"=>$data->primaryKey))',

                            ),

                        ),

		),

	),

)); ?>

Any help for this???

2

CActiveDataProvider which is by default returned from search() method of ActiveRecord entity doesn’t support sub-queries (queries inside queries). But you can easily achieve what you want by using

http://www.yiiframework.com/doc/api/1.1/CSqlDataProvider

You can paste this SQL to the constructor of this object and after slight changes this should work.

Ask if you still have problems with that.

3

Yes, thanks

But i dont have to searching data.


        $connection=Yii::app()->db;

        $key="kode";

        $sql="SELECT status.id, status.kode, status.status, m1.berlaku_punish, m1.nilai

        FROM STATUS LEFT JOIN punish m1 ON status.id = m1.id_status

        AND m1.id = ( SELECT MAX( m2.id ) FROM punish m2 WHERE m2.id_status = status.id)";

        

        $rowcountsql = "SELECT COUNT(*) FROM status";

        $command = $connection->createCommand($rowcountsql);

        $count=$command->queryScalar(); // provide count for pagination

            // create data provider that works with CGridView

        $dataProvider=new CSqlDataProvider($sql, array(

        'keyField' => $key,

        'totalItemCount'=>$count,

        'pagination'=>array(

        'pageSize'=>500,

        ),

        ));

        $this->render('admin1',array('dataProvider'=>$dataProvider

        ));