preGet not working

xulunwu

I want to change the field value of pregroupdata.status before datatable read from database. I use preGet event to implement. But it is not work.

There is my code.

<?php
header("Conten-Type:text/html;charset=utf-8");
/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../../php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'pregroupdata' )
    ->fields(
        Field::inst( 'pregroupdata.appdate' ),
        Field::inst( 'pregroupdata.status' )->set(Field::SET_BOTH),
        Field::inst( 'pregroupdata.setupdate' ),
        Field::inst( 'pregroupdata.pastoralid' )->validator ( 'Validate::notEmpty' )
                                   ->options ( Options::inst()
                                                ->table( 'pastoral' )
                                                ->value( 'id' )
                                                ->label( 'name' )
                                             ),
        Field::inst( 'pname.name' ),
        Field::inst( 'pregroupdata.zoneid' )->validator ( 'Validate::notEmpty' )
                               ->options ( Options::inst()
                                                ->table( 'zonedata' )
                                                ->value( 'id' )
                                                ->label( 'name' )
                                             ),
        Field::inst( 'zname.name' ),
        Field::inst( 'pregroupdata.leaderid' )->validator ( 'Validate::notEmpty' )
                                 ->options ( Options::inst()
                                                ->table( 'leader' )
                                                ->value( 'id' )
                                                ->label( 'name' )
                                             ),
        Field::inst( 'lname.name' ),
        Field::inst( 'lname.phone' ),
        Field::inst( 'lname.cellphone' ),
        Field::inst( 'lname.identity' ),
        Field::inst( 'pregroupdata.groupname' ),
        Field::inst( 'pregroupdata.gdate' ),
        Field::inst( 'pregroupdata.gtime' ),
        Field::inst( 'pregroupdata.gplace' ),
        Field::inst( 'pregroupdata.property' ),
        Field::inst( 'zlname.name'),
        Field::inst( 'zlname.phone'),
        Field::inst( 'zlname.cellphone'),
        Field::inst( 'zlname.identity' )
    )
    ->leftJoin('leader as lname', 'pregroupdata.leaderid','=','lname.id')
    ->leftJoin('zonedata as zname', 'pregroupdata.zoneid','=','zname.id')
    ->leftJoin('pastoral as pname', 'pregroupdata.pastoralid','=','pname.id')
    ->leftJoin('leader as zlname', 'zlname.id','=','zname.leaderid')
    ->on('preGet', function($editor, $id){
        $editor
            ->field('pregroupdata.status')
            ->setValue('test');
    })
    ->process( $_POST )
    ->json();

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Hi,

The Field->setValue() method sets what the value that should be written to the database should be. But in this case you aren't writing to the database, just getting from it, so perhaps Field->getValue() is what you want?

I'm not quite sure what the goal is here though. You could perhaps just use:

Field::inst( 'pregroupdata.status' )->getValue('test');

if you want it to just always be test.

Allan

xulunwu

Hi Allan,

I want to overwrite the value of pregroupdata.status on database before the data load from server side. So I use preGet event to do it. But it does not work when I refresh the page. Or I may misunderstand the preGet event.

allan

So every time you read from the database, you want to write to it as well?

You'd need to use something like:

>on('preGet', function($editor, $id){
  $editor->db()->update( ...table..., ...fields..., ...where... );
})

Full docs for the database methods are available here.

Allan

xulunwu

Every time it read date from database, I want to check the condition to update the value of the field .

But it shows warning message "DataTables warning: table id=example - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'field list'".

my code:
```
<?php
header("Conten-Type:text/html;charset=utf-8");
/*
* Example PHP implementation used for the index.html example
*/

// DataTables PHP library
include( "../../php/DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'pregroupdata' )
->fields(
Field::inst( 'pregroupdata.appdate' )->validator ( 'Validate::notEmpty',array(
'message'=>"請填寫申請日期！"
) ),
Field::inst( 'pregroupdata.status' ),
Field::inst( 'pregroupdata.setupdate' ),
Field::inst( 'pregroupdata.pastoralid' )->validator ( 'Validate::notEmpty' )
->options ( Options::inst()
->table( 'pastoral' )
->value( 'id' )
->label( 'name' )
),
Field::inst( 'pname.name' ),
Field::inst( 'pregroupdata.zoneid' )->validator ( 'Validate::notEmpty' )
->options ( Options::inst()
->table( 'zonedata' )
->value( 'id' )
->label( 'name' )
),
Field::inst( 'zname.name' ),
Field::inst( 'pregroupdata.leaderid' )->validator ( 'Validate::notEmpty' )
->options ( Options::inst()
->table( 'leader' )
->value( 'id' )
->label( 'name' )
),
Field::inst( 'lname.name' ),
Field::inst( 'lname.phone' ),
Field::inst( 'lname.cellphone' ),
Field::inst( 'lname.identity' ),
Field::inst( 'pregroupdata.groupname' )->validator ( 'Validate::notEmpty',array(
'message'=>"請填寫小組名稱！"
) ),
Field::inst( 'pregroupdata.gdate' ),
Field::inst( 'pregroupdata.gtime' ),
Field::inst( 'pregroupdata.gplace' ),
Field::inst( 'pregroupdata.property' ),
Field::inst( 'zlname.name'),
Field::inst( 'zlname.phone'),
Field::inst( 'zlname.cellphone'),
Field::inst( 'zlname.identity' ),
Field::inst( 'pregroupdata.delid'),
Field::inst( 'pregroupdata.allow' )
)
->leftJoin('leader as lname', 'pregroupdata.leaderid','=','lname.id')
->leftJoin('zonedata as zname', 'pregroupdata.zoneid','=','zname.id')
->leftJoin('pastoral as pname', 'pregroupdata.pastoralid','=','pname.id')
->leftJoin('leader as zlname', 'zlname.id','=','zname.leaderid')
->on('preGet', function($editor, $id){
$editor->db()->update( 'pregroupdata', array('status','testcontent'),
function ($q) {
$q->where( 'pastoralid', 2, '=');
}
);
})
->where('pregroupdata.allow',1,'=')
->where('pregroupdata.delid',0,'=')
->process( $_POST )
->json();

<?php > ``` ?>

allan

Could you add ->debug( true ) immediately before the ->process( $_POST ) call please? Then show me the JSON that the server is returning. That will show the SQL that is being generated.

Allan

xulunwu

{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S22]: 
Column not found: 1054 Unknown column '0' in 'field list'","data":[],"ipOpts":[],"cancelled":[],
"debugSql":[{"query":"UPDATE `pregroupdata` SET `0` = :0, `1` = :1 
WHERE (`pastoralid` = :where_1 )","bindings":[{"name":":0","value":"status","type":null},
{"name":":1","value":"testcontent","type":null},{"name":":where_1","value":2,"type":null}]}]}

This is the JSON that the server is returning.

allan

Thanks - I see the issue now. The field / value array you pass into update() should be an associative array:

array('status' => 'testcontent')

Allan

xulunwu

Thanks, Allen! The issue solved now.