Cdbcriteria Select Distinct On

Yii 1.1 General Discussion
1

Hello,

I have the need to create a CActiveDataProvider using a CDbCriteria.

The CDbCriteria must return data like this query:

SELECT DISTINCT ON (field1,field2) * FROM table

WHERE field=value

…

In CDbCriteria i have the boolean attribute distinct, but how can i implement the ‘on’?

Any sugestions?

Thanks.

2

Just put it in the ‘select’ part.

3

Problem solved!

Thanks!

4

How? I’m having the same problem




$criteria = new CDbCriteria;

				$criteria->select = 'DISTINCT ON(osp.placa_id) t.*, sp.nome as status';

				$criteria->with = array(

										'ocorrenciasStatus'=>array('alias'=>'osp', 'joinType'=>'INNER JOIN'),

										'ocorrenciasStatus.entidade'=>array('alias'=>'ospe', 'joinType'=>'INNER JOIN'),

										'ocorrenciasStatus.statusPlaca'=>array('alias'=>'sp', 'joinType'=>'INNER JOIN')

									);

				$criteria->together = true;

				$criteria->addCondition('ospentscope.cadastro <= :dataBase');

				$criteria->params = array(':dataBase'=>$relatorioEmplacamento->dataBase);

				$criteria->order = 'osp.placa_id, ospentscope.cadastro DESC';

I want it to return something like SELECT DISTINCT ON(osp.placa_id) *, sp.nome AS status …

but i’m getting SELECT *, sp.nome AS status … as result query

5

Hi Sir, I’m a beginner in Yii. My problem is: i’m using $criteria->addInCondition(‘std_id’,array(4,5,6)); code under $criteria->compare(‘std_p_name’,$this->std_p_name); this code and it is out put me the id the number in the array field i have. how can i use other method in here addBetweenCondition/addNotInCondition like $criteria->addInCondition(‘std_id’,array(4,5,6)); this. The documentation page github example give me a function reference like

""

public function addNotInCondition($column,$values,$operator=‘AND’)

{

if(($n=count($values))<1)

return $this;

if($n===1)

{

$value=reset($values);

if($value===null)

$condition=$column.’ IS NOT NULL’;

else

{

$condition=$column.’!=’.self::PARAM_PREFIX.self::$paramCount;

$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;

}

}

else

{

$params=array();

foreach($values as $value)

{

$params[]=self::PARAM_PREFIX.self::$paramCount;

$this->params[self::PARAM_PREFIX.self::$paramCount++]=$value;

}

$condition=$column.’ NOT IN (’.implode(’, ‘,$params).’)’;

}

return $this->addCondition($condition,$operator);

}

""

but i want to use it simple like above $criteria->addInCondition(‘std_id’,array(4,5,6));

How i can i do it. Please tell me. Please remember i’m a beginner so alot think is not clear to me.

Thanks

6

Hi, I have the same problem. I want to use “DISTINCT ON()”, but if I put it in select, and no matter if it’s plain text or new CDbExpression", it will disapper from there. There must be some part in Yii, which actively removes it.

Do you anybody know, how I can construct a sql query with DISTINCT ON() via activerecord/CActiveDataProvider.

After two days, I’m totaly hopeless star thinking, it’s not possible :frowning:

Thanks