Where condition in autocomplete field

KeepMoving

The autocomplete field is working good but now I have to show users with specific profile using a where condition
I've read the documentation and Im doing somethink like this and doesnt work.

Field:

{               
      label:" Usuario",
      name:"altaestudios.IdUsuario",
      type:"autoComplete",
      placeholder:"Elija usuario"
 },

 Field::inst( 'altaestudios.IdUsuario' )
                    ->options( Options::inst()
                        ->table( 'usuario'  )
                        ->value( 'IdUsuario' )
                        ->label( 'nombre'   )
            -where('perfil',2)  // where condition not working
                    )
                    ->validator( 'Validate::dbValues' ),

                Field::inst( 'usuario.nombre'),
                    Field::inst('usuario.apellido'),

table usuario has "perfil" field

Thanks for help

allan

Could you enable the server-side debug mode and show me the SQL that Editor generates?

Thanks,
Allan

KeepMoving

debugSql It says "SELECT DISTINCT IdUsuario as 'IdUsuario', nombre as 'nombre' FROM usuario WHERE perfil IS NULL "

[{,…}, {,…}, {,…}, {,…}, {,…}]
0
:
{,…}
bindings
:
[]
query
:
"SELECT  `altaestudios`.`IdAltaEstudios` as 'altaestudios.IdAltaEstudios', `altaestudios`.`FechaEstudio` as 'altaestudios.FechaEstudio', `altaestudios`.`IdUsuario` as 'altaestudios.IdUsuario', `usuario`.`nombre` as 'usuario.nombre', `usuario`.`apellido` as 'usuario.apellido', `altaestudios`.`IdLaboratorio` as 'altaestudios.IdLaboratorio', `laboratorio`.`NombreLaboratorio` as 'laboratorio.NombreLaboratorio', `altaestudios`.`activo` as 'altaestudios.activo', `altaestudios`.`IdTipoEstudio` as 'altaestudios.IdTipoEstudio', `tipoestudio`.`ClaveEstudio` as 'tipoestudio.ClaveEstudio', `altaestudios`.`archivo` as 'altaestudios.archivo' FROM  `altaestudios` LEFT JOIN `usuario` ON `altaestudios`.`IdUsuario` = `usuario`.`IdUsuario`  LEFT JOIN `laboratorio` ON `altaestudios`.`IdLaboratorio` = `laboratorio`.`IdLaboratorio`  LEFT JOIN `tipoestudio` ON `altaestudios`.`IdTipoEstudio` = `tipoestudio`.`IdTipoEstudio` "
1
:
{,…}
bindings
:
[]
query
:
"SELECT DISTINCT  `IdUsuario` as 'IdUsuario', `nombre` as 'nombre' FROM  `usuario` WHERE `perfil` IS NULL "
2
:
{,…}
bindings
:
[]
query
:
"SELECT DISTINCT  `IdLaboratorio` as 'IdLaboratorio', `NombreLaboratorio` as 'NombreLaboratorio' FROM  `laboratorio` WHERE `IdLaboratorio` IS NULL "
3
:
{,…}
bindings
:
[]
query
:
"SELECT DISTINCT  `IdTipoEstudio` as 'IdTipoEstudio', `ClaveEstudio` as 'ClaveEstudio' FROM  `tipoestudio` "
4
:
{,…}
bindings
:
[]
query
:
"SELECT  `IdArchivo` as 'IdArchivo', `NombreArchivo` as 'NombreArchivo', `Tamano` as 'Tamano', `web_path` as 'web_path', `local_path` as 'local_path' FROM  `archivo` "

allan

Thanks!

-where('perfil',2)

There is a typo there - a missing >. I would have expected that to show as a syntax error, but it would be worth checking. If it wasn't then can you copy and paste the exact code you are using?

Allan

KeepMoving

Yea I see that but still doesnt work, I get the same Query in debugSQl

<?php

/*
 * Script principal de Editor
 */

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

