There’s something I’m missing, I’m sure, but I don’t know what it is.
I am trying to get a list of Colegios that belong to a Pais and to a Empresa.
Colegio belogs to a Pais, and Empresa belogs to a Pais, but Colegio does not belong directly to a Empresa.
The thing is, that the query that Yii makes to get the COUNT, it joins Empresa fine, but not in the query to select the Colegios, why?
public function obtener_colegios($empresa_id, $ciclo_escolar_id)
{
$pais_empresa = Empresa::model()->obtener_pais_empresa($empresa_id);
$pais_id = $pais_empresa['pais_id'];
$criteria = new CDbCriteria;
$criteria->with = array(
'pais',
'pais.empresas'
);
$criteria->compare('colegio_id',Colegio::model()->colegio_id);
$criteria->compare('colegio',Colegio::model()->colegio);
$criteria->condition = "colegio != '' and colegio_id > 0 and t.pais_id=:pais_id and empresa_id=:empresa_id";
$criteria->params = array(':pais_id'=>$pais_id, 'empresa_id'=>$empresa_id);
$colegios = new CActiveDataProvider('colegio', array(
'criteria'=>$criteria,
'sort'=>array(
'defaultOrder'=>'colegio'
),
));
return $colegios;
}
// model Colegios
public function relations()
{
return array(
...
'pais' => array(self::BELONGS_TO, 'Pais', 'pais_id'),
...
);
}
// model Pais
public function relations()
{
return array(
...
'colegios' => array(self::HAS_MANY, 'Colegio', 'pais_id'),
'empresas' => array(self::HAS_MANY, 'Empresa', 'pais_id'),
...
);
}
// model Empresa
public function relations()
{
return array(
...
'pais' => array(self::BELONGS_TO, 'Pais', 'pais_id'),
...
);
}
// this query joins pais and empresa just fine. I removed the field I want to use from Empresa because the next query fails.
SELECT COUNT(DISTINCT `t`.`colegio_id`)
FROM `colegio` `t`
LEFT OUTER JOIN `pais` `pais` ON (`t`.`pais_id`=`pais`.`pais_id`)
LEFT OUTER JOIN `empresa` `empresas` ON (`empresas`.`pais_id`=`pais`.`pais_id`)
WHERE (colegio != '' and colegio_id > 0 and t.pais_id='44')
// this query does not join Empresa, why not?
SELECT `t`.`colegio_id` AS `t0_c0`, `t`.`pais_id` AS `t0_c1`, `t`.`colegio_clave` AS `t0_c2`, `t`.`colegio` AS `t0_c3`, `t`.`colegio_orden` AS `t0_c4`, `t`.`persona_id` AS `t0_c5`, `t`.`colegio_padre_id` AS `t0_c6`, `t`.`fecha_ingreso` AS `t0_c7`, `t`.`crm_id` AS `t0_c8`, `t`.`sap_id` AS `t0_c9`, `t`.`baja` AS `t0_c10`, `t`.`razon_baja_id` AS `t0_c11`, `t`.`fecha_actualizacion` AS `t0_c12`, `t`.`persona_rol_id` AS `t0_c13`, `t`.`version` AS `t0_c14`, `pais`.`pais_id` AS `t1_c0`, `pais`.`pais_clave` AS `t1_c1`, `pais`.`pais` AS `t1_c2`, `pais`.`pais_orden` AS `t1_c3`, `pais`.`separador_mil` AS `t1_c4`, `pais`.`separador_decimal` AS `t1_c5`, `pais`.`formato_numero` AS `t1_c6`, `pais`.`formato_fecha` AS `t1_c7`, `pais`.`tamanio_papel` AS `t1_c8`, `pais`.`fecha_actualizacion` AS `t1_c9`, `pais`.`persona_rol_id` AS `t1_c10`
FROM `colegio` `t`
LEFT OUTER JOIN `pais` `pais` ON (`t`.`pais_id`=`pais`.`pais_id`)
WHERE (colegio != '' and colegio_id > 0 and t.pais_id='44') ORDER BY colegio LIMIT 10
Thanks!