option with label linked

option with label linked

e.jourdee.jourde Posts: 26Questions: 9Answers: 0

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

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406

    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.jourdee.jourde Posts: 26Questions: 9Answers: 0

    thank you very much, i will study your code.

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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.jourdee.jourde Posts: 26Questions: 9Answers: 0

    thank you very much, it works!

This discussion has been closed.