Sql Statement using AR


(Sbondi) #1

Hello everyone,

I’m starting use Yii from this Monday

I need a help to understand to create a AR statements for a complex query.

Actually with AR I able to do simple query but now I need to do the follow:

("SELECT COUNT(*) as Totale FROM $tbl_name

where StartDate < ‘2010-02-01’ AND StartDate >=‘2010-01-01’ AND class=‘A’

How can to do with AR?

Thank you for the help


(Matteo Falsitta) #2

You have to add a property Totale in the activerecord class:




class TableName extendx CactiveRecord

{

  public $totale;




then in the controller (or wherever you have to do it):




$criteria= new CDbCriteria;

$criteria->condition= "StartDate < '2010-02-01' AND StartDate >='2010-01-01' AND class='A' ";

$criteria->select=" COUNT(*) as Totale";

CActiveRecord::model($tableName)->find($criteria);




(Ricardo Grana) #3

This way you don’t need to add variable ‘totale’:




$criteria= new CDbCriteria;

$criteria->condition= "StartDate < '2010-02-01' AND StartDate >='2010-01-01' AND class='A' ";

$count = classname::model()->count($criteria);




(Mh) #4

And as one liner:


$count=classname::model()->count("StartDate < '2010-02-01' AND StartDate >='2010-01-01' AND class='A'");


(Sbondi) #5

thank you Zaccaria.

Now I don’t know ho to view the results of query:

I done:

class TableName extendx CactiveRecord

{

public $totale;


public function searchReport1()

{





	


    &#036;criteria1= new CDbCriteria;


    &#036;criteria1-&gt;condition= &quot;prj_start_date &lt; '2010-03-01' AND prj_start_date &gt;'2009-01-01' AND device_category_id='0' &quot;;


    


    &#036;criteria1-&gt;select=&quot; COUNT(*) as totale&quot;;








    return new CActiveDataProvider(get_class(&#036;this), array(


		'criteria'=&gt;&#036;criteria1,


	));


}

on the controller I wrote a new funtion:

public function actionReport1()

{


	&#036;model=new Projects('searchReport1');


	&#036;model-&gt;unsetAttributes();  // clear any default values


	if(isset(&#036;_GET['Projects']))


		&#036;model-&gt;attributes=&#036;_GET['Projects'];





	&#036;this-&gt;render('report1',array(


		'model'=&gt;&#036;model,


	));


}

and on the view I create a file with the code:

<?php $this->widget(‘zii.widgets.grid.CGridView’, array(

'id'=&gt;'projects-grid',


'dataProvider'=&gt;&#036;model-&gt;searchReport1(),


'filter'=&gt;&#036;model,


'columns'=&gt;array(





   'device_category_id',


   array(


    'header' =&gt; 'Totale',


    'value' =&gt; '&#036;totale',





   ),


	/*'prj_start_date',


	'prj_end_date', */








	array(


		'class'=&gt;'CButtonColumn',


	),


),

)); ?>

but I unable to see the results of count,

can you help me?


(Maurizio Domba Cerin) #6

Your select gives just only one result… a count for the condition you set… so it’s a bit unclear why you want to display that in a CGridView…


(Matteo Falsitta) #7

change the dataprovider like that:




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

'filter'=>$model,

'columns'=>array(


'device_category_id',

'totale',




),




The query you wrote will extract only the row for id 0, maybe you want to display all rows by using group by:




$criteria= new CDbCriteria;

$criteria->select="device_category_id COUNT(*) as Totale";

$criteria->condition= "StartDate < '2010-02-01' AND StartDate >='2010-01-01' AND class='A' ";

$criteria->group_by="device_category_id";

CActiveRecord::model($tableName)->find($criteria);




(Sbondi) #8

Hi,

thank you I followed the suggestion but the count is not displayed.

I’m trying to view If I able to display one risult and then I want to extend the query to all rows

but now I’m unable to see only record


(Sbondi) #9

Hi All,

I resolved my issue.

on the model I used :

$criteria1= new CDbCriteria;

$criteria1->select="device_category_id, COUNT( device_category_id) as totale ";

$criteria1->condition= " prj_start_date <= ‘2010-12-01’ AND prj_start_date >=‘2010-11-01’ AND device_category_id=‘1’";

$criteria1->group ="device_category_id";

$totale = Projects::model()->findAll($criteria1);

foreach ($totale as $tot) echo $tot->totale.$tot->device_category_id;

return new CActiveDataProvider(get_class($this), array(

‘criteria’=>$criteria1, ));


on the controller my actionreport contains:

&#036;model=new Projects('searchReport1');


	&#036;model-&gt;unsetAttributes();  // clear any default values


	if(isset(&#036;_GET['Projects']))


		&#036;model-&gt;attributes=&#036;_GET['Projects'];





	&#036;this-&gt;render('report1',array(


		'model'=&gt;&#036;model,


	));

on the view I created a file with:

<?php $this->widget(‘zii.widgets.grid.CGridView’, array(

//'id'=&gt;'projects-grid',


'dataProvider'=&gt;&#036;model-&gt;searchReport1(),


'filter'=&gt;&#036;model,


'columns'=&gt;array(








   'device_category_id',


   'totale',

Zaccaria and all thank you very much!