sql query to CActiveDataProvider query not succesfull help please

Yii 1.1 General Discussion
1

What will be the version of this query in CActiveDataProvider


 $sql = "select z1.id, z1.type_id,z1.columnh,z1.gen ,r1.columnr_name, mt1.columnmt_name,  q1.columnqu_name, jj1.columnquj_name,cd1.current_columnz1


  from xyzmaaster as z1 

  JOIN columnr_master as r1 on z1.columnr = r1.columnr_id

      JOIN columnmt_master as mt1 on z1.columnmt = mt1.columnmt_id

     JOIN columnqu_master as q1 on z1.columnqu = q1.columnqu_id

      JOIN columnquj_master as jj1 on z1.jj = jj1.columnquj_id

       JOIN current_columnz1_master as cd1 on z1.current_columnz1 = cd1.current_columnz1_id

      

        

 where z1.dob between '".$agelow."' and '".$agehigh."'

  

 and z1.gen ='".$gen."' 

 and r1.columnr_id ='".$columnr."'  and mt1.columnmt_id ='".$columnmt."'   

  and z1.xyz ='".$xyz."'    

 

   and z1.columnqu = jj1.columnqu_id 

     and z1.current_columnz1 = cd1.current_columnz1_id

       ";

 

			 $rows = Yii::app()->db->createCommand($sql)->queryAll();

I have tried around ten versions to convert it to something like this to get it in


<?php 

 $this->widget('zii.widgets.CListView', array(

'dataProvider'=>$dataProvider,

'itemView'=>'_results',




	//'itemView'=>'_results',

));

but have not succeded

Query from class reference


$dataProvider=new CActiveDataProvider('Post', array(

    'criteria'=>array(

        'condition'=>'status=1',

        'order'=>'create_time DESC',

        'with'=>array('author'),

    ),

    'pagination'=>array(

        'pageSize'=>20,

    ),

));

Pleeeeeaseee help me with this query

2

Any Ideas?

3

i would create a sql VIEW and then create a model on top of that view (in which you just need to define:

public function primaryKey() { return ‘id’; }

that’s it

deal with all the complexities of your queries within that sql view.

and just use regular cactivedataprovider then on that view.

e.g.




CREATE VIEW xyzabc AS

SELECT z1.*,r1.whatever X,mtl.whatever Y,... from xyzmaaster as z1 

  JOIN columnr_master as r1 on z1.columnr = r1.columnr_id

      JOIN columnmt_master as mt1 on z1.columnmt = mt1.columnmt_id

     JOIN columnqu_master as q1 on z1.columnqu = q1.columnqu_id

      JOIN columnquj_master as jj1 on z1.jj = jj1.columnquj_id

       JOIN current_columnz1_master as cd1 on z1.current_columnz1 = cd1.current_columnz1_id;