PHP Library Field::inst()->Options(from, code, display) with Where clause.

Mike Storey

I have a situation where the pick list in a select control is a sub-set of the values in a validation table. This means I need to add a where qualifier when selecting a list of valid options, and the ->options constructor does not appear to support. Is there a work-around or some way to extend the functionality to accomplish this? Can a where be added to the field options dynamically?

Thanks for any help.

allan

What you would need to do is use ->options() with an anonymous function and get the list of options from the database directly rather than having Editor do it for you. There is an example of that here (click the "Server script" tab).

Allan

Mike Storey

I'm still having some trouble, probably a syntax error that I can't seem to find. The php is reporting a SQL error, "Unknown column '0' in 'where clause'" and I'm stumped where it's getting the 0 from. Here's my code, as always, any help is greatly appreciated.

Editor::inst( $db, 'condition', 'idcondition' )
    ->fields(
        Field::inst( 'condition.idcondition' ),
        Field::inst( 'condition.idsituation' ),
        Field::inst( 'condition.type' )->options( 'codesCtype', 'code', 'meaning' ),
        Field::inst( 'condition.operator' )->options( 'codesOperator', 'code', 'meaning' ),
        Field::inst( 'condition.value' ),
        Field::inst( 'attribute.name' ),
        Field::inst( 'codesOperator.meaning'  ),
        Field::inst( 'codesCtype.meaning'  ),
        Field::inst( 'situation.idgroup'  ),
        Field::inst( 'condition.idattribute' )
            ->options( function () use ( $db ) {
            $attrList = $db->select( 
                    'attribute', 
                    ['attribute.idattribute', 'attribute.name'], 
                    ['attribute.idgroup', $_POST['groupId'], "=" ] );
            $out = array();
            while ( $row = $attrList->fetch() ) {
                $out[] = array(
                        "value" => $row['attribute.idattribute'],
                        "label" => $row['attribute.name']
                );
            }       
            return $out;
        } )     
    )
    ->where( 'condition.idsituation', $_POST['situationId'] )
    ->leftJoin( 'situation', 'situation.idsituation', '=', 'condition.idsituation' )
    ->leftJoin( 'attribute', 'attribute.idattribute', '=', 'condition.idattribute' )
    ->leftJoin( 'codesOperator', 'codesOperator.code', '=', 'condition.operator' )
    ->leftJoin( 'codesCtype', 'codesCtype.code', '=', 'condition.type' )
    ->process( $_POST )
    ->json();

The field attribute.idgroup is valid, and groupId is passed on the request as a valid ID.

allan

I think the problem is with the where condition. Try this instead:

[ 'attribute.idgroup' => $_POST['groupId'] ]

An array of three indexed options is not something the where() method will accept (which is what select() calls for the third parameter).

Allan

Mike Storey

That worked! Thanks!

BTW, the documentation for the $db->select references the query->where method, and the documentation here: http://editor.datatables.net/docs/Editor-1.3.3/php/class-DataTables.Database.Query.html indicates that where method uses 4 parameters. Either I looked in the wrong place, in which case you may want to update the $db->select doc with a link to the correct where method, or the query->where doc needs updating.

allan

That needs to be clarified in the docs. I'll do so! Thanks for the feedback.

Allan