Yii Framework Forum: Cdbcriteria Group + Count - Yii Framework Forum

Jump to content

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

Cdbcriteria Group + Count Rate Topic: -----

#1 User is offline   kotuks 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 1
  • Joined: 22-December 12

Posted 22 December 2012 - 02:38 AM

Прощу помощи с построением критериев:

Имеется таблица оборудования в которую заносятся записи при определенных событиях, требуется сделать выборку наподобие статистики, что из оборудования наиболее часто/редко оставляло записи в таблице и количество этих записей

Сам mysql запрос который должен отрабатывать:

SELECT did, Class, Name, IP, COUNT( did ) AS counter
FROM  `events` 
GROUP BY did
ORDER BY counter DESC;


нужна помощь с организацией критерия в модели таблицы:
/**
 * This is the model class for table "events".
 *
 * The followings are the available columns in table 'events':
 * @property integer $eid
 * @property integer $did
 * @property integer $Class
 * @property string $Name
 * @property string $IP
 * @property string $Date
 * @property integer $Status
 * @property string $Reason
 * @property integer $Sender
 * @property integer $Repair
 * @property string $Senddate
 * @property string $Comment
 * @property string $Donedate
 * @property integer $Owner
 */


<------>public function relations()
<------>{
<------><------>// NOTE: you may need to adjust the relation name and the related
<------><------>// class name for the relations automatically generated below.
<------><------>return array(
<------><------>'counter' => array(self::STAT, 'Events', 'did'),
<------><------>);
<------>}






public function search2()
{
<------><------>$criteria=new CDbCriteria;
<------><------>
<------><------>$criteria->with = array('counter');
<------><------>$criteria->compare('eid',$this->did);
<------><------>$criteria->compare('did',$this->did);
<------><------>$criteria->compare('Class',$this->Class);
<------><------>$criteria->compare('Name',$this->Name,true);
<------><------>$criteria->compare('IP',$this->IP,true);
<------><------>$criteria->group = 'did';
<------><------>return new CActiveDataProvider($this, array(
<------><------><------>'criteria'=>$criteria,
                       'sort' => array(
                      'defaultOrder' => 'counter  DESC'
<------><------><------> ),
<------>                )
<------>                );
}


нечто подобное выдает ошибку:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'counter' in 'order clause'. The SQL statement executed was: SELECT `t`.`eid` AS `t0_c0`, `t`.`did` AS `t0_c1`, `t`.`Class` AS `t0_c2`, `t`.`Name` AS `t0_c3`, `t`.`IP` AS `t0_c4`, `t`.`Date` AS `t0_c5`, `t`.`Status` AS `t0_c6`, `t`.`Reason` AS `t0_c7`, `t`.`Sender` AS `t0_c8`, `t`.`Repair` AS `t0_c9`, `t`.`Senddate` AS `t0_c10`, `t`.`Comment` AS `t0_c11`, `t`.`Comdate` AS `t0_c12`, `t`.`Donedate` AS `t0_c13`, `t`.`Owner` AS `t0_c14` FROM `events` `t` GROUP BY did ORDER BY counter DESC LIMIT 10


Заранее благодарен за помощь
0

#2 User is offline   Charger 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 317
  • Joined: 03-September 11

Posted 23 December 2012 - 02:29 AM

В CDbCriteria нужно добавить столбец "counter", что бы по нему можно было сортировать. Возможно:
$criteria->select='COUNT( did ) AS counter';
//или может
$criteria->select[]='COUNT( did ) AS counter';

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