option with label linked

e.jourde

Hello, I have a particular database structure. With a translation of all the labels. So to display a select I have to go through 2 tables. How to display in the option label the translation which would be "profile_lib.translation"?

Editor::inst( $db, 'usr', 'id_usr' )
->fields(Field::inst( 'usr.id_usr')
->set(false)
,Field::inst( 'usr.name')
,Field::inst( 'usr.forname')
,Field::inst( 'usr.id_profile')
->options( Options::inst()
                ->table( 'profile' )
                ->value( 'id_profile' )
                ->label( 'id_translate' )
                   
            )
            ->validator( Validate::dbValues() )
,Field::inst( 'usr.activate')
->setFormatter( function ( $val, $data, $opts ) {
                        return ! $val ? 0 : 1;
                    } )

)                    
->leftJoin("profile","usr.id_profile=profile.id_profile","","")
->leftJoin("profile_lib", "translate_language.id_translate=profile.id_translate and translate_language.id_language= ".$_GET['langue'],"","")
->debug( true )
->process( $_POST )
->json();

struct : usr is linked to profile by id_profile and profile is linked to translate language by id_translate

rf1234

If you need to get the label from multiple tables please observe that you can't do a left join in an Editor options instance. But you can do an implicit inner join through the where clause. Here is an example from my own coding. The example is about assigning a department id (govdept.id) to a contract. To select the department the user sees a label that consists of rendered fields of two tables: "govdept" and "gov". You can also do this with three or more tables if you like.

Field::inst( 'contract.govdept_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
        ->options( Options::inst()
            ->table('govdept, gov')
            ->value('govdept.id')
            ->label( array('gov.name', 'govdept.name', 'gov.type', 'gov.regional_12') )
            ->render( function ( $row ) {               
                return $row['gov.name'].' / '.$row['govdept.name'].' ('.$row['gov.regional_12'].'); '
                        .renderGovType($row['gov.type']); 
            } )
            ->order( 'gov.name asc' )
            //where clause MUST be a closure function in Options!!!
            ->where( function($q) {
                //only govs that are already clients can be selected
                $q ->where( function($r) {
                    $r ->where( function($s) {
                        $s ->where('gov.is_client_fin', 1 );
                        $s ->or_where('gov.is_client_marketplace', 1 );
                    });
                  //and the inner join through the WHERE clause
                    $r ->where('govdept.gov_id', 'gov.id', '=', false);
                });
            } )
        ),

e.jourde

thank you very much, i will study your code.

rf1234

There is an alternative as well: If you think the Editor options instance isn't flexible enough you could use a custom function: https://editor.datatables.net/manual/php/joins#Closure---custom-function

Using your own or Editor's db handler and its raw() method you can select the array of label - value pairs "manually" using SQL like this for example:

Field::inst( 'your.field' )
    ->options( function () use ($categoryId, $db) {
         $statement = 
                   ('SELECT DISTINCT value AS label, id AS value 
                       FROM category_value_range 
                      WHERE ctr_category_id = :categoryId 
                   ORDER BY 1 ASC');  
          $result =
          $db ->raw()
              ->bind(':categoryId', $categoryId); 
              ->exec($statement);

          return $result->fetchAll(PDO::FETCH_ASSOC);
    } );

e.jourde

thank you very much, it works!