php new field not in the database

carrarachristophe · August 30

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 · August 30

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 · September 5

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 · September 5

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

carrarachristophe · September 7

Thank you Allan,
I prefer it on the server-side (and your code works perfectly), because I intended to add a join clause in order to, based on the 'country' (ex: GB), retrieve its name (Ex: United Kingdom).
Is it technically possible with a field that is not part of the DB?
I am getting : DataTables warning: table id=tms_sw_isobic - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'country' in 'on clause'
when I use:

        new Field('SUBSTRING(isobic.BIC, 4, 2)', 'country')
          ->set(false),
        Field::inst( 'geo_pays.pays' ),
        Field::inst( 'geo_pays.pays_code' )
    )
    ->leftJoin( 'geo_pays', 'geo_pays.pays_code', '=', 'country' )
    ->process( $_POST )
    ->json();

allan · September 7

No, because of how the SQL builder works for the PHP libraries, it doesn't actually do the aliasing to country in the SQL, but rather in PHP. So I'm afraid that will not work as you are expecting.

To do something like that, you'd need to use a VIEW for reading the data.

Allan

carrarachristophe · September 12

Thank you allan.

php new field not in the database

php new field not in the database

Answers