I have the following code that joins three tables in my app:
$criterios = new CDbCriteria();
//Multi-level with(), to join a total of three tables.
//First level is FacturaConceptoComplemento.
$criterios->with = array(
'facturaConcepto' => array(
'with' => array(
'factura' => array(
'select' => '',
),
),
'select' => '',
),
);
//Two instructions that did not help.
$criterios->group = 't.id';
$criterios->together = true;
//Fields needed.
$criterios->select = 'campo1,campo2,campo3,campo4,campo5,campo6,campo7,';
$criterios->select .= 'valor1,valor2,valor3,valor4,valor5,valor6,valor7';
$criterios->distinct = true;
$models = FacturaConceptoComplemento::model()->findAll($criterios);
I only need the data from the table FacturaConceptoComplemento, but only when a value in Factura is met (therefore the need to go up two levels).
However, as soon as I use the with(), the following query is created:
SELECT DISTINCT `t`.`campo1` AS `t0_c4`, `t`.`campo2` AS `t0_c6`, `t`.`campo3` AS `t0_c8`, `t`.`campo4` AS `t0_c10`, `t`.`campo5` AS `t0_c12`, `t`.`campo6` AS `t0_c14`, `t`.`campo7` AS `t0_c16`, `t`.`valor1` AS `t0_c5`, `t`.`valor2` AS `t0_c7`, `t`.`valor3` AS `t0_c9`, `t`.`valor4` AS `t0_c11`, `t`.`valor5` AS `t0_c13`, `t`.`valor6` AS `t0_c15`, `t`.`valor7` AS `t0_c17`, `t`.`id` AS `t0_c0` FROM `cfdi_factura_concepto_complemento` `t` LEFT OUTER JOIN `cfdi_factura_concepto` `facturaConcepto` ON (`t`.`factura_concepto_id`=`facturaConcepto`.`id`) LEFT OUTER JOIN `cfdi_factura` `factura` ON (`facturaConcepto`.`factura_id`=`factura`.`id`)
That is: it selects only the fields for the FacturaConceptoComplemento model defined by the select, PLUS the id from that same table (t
.id
AS t0_c0
). My problem is that the id field that is automatically added by the with() is unique, rendering the DISTINCT clause useless since all results will be shown.
My question is, how can I remove that id field that adds itself to the query (since on my select parameters I have specified the fields I want already). Notice that the group and together parameters did not help for this (although I am not sure of what those do).
Thanks in advance .