Manipulate sql database values before being submitted to the client

itram

Is it possible to manipulate the whole database raws on the php server side (basically using raw sql commands), before loading the data and submit them to the client from the php controller?

rf1234

Sure, anything is possible:
- getFormatters with SQL (see example below)
- postGet event handlers with SQL: Just pass $data by reference ("&$data") and do whatever you like to manipulate it

Like this for example (just for illustration purposes)

->on( 'postGet', function ( $e, &$data, $id ) use ( $db ) { 
    $data = array_unique($data, SORT_REGULAR);
    $stmt = ('SELECT DISTINCT govdept_id 
                FROM govdept_has_user  
               WHERE user_id = :user_id
                 AND role IN ("Principal", "Administrator")');  
    $result = $db ->raw()
                  ->bind(':user_id',$_SESSION['id'])
                  ->exec($stmt);
    $row = $result->fetchAll(PDO::FETCH_ASSOC);
    $govdeptIdArray = [];
    foreach ($row as $val) {
       $govdeptIdArray[] = $val["govdept_id"];
    }

    foreach ($data AS $key => $val) {
        //for non-deleted users we need to check whether the updater is 
        //not a gov user. if so unset the record!
        $stmt = ('SELECT COUNT(*) AS is_creditor_user
                    FROM creditor_has_user 
                   WHERE user_id  = :user_id');  
        $result = $db ->raw()
                      ->bind(':user_id',    $val["log"]["user_id"])
                      ->exec($stmt);
        $isCred = $result->fetch(PDO::FETCH_ASSOC);
        if ( (bool)$isCred["is_creditor_user"] ) {
            unset($data[$key]);
        } elseif ( ! in_array($val['dept_id'], $govdeptIdArray) ) {
            unset($data[$key]);
        } elseif ( mb_strpos( $val["changer"], "gelöscht" ) === false &&
                   mb_strpos( $val["changer"], "deleted" )  === false    ) {
    //for non-deleted users we need to check whether they have an authorization for
    //the respective department at all due to the auto-increment problem with Inno DB!!
    //id's can be duplicates in the log: see: 
    //https://stackoverflow.com/questions/18692068/will-mysql-reuse-deleted-ids-when-auto-increment-is-applied
            $stmt = ('SELECT COUNT(*) AS user_has_dept
                        FROM govdept_has_user
                       WHERE user_id    = :user_id
                         AND govdept_id = :govdept_id');  
            $result = $db ->raw()
                          ->bind(':user_id',    $val["log"]["user_id"])
                          ->bind(':govdept_id', $val["dept_id"])
                          ->exec($stmt);
            $usrDept = $result->fetch(PDO::FETCH_ASSOC);
            if ( ! (bool)$usrDept["user_has_dept"] ) {
                unset($data[$key]);
            }
        }
    }

    $data = array_values($data);
    array_multisort( array_column($data, "contract_id"), SORT_ASC,
                    array_column($data, "update_time"), SORT_ASC,
                    $data );
})

Field::inst( 'ctr_govdept.id AS ctr_govdept.has_contracts' )->set( false )
    ->getFormatter( function($val, $data, $opts) use ($db){
        $result = $db->raw()
            ->bind( ':fk', $val )  
            ->exec( 'SELECT COUNT(*) AS ctrCount 
                       FROM ctr_has_ctr_govdept  
                      WHERE ctr_govdept_id = :fk' );
        $row = $result->fetch(PDO::FETCH_ASSOC);
        if ( (bool)$row["ctrCount"] ) {
            return 1;
        }
        return 0;
    }), 

itram

Thanks for pointing me to the solution.
I did as you suggested but on 'preGet', which seems to work fine.
Just for illustration:

    ->on('preGet', function ($editor, $id) {
          $editor->db()->raw()
            ->bind(':colStatus', 'test')
            ->exec('UPDATE `dbTable` SET `colStatus`= :colStatus WHERE `id`= 3');
    })

rf1234

Sure, any event is ok for this. I use "validatedEdit" and "validatedCreate" a lot: At that point you know the UPDATE or INSERT done by Editor will be fine and you can make certain required updates beforehand, e.g. more complex link table inserts and the like.