php new field not in the database

carrarachristophe

Hello,
I need to, while keeping the original field from the db, add a new field (country) which is not in the db but depends on a field in the db "isobic.BIC".
Here is the inital code:

Editor::inst( $db, 'isobic', 'id' )
    ->fields(
        Field::inst( 'isobic.BIC' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' )))
            ->validator( Validate::maxLen( 11 ) )
            ->validator( Validate::unique( ValidateOptions::inst()
                ->message( 'Existe déjà' )))
    )

I tried the following:

Editor::inst( $db, 'isobic', 'id' )
    ->fields(
        Field::inst( 'isobic.BIC' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' )))
            ->validator( Validate::maxLen( 11 ) )
            ->validator( Validate::unique( ValidateOptions::inst()
                ->message( 'Existe déjà' ))),
        new Field( 'country' )
            ->getFormatter(function ($val, $data, $opts) {
                return substr($data['isobic.BIC'], 4, 2);
            })
            ->set(false),
    )

and

Editor::inst( $db, 'isobic', 'id' )
    ->fields(
        Field::inst( 'isobic.BIC' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' )))
            ->validator( Validate::maxLen( 11 ) )
            ->validator( Validate::unique( ValidateOptions::inst()
                ->message( 'Existe déjà' ))),
        new Field( 'country' )
            ->setValue( substr($data['isobic.BIC'], 4, 2))
            ->set(false),
    )

But I am getting an error message: "An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'country' in 'field list'".
Is there a way to achieve what I am trying to do?

Thanks and regards.

allan

Add ->get(false) and it will stop the script from attempting to read it from the DB. Alternatively, if you need there to be a value for that field in the JSON sent to the client-side, use ->getValue('myValue') on the Field instance (replacing the value with whatever it is you need of course).

Allan

carrarachristophe

Thank you Allan,
I tried the following:

        new Field( 'country' )
            ->set( FIELD::SET_CREATE )
            ->getValue( substr('isobic.BIC', 4, 2) )

but am getting ic (from isobic.BIC), so I tried:

        new Field( 'country' )
            ->set( FIELD::SET_CREATE )
            ->getValue( substr(Field::inst( 'isobic.BIC' ), 4, 2) )

but am getting the https://datatables.net/tn/7 error.

Any idea?

allan

A Field instance can only be a parameter for the Editor->field() or Editor->fields() methods. substr doesn't know what to do with a file instance!

Since you already have isobic.BIC being read and returned to the client-side, I'd suggest you just use that in a second column at the client-side - e.g. in columns or columnDefs:

{
  data: 'isobic.BIC',
  render: data => data.substr(4, 2)
}

Alternatively, if you did want to do it on the server-side:

new Field('SUBSTRING(isobic.BIC, 4, 2)', 'country')
  ->set(false)

Allan