Custom Where Clause in Select

alf007

Hi, I have the following multiple condition code to populate a select inside my datable. But I'm getting more options that I need. The
problem is in the:

$q->where( 'idFraccionamientos', $_SESSION['fraccionamiento'], '=' )

It does not respect this condition. I'm getting options that have different idFraccionamientos value. Example: There are only 4 records that have idFraccionamientos = 1, but the option list gives me 6 options.

Field::inst( 'colonos.idUsuarios' )
    ->validator( 'Validate::notEmpty',
    array(
    "message" => "Campo obligatorio"
    ))
    ->options( 'usuarios', 'idUsuarios', 'usuario', function ($q) {
        $q->where( 'idFraccionamientos', $_SESSION['fraccionamiento'], '=' )
        ->and_where( function ( $r ) {
            $r->where( 'rol', 'Colono' );            
        } )
        ->or_where( function ( $z ) {
            $z->where( 'rol', 'Presidente' );
        } );
    } )
    ->validator( 'Validate::dbValues' ),
    Field::inst( 'usuarios.usuario' )

Hope you can help me once again.

bindrid

From what I see you wrote ( (idFraccionamientos == $_SESSION['fraccionamiento'], and rol == colono) or (rol == Presidenta))

allan

Agreed - it sounds like a grouping issue. The Query->where_group() method can be used to set the grouping to match what you want.

Allan

alf007

Thanks for the replay, how can I use the where_group()?

I have the following code but it doesn't work.

$query->where_group( function ($q) {
        $q->where( 'idFraccionamientos', $_SESSION['fraccionamiento'], '=' );
        $q->where( 'rol', 'Colono' );            
        $q->where( 'rol', 'Presidente' );
            } );

allan

If you click on the table row for the where_group documentation that I linked to above it will open and show more details about it, including an example.

Allan

alf007

Hi, I follow the example but it not seems to work, it shows and error.

->options( 'usuarios', 'idUsuarios', 'usuario', function ($q) {        
        $query->where_group( true,function ($q) {
      $q->where( 'idFraccionamientos', $_SESSION['fraccionamiento']  );
      $q->where( 'rol', 'Colono' );
    } );
    } )

DataTables warning: table id=colonos - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

allan

You don't have a $query variable so the error being returned from the server should really be stating that. Change $query->... to use the $q variable that you are passing into the function.

Allan