Help Me With Criteria

Yii 1.1 General Discussion
1

Hi good morning,

my problem is:

I´am have this sql:

select t.id, t.nombre, t.f_baja, t.baja, d.f_inicio, d.f_fin, d.contrato as cont, (select count(dc.id) from duracion_contratos dc where dc.trabajadores_id = d.trabajadores_id) as numContrato from trabajadores t, duracion_contratos d where t.id = d.trabajadores_id group by t.id, cont;

The code with criteria is the following one:

$criteria=new CDbCriteria;

	$criteria->alias='d';


	$criteria ->select="t.nombre, t.apellido1, t.apellido2, d.contrato, d.f_inicio, d.f_fin, t.f_baja, t.baja, t.id, t.cr, t.ar, t.dr, d.contrato as cont, sum(d.contrato) AS numContrato ";


	$criteria ->join="INNER JOIN trabajadores t ON t.id = d.trabajadores_id";


	//$criteria ->condition("t.empresas_id =".$id);


	$criteria->condition = 't.empresas_id=:id';


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


	$criteria->group="t.nombre, t.apellido1, t.apellido2, d.contrato, d.f_inicio, d.f_fin, t.f_baja, t.baja, t.id, t.cr, t.ar, t.dr, cont";

Only to implement the subquery of the select, can someone help me?

thanks

2

Hello jairo1986

Your code looks fine to me except this


d.contrato as cont, sum(d.contrato) AS numContrato

you’re using the same column but in your pure sql


d.contrato as cont, (select count(dc.id)

, CDbCriteria only returns table column names, so if you’re using aleas for cloumns you’ve use a table cloumn name for that, first try without thoee


d.contrato as cont, sum(d.contrato) AS numContrato

if your intended result returns try giving alias as primary table not using column names this should work because I’ve used this in many cases.

if your class relations are build properly try this.




$ct = new CDbCriteria();

$ct->together = true;

$ct->select = array(t.nombre, t.apellido1, t.apellido2,

/*if you like you can use the aliases here, 

with a primary table column name as alias eg- 

model relation name for joining table (find it under model raltions) */.-

-contrato as primary table column 

that is not using for this query); 

/* better to use select as an array, 

if you're using aliases for columns */

$ct->with = array('primary table relation name to trabajadores'=> 

array(/*more specified conditions*/ 'joinType' => 'INNER JOIN', 'condition' => '', 'params' => array()) 

/*not required, if you want */) /* why trouble specifying join columns, 

if your model relations are build just mention it, 

Yii will do the joining

eg - under primary model function relations, 

a relation name like this one 'stItems' => array(self::HAS_MANY, 'StItem', 'item_sub_cat_id'), */
3

Sorry this code correct is:

this sql:

select t.id, t.nombre, t.f_baja, t.baja, d.f_inicio, d.f_fin, d.contrato as cont, (select count(dc.id) from duracion_contratos dc where dc.trabajadores_id = d.trabajadores_id) as numContrato from trabajadores t, duracion_contratos d where t.id = d.trabajadores_id group by t.id, cont;

The code with criteria is the following one:

$criteria=new CDbCriteria;

$criteria->alias=‘d’;

$criteria ->select="t.nombre, t.apellido1, t.apellido2, d.contrato, d.f_inicio, d.f_fin, t.f_baja, t.baja, t.id, t.cr, t.ar, t.dr, d.contrato as cont, count(d.contrato) AS numContrato ";

$criteria ->join="INNER JOIN trabajadores t ON t.id = d.trabajadores_id";

//$criteria ->condition("t.empresas_id =".$id);

$criteria->condition = ‘t.empresas_id=:id’;

$criteria->params = array(’:id’=>$id);

$criteria->group="t.nombre, t.apellido1, t.apellido2, d.contrato, d.f_inicio, d.f_fin, t.f_baja, t.baja, t.id, t.cr, t.ar, t.dr, cont";

Vilo, to put on a simple example with code?