如何使用codeigniter对连接查询执行不同的操作或仅使用omitt重复项

Question

I have this code that does outputs repeated rows but when I look for them individually in the database only 3 rows apear but when I use join 6 of them come out and 3 of them are repeated. How do I avoid this or how do I omit the duplicates.

<?php 
class Joins_model extends CI_Model{
    private $table = 'cm_proveedor, cm_compras, cm_valuacion, cm_valuacionr, cm_nomina, cm_empleado';
    function _construct(){
       parent::Model();
    }

    function get_reg($data){
        $this->db->distinct();
        $this->db->select('
                        cm_proveedor.nombre,
                        cm_valuacion.mano_obra,
                        cm_valuacion.refaccion,
                        cm_valuacionr.refaccion,
                        cm_valuacion.costoHojalateria,
                        cm_valuacion.costoPintura,
                        cm_valuacion.costoMecanica,
                        cm_valuacion.pv_hojalateria,
                        cm_valuacion.pv_pintura,
                        cm_valuacion.pv_mecanica,
                        cm_valuacion.pc_hojalateria,
                        cm_valuacion.pc_pintura,
                        cm_valuacion.pc_mecanica,
                        cm_valuacion.tipo,
                        cm_valuacion.hojalateria,
                        cm_valuacion.pintura,
                        cm_valuacion.mecanica,
                        cm_valuacion.tipo_r,
                        cm_empleado.nombre,
                        cm_compras.precio
                    ');

        $this->db->from('cm_proveedor');
        $this->db->join('cm_compras','cm_proveedor.id = cm_compras.id_proveedor');
        $this->db->join('cm_valuacion','cm_compras.id_siniestro = cm_valuacion.id_siniestro');
        $this->db->join('cm_valuacionr','cm_valuacion.id_siniestro = cm_valuacionr.id_siniestro');
        $this->db->join('cm_nomina','cm_valuacionr.id_siniestro = cm_nomina.id_siniestro');
        $this->db->join('cm_empleado','cm_nomina.id_empleado = cm_empleado.id');
        $this->db->where('cm_valuacion.id_siniestro',$data);
        //$this->db->order_by('id','asc');

        return $this->db->get($table);

    }

}
?>

currently outputting:

 name           mano_obra       cH  cP cM  mecanica tipo_r empleado precio   nomina  refaccion  refaccion1
MyStore     CAMBIO DE RADIADOR  0   0   0    250      0    DAVID    1850.00   89.00   REDIADOR    REDIADOR

MyStore     CAMBIO DE RADIADOR  0   0   0    250      0    DAVID    150.00    89.00   REDIADOR    REDIADOR

MyStore     CAMBIO DE RADIADOR  0   0   0    250      0    DAVID    1850.00   89.00   REDIADOR    RADIADOR 

MyStore     CAMBIO DE RADIADOR  0   0   0    250      0    DAVID    150.00    89.00   REDIADOR    RADIADOR 

MyStore     CAMBIO DE RADIADOR  0   0   0    250      0    DAVID    1850.00   89.00   REDIADOR    ANTICONGELANTE

MyStore     CAMBIO DE RADIADOR  0   0   0    250      0    DAVID    150.00    89.00   REDIADOR    ANTICONGELANTE

Answer 1

It seems there is additional data being brought over from you joins causing the duplicates to be formed.

I believe you need to add a GROUP BY at the end of the call

Potentially:

$this->db->group_by('refaccion1');

Basically what are you looking to remove from the dataset? DISTINCT on its own wont really do much in this instance.

Basically, if you omitted the select statement.. there would be different data in some results causing the 'duplicates'. Try running the query directly in SQL query and see the results.

I also suggest dropping

private $table = 'cm_proveedor, cm_compras, cm_valuacion, cm_valuacionr, cm_nomina, cm_empleado';

and changing in your model:

return $this->db->get($table);

to

return $this->db->get();

The method you are using you are basically joining twice to each column, FROM with multiple tables is the old depreciated method of doing JOINs. Effectively joining your tables twice which will hurt performance and there is absolutely no need.

Further reference: SQL left join vs multiple tables on FROM line?