GROUP BY with DISTINCT LEFT

Archives Yii 1.0.x Discussions
1

Hello,

Can anyone help me how to use GROUP BY with DISTINCT LEFT like this mysql query

SELECT DISTINCT LEFT(telephone, 6) FROM USER GROUP BY telephone;

GROUP BY working fine but I need left 6 number.

my action controller

public function actionRequest()

{


	$this->processAdminCommand();





	$criteria=new CDbCriteria;


   


	$criteria->group= 'telephone';


	$pages=new CPagination(USER::model()->count ($criteria)); 


      


	$pages->pageSize=self::PAGE_SIZE;


	$pages->applyLimit($criteria);





	$sort=new CSort('USER');


           $sort->applyOrder($criteria);





	$models=USER::model()->findAll($criteria);





	$this->render('request',array(


		'models'=>$models,


		'pages'=>$pages,


		'sort'=>$sort,


	));


}
2

you are unclear about your question

but I dare say

SELECT DISTINCT LEFT(telephone, 6) as xxx FROM USER;

"group by" is not necesary

count not work with group by and distinct

you have to do manual :(

see http://code.google.com/p/yii/issues/detail?id=675

and http://code.google.com/p/yii/source/detail?r=1563

and http://code.google.com/p/yii/issues/detail?id=694

greetings

3

Thanks for your reply, actually I want to use DISTINCT LEFT(telephone, 6) . Any idea.

4 



public function actionRequest()

{

$this->processAdminCommand();


$criteria=new CDbCriteria;


$criteria->select= 'count(distinct LEFT(telephone, 6))';


$count=USER::model()->find($criteria)->queryScalar();




$criteria->distinct= true;

$criteria->select= 'LEFT(telephone, 6) as telephone6';


$pages=new CPagination($count); 


$pages->pageSize=self::PAGE_SIZE;

$pages->applyLimit($criteria);


$sort=new CSort('USER');

$sort->applyOrder($criteria);


$models=USER::model()->findAll($criteria);


$this->render('request',array(

'models'=>$models,

'pages'=>$pages,

'sort'=>$sort,

));

}

and add at your model




public telephone6;
5

Thanks Buddy. You re great. Life is easy.