selectDistinct apply a leftJoin

rrzavaleta

hi I met a small problem related to a leftJoin , the problem that I have related tables that are repeated and is devido to the structure of the database .
The result is duplication of the fields in this consultation. I've seen that you can use something like SelectDistinct but have not been successful .
->leftJoin( 'ING_VALOR_CAMPO', 'ING_VALOR_CAMPO.VAL_CAMPO', '=', 'MVR_ORDEN.SIT_ORDEN' ) // join this cause duplicate rows ,

hope you can help .

<?php

header("Content-Type: text/html;charset=utf-8");
//* Example PHP implementation used for the index.html example

// DataTables PHP library
include( "../extensions/Editor-1.3.3/php/DataTables.php" );

// Alias Editor classes so they are easy to use
function dias_transcurridos($fecha_i,$fecha_f)
{
$dias = (strtotime($fecha_i)-strtotime($fecha_f))/86400;
$dias = abs($dias); $dias = floor($dias);
return $dias;
}

//echo $fecha;

use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Join,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'MVR_ORDEN' )
->pkey( 'ID_ORDEN')
->fields(
Field::inst( 'MVR_ORDEN.ID_ORDEN'),
Field::inst( 'MVR_ORDEN.F_INGRESO2'),
Field::inst( 'MVR_ORDEN.ID_ASEGURADORA'),
Field::inst( 'ING_PERSONA.NOM_RAZON_SOCIAL' ),

    Field::inst( 'MVR_ORDEN.ID_CENTRO_REP'),
    Field::inst( 'MVR_CENTRO_REP.CENTRO' ),

    Field::inst( 'MVR_ORDEN.NUM_SINIESTRO'),

    Field::inst( 'MVR_ORDEN.ID_TIPO_ESTILO'),
    Field::inst( 'ING_ESTILOS.DESC_TIPO_ESTILO' ),  

    Field::inst( 'MVR_ORDEN.NUM_ORDEN'),

    Field::inst( 'MVR_ORDEN.SIT_ORDEN'),
    Field::inst( 'ING_VALOR_CAMPO.DESC_VALOR' ),


    Field::inst('MVR_ORDEN.F_INGRESO')
                 ->getFormatter( function  ( $val ) {
              $fechadebase = $val;
              $fechaactual=date("Y/m/d");
              $val = dias_transcurridos($fechadebase,$fechaactual);
              return($val);  

        } ),        
    Field::inst( 'MVR_ORDEN.CVE_ETAPA') 


                 )
 ->leftJoin( 'ING_PERSONA', 'ING_PERSONA.ID_ASEGURADORA', '=', 'MVR_ORDEN.ID_ASEGURADORA' )
 ->leftJoin( 'MVR_CENTRO_REP', 'MVR_CENTRO_REP.ID_CENTRO_REP', '=', 'MVR_ORDEN.ID_CENTRO_REP' ) 
  ->leftJoin( 'ING_ESTILOS', 'ING_ESTILOS.ID_ESTILO', '=', 'MVR_ORDEN.ID_TIPO_ESTILO' ) 
  ->leftJoin( 'ING_VALOR_CAMPO', 'ING_VALOR_CAMPO.VAL_CAMPO', '=', 'MVR_ORDEN.SIT_ORDEN' )    // join this cause duplicate rows ,
 ->selectDistinct( )
->process( $_POST )
->json();

rrzavaleta

I forgot to mention that in this case we need only to see the results table

allan

The Editor class has no selectDistinct method. Could you point me to the documentation where you found that it could, as it is wrong and needs to be corrected if it does say that.

You would likely need to use a complex where statement. See this comment for the principles of how to use an anonymous function to create complex queries.

Allan