// Alias Editor
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;
$db->sql( "SET NAMES 'utf8'" );
// Libreria para captura, lectura y edicion de datos
Editor::inst( $db, 'altaestudios', 'IdAltaEstudios' )
    ->debug(true)
    ->fields(
    
        Field::inst( 'altaestudios.FechaEstudio' )
            ->validator( 'Validate::dateFormat', array(
                "format"  => Format::DATE_ISO_8601,
                "message" => "Ingrese un formato válido de fecha yyyy-mm-dd"
            ) )
            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
    
            // se crea el campo que se va a relacionar
                Field::inst( 'altaestudios.IdUsuario' )
    
            // se agregan las opciones del objeto que se va a llenar dinamicamente
                    ->options( Options::inst()
                        ->table( 'usuario'  )
                        ->value( 'IdUsuario' )
                        ->label( 'nombre'   )
                ->where('perfil', 2)
                    )
                    
                    // validador de datos
                    ->validator( 'Validate::dbValues' ),
                // campo de el left join
                Field::inst( 'usuario.nombre'),
                    Field::inst('usuario.apellido'), //Join para render de apellido
          
        Field::inst( 'altaestudios.IdLaboratorio')
                 ->options( Options::inst()
                        ->table( 'laboratorio')
                        ->value( 'IdLaboratorio' )
                        ->label( 'NombreLaboratorio')
                        
                    )
                    // validador de datos
                    ->validator( 'Validate::dbValues' ),
                // campo de el left join
                 Field::inst( 'laboratorio.NombreLaboratorio'),

    
    
        Field::inst( 'altaestudios.activo'),
    
        Field::inst( 'altaestudios.IdTipoEstudio')
                    ->options( Options::inst()
                        ->table( 'tipoestudio'  )
                        ->value( 'IdTipoEstudio' )
                        ->label( 'ClaveEstudio'   )
                    )
                    // validador de datos
                    ->validator( 'Validate::dbValues' ),
                // campo de el left join
                Field::inst('tipoestudio.ClaveEstudio'),
    
    
                Field::inst( 'altaestudios.archivo' )
                ->setFormatter( 'Format::ifEmpty', null )
                ->upload( Upload::inst( $_SERVER['DOCUMENT_ROOT'].'/archivos/estudioNumero__ID__.__EXTN__' )
                    ->db( 'archivo', 'IdArchivo', array(
                        'NombreArchivo'    => Upload::DB_FILE_NAME,
                        'Tamano'    => Upload::DB_FILE_SIZE,
                        'web_path'    => Upload::DB_WEB_PATH,
                        'local_path' => Upload::DB_SYSTEM_PATH
                    ) 
                )

                    ->allowedExtensions( array( 'pdf'), "Archivo no válido" )
            )
                

    )

//        ->leftJoin( 'archivo', 'altaestudios.archivo', '=', 'archivo.IdArchivo')
        ->leftJoin( 'usuario', 'altaestudios.IdUsuario', '=', 'usuario.IdUsuario' )
        ->leftJoin( 'laboratorio', 'altaestudios.IdLaboratorio', '=', 'laboratorio.IdLaboratorio')
        ->leftJoin( 'tipoestudio', 'altaestudios.IdTipoEstudio', '=', 'tipoestudio.IdTipoEstudio')
    ->process( $_POST )
    ->json();

thanks for your help

allan

Ah. I've just remembered that the Options class needs a function to be given (see the documentation here) - e.g. you might use:

->where( function ($q) {
    $q->where( 'perfil', 2 );
} )

Allan

KeepMoving

Hey allan any idea why when I select name from autocomplete I get the Id instead name?

allan

Sounds like the label and value might be getting missed up somewhere. Where do you get the Id - does it display where the able should be?

Allan

KeepMoving

When I add or edit a record the search filter display name but when I select I get Id instead name

Pic.

PabloIE

I think joins are wrong. I would check with this example :

https://editor.datatables.net/examples/advanced/joinLinkTable.html

sry got not enough time to dig in deeper.It's late here.

allan

What is the library you are using for the AutoComplete? Apologies if you said before, I can't see it above.

Allan

PabloIE

Sry if i'm wrong but i think this join

->leftJoin( 'usuario', 'altaestudios.IdUsuario', '=', 'usuario.IdUsuario' )

should be :

->leftJoin( 'altaestudios', 'altaestudios.IdUsuario', '=', 'usuario.IdUsuario' )

Hope this helps.

KeepMoving

It was my mistake, thanks for your help