Use JSONB from PoSTGRESQL in datatable-editor simply

e.jourde

Good morning all !
I think this will help those who want to use the nosql features of POSTGRESQL. The jsonb update cannot work with the standard datatable update commands. Here is how to do it in a simple way.

field names are in the form jsonb jsonfield - >> property
the update must not be done by the normal procedure so -> set (false)
To perform the update, you have to go through the validator because the setFormatter will not work.
Here is an example of code.

Editor::inst( $db, 'btranslate',  'id_translate') 

        ->fields(
            Field::inst( 'id_translate' )->set(false)
            
            ,Field::inst( "translate->>'de'" )
                ->set(false)
                ->Validator(function ( $val, $data,$a ) use ($db){
                    $tab=array();
                    $tab["de"]=$val;
                    $vj=json_encode($tab,JSON_HEX_APOS);                                              //for string with apostrophe
                    $sql="update btranslate set
                    translate=translate::jsonb||'".$vj."'::jsonb
                    where id_translate=".$data["id_translate"];
                    $result =
                    $db ->raw()
                
                    ->exec($sql);
                    return true;
                }),Field::inst( "translate->>'en'" )
                ->set(false)
                ->Validator(function ( $val, $data,$a ) use ($db){
                    $tab=array();
                    $tab["en"]=$val;
                    $vj=json_encode($tab,JSON_HEX_APOS);
                    $sql="update btranslate set
                    translate=translate::jsonb||'".$vj."'::jsonb
                    where id_translate=".$data["id_translate"];
                    $result =
                    $db ->raw()
                
                    ->exec($sql);
                    return true;
                }),Field::inst( "translate->>'fr'" )
                ->set(false)
                ->Validator(function ( $val, $data,$a ) use ($db){
                    $tab=array();
                    $tab["fr"]=$val;
                    $vj=json_encode($tab,JSON_HEX_APOS);
                    $sql="update btranslate set
                    translate=translate::jsonb||'".$vj."'::jsonb
                    where id_translate=".$data["id_translate"];
                    $result =
                    $db ->raw()
                
                    ->exec($sql);
                    return true;
                })
           
        )->debug(true)  
        ->process( $_POST )
        ->json();

PS : the submit is "allIfChanged"

allan

Very nice - thanks for sharing this! However, doesn't jsonb also accept a valid JSON string? In which case you could use a custom set formatter to call json_encode() on the value?

Allan