How do I do a distinct on a join query using codeigniter or just to omitt duplicates

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

Are you looking to only return the first result from the dataset? — Brian Ramsey, May 11 '16 at 07:55
@BrianRamsey Im looking to not repeat rows that were already outputted — learningbyexample, May 11 '16 at 08:40

score 0 · Accepted Answer · edited May 23 '17 at 11:52

0

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?

edited May 23 '17 at 11:52

Community

1
1

answered May 11 '16 at 08:54

Brian Ramsey

850
7
21

what is group_by()'s main function? – learningbyexample May 11 '16 at 09:01
How do I decide which on is the best choice to group by since I can chose any tabl_ name? your answer helped resolve my issue but it takes too long doing the query. @brianRamsey – learningbyexample May 11 '16 at 09:13
You need to find the unique value (preferably indexed) to group by. I strongly suggest taking a look at all the tables you have joined, find the best col to do it on (sometimes/often this wont even be based on the data you are selecting/displaying). I also dont get calling return $this->db->get($table); seeing you are using a FROM and JOINS? why not just $this->db->get(); ? – Brian Ramsey May 11 '16 at 09:20
@BrianRamsey where do I specify the table(s) if i take out `table` – learningbyexample May 11 '16 at 09:40
You have already specified them in your $this->db->from() and $this->db->join()? you only use table names in $this->db->get() where you haven't specified FROM or JOIN. like simply returning all columns from a table with 'return $this->db->get('table');' – Brian Ramsey May 11 '16 at 09:41
if i take table out it gives me an error that the first fieldname does not exist. One more question... how do I access the data not that I have the query. ex: inside a foreach ($reg as $r) { $data = $r->cm_empreado.nombre; } ??? @BrianRamsey – learningbyexample May 11 '16 at 09:54
There is no reason for that error, I'm stumped... 2nd question) change your output to be: return $this->db->get($table)->row_array(); at the moment you are not asking for a format and foreach needs an array – Brian Ramsey May 11 '16 at 10:01

How do I do a distinct on a join query using codeigniter or just to omitt duplicates

1 Answers1