select options from in the same table

ing.julianavilan@gmail.com

As I use a select optiom from in the same table, I have a table that stores for each id_user a id_validator, the id_validator in turn corresponds to an id of a user from the same table. In other words, users will be able to select the person who will be their validator from the list in the users table. The problem I have is that I have to call the "user" table to load the data and in turn I have to call the "user" table to show the validator list, I manage to do part of this, but for some reason the data is not saved, I have modified many options and I can not find the solution:

include( "lib/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,
    DataTables\Editor\ValidateOptions;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'usuario', 'idusuario' )
    ->fields(
        Field::inst( 'usuario.id_validador')
        ->setValue('usr2.id_validador')
        ->validator( Validate::dbValues() ),
        //->get( false ),
        Field::inst( 'usuario.nombre' )
            ->validator( Validate::notEmpty() )
            ->validator( Validate::minLen(1) ),
        Field::inst( 'usuario.apellido' )
            ->validator( Validate::notEmpty() )
            ->validator( Validate::minLen(1) ),
        Field::inst( 'usuario.correo' )
        ->validator( Validate::notEmpty())
        ->validator( Validate::email() ),
        Field::inst( 'usuario.clave' )
                ->get( false ) // never read from the db
                ->setFormatter( function ( $val, $data ) {
                    return md5( $val );
                } ),
            
        Field::inst( 'usuario.id_unitat' )
            ->options( Options::inst()
                ->table( 'unitat_int' )
                ->value( 'unitat_int.id')
                ->label( 'unitat_int.nom')
            )
        ->validator( Validate::notEmpty())
        ->validator( Validate::dbValues() ),
        Field::inst( 'unitat_int.nom'),
        Field::inst( 'usr2.id_validador')
            ->set( false ) // never read from the db
            ->options( Options::inst()
                ->table( 'usuario' )
                ->value( 'usuario.idusuario')
                ->label( 'usuario.correo')
            )
        ->validator( Validate::notEmpty()),
        Field::inst( 'usr2.correo' ),
        Field::inst( 'usr2.idusuario' ),
        Field::inst( 'usuario.rol' )
            ->options( Options::inst()
            ->table( 'rol' )
            ->value( 'rol.idrol')
            ->label( 'rol.rol')
        )
        ->validator( Validate::notEmpty()),
        Field::inst( 'rol.rol' )
        ->validator( Validate::dbValues() 
        )
    )
        ->on('preEdit', function ( $editor,$id, $values ) {
             if (!isset( $values['usuario']['clave'] ) || $values['usuario']['clave'] === '' ) {
                $editor
                ->field( 'usuario.clave' )
                ->set( false )
                ->get( false );
        /*  $editor
            ->field( 'usuario.id_validador' )
            ->setValue($values['usr2']['id_validador'])
            ->validator( Validate::notEmpty());*/
            }
         })
         ->on( 'preCreate', function ( $editor, $values ){
            $editor
            ->field( 'usuario.clave' )
            ->setValue($values['usuario']['clave'])
            ->validator( Validate::notEmpty() )
            ->validator( 'Validate::minMaxNum', array(
                'min' => 1000,
                'max' => 9999999999,
                'message' => 'introduïu una contrasenya numèrica de min 4 dígits'
            ) )
            ->get(false);
        /*  $editor
            ->field( 'usuario.id_validador' )
            ->setValue($values['usr2']['id_validador'])
            ->validator( Validate::notEmpty());
            ;*/
         })
            
    ->leftJoin( 'unitat_int', 'unitat_int.id', '=', 'usuario.id_unitat')
    ->leftJoin( 'usuario as usr2', 'usr2.id_validador', '=', 'usuario.idusuario')
    ->leftJoin( 'rol', 'usuario.rol', '=', 'rol.idrol')
    ->process( $_POST )
    ->json();

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

Lapointe

Hi
I do not understand... Why do you use a usr2 ?

Just get the userlist as a select from the usuario table and set it in the usuario field

   ->fields(
        Field::inst( 'usuario.id_validador')
            ->options( Options::inst()
                ->table( 'usuario' )
                ->value( 'usuario.idusuario')
                ->label( 'usuario.correo')
            )
        ->validator( Validate::notEmpty()),

You may edit one table at a time, not ?

allan

Hi,

I think Lapointe is spot on! You are performing a self -referencing join, but it looks like your fields are slightly confused.

What I think is wrong in your code above is the Field::inst( 'usuario.id_validador'). I think it should be:

Field::inst( 'usuario.id_validador')

Also remove:

        Field::inst( 'usuario.id_validador')
        ->setValue('usr2.id_validador')
        ->validator( Validate::dbValues() ),

This assumes that you want to set the id_validador value to be a value in the usuario table.

Allan