Hi,
I have two tables,
clients
clientid|client_name|client_type|use_id
&
client_type
id|name
now i want to retrieve data using below query
SELECT ct.name, COUNT(*) AS ClientTypeCount FROM clients t JOIN client_type ct ON t.client_type = ct.id WHERE user_id = 1 GROUP BY t.client_type ORDER BY ClientTypeCount DESC
inside controller:
$criteria = new CDbCriteria;
$criteria->together = true;
$criteria->compare('user_id', Yii::app()->user->getId(), false, 'AND');
$criteria->select = 'COUNT(client_type) as client_type_count';
$criteria->with = array('clientType');
$criteria->group = 'client_type';
$dataProvider = new CActiveDataProvider('Clients', array(
'criteria' => $criteria
));
echo "<pre>";
print_r($dataProvider->getData() );
exit;
inside model:
public function relations()
{
return array(
'user' => array(self::BELONGS_TO, 'Users', 'user_id'),
'clientType' => array(self::BELONGS_TO, 'ClientType', 'client_type'),
);
}
please advice how can i archive this in Yii
paul_po
August 18, 2014, 9:03am
2
Hi,
I have two tables,
clients
clientid|client_name|client_type|use_id
&
client_type
id|name
now i want to retrieve data using below query
SELECT ct.name, COUNT(*) AS ClientTypeCount FROM clients t JOIN client_type ct ON t.client_type = ct.id WHERE user_id = 1 GROUP BY t.client_type ORDER BY ClientTypeCount DESC
inside controller:
$criteria = new CDbCriteria;
$criteria->together = true;
$criteria->compare('user_id', Yii::app()->user->getId(), false, 'AND');
$criteria->select = 'COUNT(client_type) as client_type_count';
$criteria->with = array('clientType');
$criteria->group = 'client_type';
$dataProvider = new CActiveDataProvider('Clients', array(
'criteria' => $criteria
));
echo "<pre>";
print_r($dataProvider->getData() );
exit;
inside model:
public function relations()
{
return array(
'user' => array(self::BELONGS_TO, 'Users', 'user_id'),
'clientType' => array(self::BELONGS_TO, 'ClientType', 'client_type'),
);
}
please advice how can i archive this in Yii
Hi,
What is the error you are getting when you execute your code?. It will help us to direct you in a right direction.
Regards,
Paul
No errors display while execution above code however i can not get group by count values
while execute query inside mysql got below result:
name|ClientTypeCount
abex|12
US|59
But inside code i got result like this:
Array
(
[0] => Clients Object
(
[_new:CActiveRecord:private] =>
[_attributes:CActiveRecord:private] => Array
(
[id] => 3
[created_on] =>
)
[_related:CActiveRecord:private] => Array
(
[clientType] => ClientType Object
(
[_new:CActiveRecord:private] =>
[_attributes:CActiveRecord:private] => Array
(
[id] => 1
[name] => abex
[order] => 1
)
[_related:CActiveRecord:private] => Array
(
)
[_c:CActiveRecord:private] =>
[_pk:CActiveRecord:private] => 1
[_alias:CActiveRecord:private] => t
[_errors:CModel:private] => Array
(
)
[_validators:CModel:private] =>
[_scenario:CModel:private] => update
[_e:CComponent:private] =>
[_m:CComponent:private] =>
)
)
[_c:CActiveRecord:private] =>
[_pk:CActiveRecord:private] => 3
[_alias:CActiveRecord:private] => t
[_errors:CModel:private] => Array
(
)
[_validators:CModel:private] =>
[_scenario:CModel:private] => update
[_e:CComponent:private] =>
[_m:CComponent:private] =>
)
[1] => Clients Object
(
[_new:CActiveRecord:private] =>
[_attributes:CActiveRecord:private] => Array
(
[id] => 15
[created_on] =>
)
[_related:CActiveRecord:private] => Array
(
[clientType] => ClientType Object
(
[_new:CActiveRecord:private] =>
[_attributes:CActiveRecord:private] => Array
(
[id] => 2
[name] => Us
[order] => 2
)
[_related:CActiveRecord:private] => Array
(
)
[_c:CActiveRecord:private] =>
[_pk:CActiveRecord:private] => 2
[_alias:CActiveRecord:private] => t
[_errors:CModel:private] => Array
(
)
[_validators:CModel:private] =>
[_scenario:CModel:private] => update
[_e:CComponent:private] =>
[_m:CComponent:private] =>
)
)
[_c:CActiveRecord:private] =>
[_pk:CActiveRecord:private] => 15
[_alias:CActiveRecord:private] => t
[_errors:CModel:private] => Array
(
)
[_validators:CModel:private] =>
[_scenario:CModel:private] => update
[_e:CComponent:private] =>
[_m:CComponent:private] =>
)
)
so i not get group by count of those types, please advice
