Yii Framework Forum: Sql Statement using AR - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Sql Statement using AR Rate Topic: -----

#1 User is offline   sbondi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 07-October 10

Posted 27 October 2010 - 08:35 AM

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
0

#2 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 27 October 2010 - 08:40 AM

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

0

#3 User is offline   ricardograna 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 376
  • Joined: 31-March 09
  • Location:Manaus/AM - Brazil

Posted 27 October 2010 - 08:47 AM

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


Yes, It Is!
1

#4 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 27 October 2010 - 09:03 AM

And as one liner:


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

0

#5 User is offline   sbondi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 07-October 10

Posted 27 October 2010 - 09:35 AM

View Postzaccaria, on 27 October 2010 - 08:40 AM, said:

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





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()
{


$criteria1= new CDbCriteria;
$criteria1->condition= "prj_start_date < '2010-03-01' AND prj_start_date >'2009-01-01' AND device_category_id='0' ";

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


return new CActiveDataProvider(get_class($this), array(
'criteria'=>$criteria1,
));
}

------------------------------

on the controller I wrote a new funtion:

public function actionReport1()
{
$model=new Projects('searchReport1');
$model->unsetAttributes(); // clear any default values
if(isset($_GET['Projects']))
$model->attributes=$_GET['Projects'];

$this->render('report1',array(
'model'=>$model,
));
}

------------------
and on the view I create a file with the code:
<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'projects-grid',
'dataProvider'=>$model->searchReport1(),
'filter'=>$model,
'columns'=>array(

'device_category_id',
array(
'header' => 'Totale',
'value' => '$totale',

),
/*'prj_start_date',
'prj_end_date', */


array(
'class'=>'CButtonColumn',
),
),
)); ?>


but I unable to see the results of count,
can you help me?
0

#6 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,338
  • Joined: 12-October 09
  • Location:Croatia

Posted 28 October 2010 - 02:55 AM

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...
Find more about me.... btw. Do you know your WAN IP?
0

#7 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 28 October 2010 - 02:59 AM

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

0

#8 User is offline   sbondi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 07-October 10

Posted 28 October 2010 - 07:34 AM

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
0

#9 User is offline   sbondi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 33
  • Joined: 07-October 10

Posted 29 October 2010 - 03:24 AM

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:
$model=new Projects('searchReport1');
$model->unsetAttributes(); // clear any default values
if(isset($_GET['Projects']))
$model->attributes=$_GET['Projects'];

$this->render('report1',array(
'model'=>$model,
));

------------------------------------------------------

on the view I created a file with:

<?php $this->widget('zii.widgets.grid.CGridView', array(
//'id'=>'projects-grid',
'dataProvider'=>$model->searchReport1(),
'filter'=>$model,
'columns'=>array(


'device_category_id',
'totale',

Zaccaria and all thank you very much!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users