Select with a label that includes information from two different tables.

Gargiucn

I refer to the following old discussion:
https://datatables.net/forums/discussion/44483
I couldn't figure out how to do it and I think it would be very useful to have an example.
I stopped here:

Field::inst( 'pagamenti.pag_idcontr' )
    ->options( function(){
        global $db;
        return $db->sql( 'SELECT contratti.con_num, clienti.cli_ragsoc FROM clienti INNER JOIN 
                   contratti ON clienti.cli_id = contratti.con_cli' )->fetchAll();
    } )                 
    ->validator( Validate::dbValues() 

client side:

fields: [       
    {
        label: "Contratto",
        name: "pagamenti.pag_idcontr",
        type: "select",
        placeholderDisabled: false,
        placeholder: "Seleziona..." 
    },  
...

The field "contratti.pag_idcont" should have a label that displays together:
"contratti.con_num" and "clienti.cli_ragsoc".

Your help will be greatly appreciated...

Giuseppe

Gargiucn

I believe I have solved it:

Field::inst( 'pagamenti.pag_idcontr' )
   ->options( function(){
      global $db;
        return $db->sql( 'SELECT contratti.con_id as value, 
           CONCAT(contratti.con_num, contratti.con_sigla, " ",clienti.cli_ragsoc) as 
           label FROM clienti INNER JOIN contratti ON clienti.cli_id' )
          ->fetchAll();
    } )                
    ->validator( Validate::dbValues()

I hope he can help someone ...
Giuseppe

allan

Hi Giuseppe,

Perfect - thanks for posting your solution. That looks good to me. You might be able to do function () use ($db) { rather than accessing it from global scope, but that's the only thing I'd change.

Allan

Gargiucn

I thought I had done a good job, the select works fine on its own but within my program it generates the following error:

<b>Fatal error</b>:  Uncaught Error: Call to a member function table() on null in C:\inetpub\wwwroot\test\php\Editor\Validate.php:1026
Stack trace:
#0 C:\inetpub\wwwroot\test\php\Editor\Field.php(690): DataTables\Editor\Validate::DataTables\Editor\{closure}('1', Array, Object(DataTables\Editor\Field), Array)
#1 C:\inetpub\wwwroot\test\php\Editor.php(814): DataTables\Editor\Field-&gt;validate(Array, Object(DataTables\Editor), '0')
#2 C:\inetpub\wwwroot\test\php\Editor.php(996): DataTables\Editor-&gt;validate(Array, Array)
#3 C:\inetpub\wwwroot\test\php\Editor.php(683): DataTables\Editor-&gt;_process(Array)
#4 C:\inetpub\wwwroot\test\php\table-pagamenti.php(76): DataTables\Editor-&gt;process(Array)
#5 {main}
  thrown in <b>C:\inetpub\wwwroot\test\php\Editor\Validate.php</b> on line <b>1026</b>

This is the php script:

<?php

include( "DataTables.php" );
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst( $db, 'pagamenti', 'pag_id' )
    ->fields(               
    /*
        ********** SCRIPT THATS WORKS **********
        Field::inst( 'pagamenti.pag_idcontr' )
            ->options( Options::inst()      
                ->table( 'contratti' )
                ->value( 'con_id' )
                ->label( array('con_num', 'con_sigla','con_importo') )
                ->render( function ( $row ) {
                    return $row['con_num'].''.$row['con_sigla'].' - Importo contratto €'.$row['con_importo'];
                } )
                ->order( 'con_sigla DESC, con_num DESC ' )
                ->where( function ($q) {
                    $q->where( function ( $r ) {
                        if ( ! isset($_POST['idcontratto']) || ! is_numeric($_POST['idcontratto']) ) {
                            $r->where( 'contratti.con_user',$_SESSION['id'] );
                        }else{
                            $r->where( 'contratti.con_id', $_POST['idcontratto'] );
                        }
                    } );
                } )             
            )
            ->validator( Validate::dbValues() ),    
    */  
        /********** SCRIPT THAT GENERATES ERROR **********/     
        Field::inst( 'pagamenti.pag_idcontr' )
            ->options( function() use ($db){                
                $select = 'SELECT contratti.con_id as value, 
                    CONCAT(contratti.con_num,contratti.con_sigla," ",clienti.cli_ragsoc) as label 
                    FROM contratti LEFT JOIN clienti ON clienti.cli_id = contratti.con_cli  
                    WHERE contratti.con_user='.$_SESSION['id'].' AND contratti.con_chiuso=0';   
                    if ( ! isset($_POST['idcontratto']) || ! is_numeric($_POST['idcontratto']) ) {
                        //nothing
                    }else{
                        $select.=' AND contratti.con_id='.$_POST['idcontratto'].'';
                    }   
                return $db->sql( $select )->fetchAll();
            } ) 
            ->validator( Validate::dbValues() ),    
        /********** END **********/ 
    
        //Field::inst( 'contratti.con_num' ),
        //Field::inst( 'contratti.con_sigla' ),
        
        Field::inst( 'pagamenti.pag_user' ),
        Field::inst( 'pagamenti.pag_importo' )  
        Field::inst( 'pagamenti.pag_data' ) 

        ->on( 'preCreate', function ( $editor, $values ) {
            $editor
                ->field( 'pagamenti.pag_user' )
                ->setValue( $_SESSION['id'] );
        } )         
        ->leftJoin( 'contratti', 'con_id', '=', 'pagamenti.pag_idcontr' )   
        ->where( function ($q) {
            if ( ! isset($_POST['idcontratto']) || ! is_numeric($_POST['idcontratto']) ) {
                $q->where( 'pagamenti.pag_user',$_SESSION['id'] );
            }else{
                $q->where( 'pagamenti.pag_idcontr', $_POST['idcontratto'] );
            }
        } )     
        ->process( $_POST )
        ->json();


<?php
>  
?>

Sigh...

allan

That's this line.

Its throwing an error because it can't get the information needed for the validator from the Options class.

What you can do is pass in the table name and column to check against for the validation using the options for the dbValues validation method.

Allan