Yii Framework Forum: Activerecord Joining Related Tables Does Not Join - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Activerecord Joining Related Tables Does Not Join Rate Topic: -----

#1 User is offline   transistor 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 79
  • Joined: 05-June 09
  • Location:Mexico

Posted 14 November 2012 - 01:36 PM

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!
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users