Issue filtering in dropdowns

si08789

I have a issue filtering in 2 dropdowns. The idea is to restrict the data given in the dropdown with the join given in the server side.

In this case I get the 5 records in the dropdown of the table "grades_types" but with the where given in the server side ('avgrades_types.idoficial_study_plan', $_GET['oficial_study_plan']) I should only get 1 possible option (I have checked that oficial_study_plan is received correctly).

PHP

Editor::inst( $db, 'avgrades_classe_subjects' )
    ->field(
        Field::inst( 'avgrades_classe_subjects.id' ),
        Field::inst( 'avgrades_classe_subjects.sort' ),
        Field::inst( 'avgrades_classe_subjects.idoficial_study_plan' )
            ->set( Field::SET_BOTH )
            ->setValue( $_GET['oficial_study_plan'] ),
        Field::inst( 'avgrades_classe_subjects.oficial_course' )
            ->set( Field::SET_BOTH )
            ->setValue( $_GET['oficial_course'] ),
        Field::inst( 'avgrades_classe_subjects.idgrades_subject' )
            ->options( 'avgrades_subjects', 'id', 'name' ),
            Field::inst( 'avgrades_subjects.name' ),
        Field::inst( 'avgrades_classe_subjects.idgrades_type' )
            ->options( 'avgrades_types', 'id', 'name' ),
            Field::inst( 'avgrades_types.name' ),
        Field::inst( 'avgrades_classe_subjects.idgrades_category' )
            ->options( 'avgrades_categories', 'id', 'name' ),
            Field::inst( 'avgrades_categories.name' ),
        Field::inst( 'avgrades_classe_subjects.idgrades_subcategory' )
            ->options( 'avgrades_subcategories', 'id', 'name' ),
            Field::inst( 'avgrades_subcategories.name' )
    )
    ->where('avgrades_classe_subjects.idoficial_study_plan', $_GET['oficial_study_plan'])
    ->where('avgrades_classe_subjects.oficial_course', $_GET['oficial_course'])
    ->where('avgrades_subjects.idschool', $_GET['school'])
    ->where('avgrades_types.idoficial_study_plan', $_GET['oficial_study_plan'])
    ->leftJoin( 'avgrades_subjects', 'avgrades_subjects.id', '=', 'avgrades_classe_subjects.idgrades_subject' )
    ->leftJoin( 'avgrades_categories', 'avgrades_categories.id', '=', 'avgrades_classe_subjects.idgrades_category' )
    ->leftJoin( 'avgrades_subcategories', 'avgrades_subcategories.id', '=', 'avgrades_classe_subjects.idgrades_subcategory' )
    ->leftJoin( 'avgrades_types', 'avgrades_types.id', '=', 'avgrades_classe_subjects.idgrades_type' )
    ->process($_POST)
    ->json();

JAVASCRIPT (client side)

    $(document).ready(function() {

        editor = new $.fn.dataTable.Editor( {
            ajax: "../includes/datatables/tables/avgrades_classes_subjects.php?school=<?=$school?>&oficial_study_plan=<?=$oficial_study_plan?>&oficial_course=<?=$oficial_course?>",
            table: "#avgrades_classe_subjects",
            i18n: { <?=$language['datatables_editor']?> },
            fields: [ {
                    label: "<?=LABEL_ORDEN?>:",
                    name: "avgrades_classe_subjects.sort"
                }, {
                    label: "<?=LABEL_NOMBRE?>:",
                    name: "avgrades_classe_subjects.idgrades_subject",
                    type: "select"
                }, {
                    label: "<?=LABEL_TIPO?>:",
                    name: "avgrades_classe_subjects.idgrades_type",
                    type: "select"
                }, {
                    label: "<?=LABEL_CATEGORIA?>:",
                    name: "avgrades_classe_subjects.idgrades_category",
                    type: "select"
                },{
                    label: "<?=LABEL_SUBCATEGORIA?>:",
                    name: "avgrades_classe_subjects.idgrades_subcategory",
                    type: "select"
                }
            ]
        } );

        // Activate an inline edit on click of a table cell
        $('#avgrades_classe_subjects').on( 'click', 'tbody td:not(:first-child)', function (e) {
            editor.inline( this, {
                submitOnBlur: true
            } );
        } );

        $('#avgrades_classe_subjects').dataTable( {
            dom: "Tfrtip",
            ajax: {
                url: "../includes/datatables/tables/avgrades_classes_subjects.php?school=<?=$school?>&oficial_study_plan=<?=$oficial_study_plan?>&oficial_course=<?=$oficial_course?>",
                type: 'POST'
            },
            columns: [
                { data: null, defaultContent: '', orderable: false },
                { data: "avgrades_classe_subjects.sort" },
                { data: "avgrades_subjects.name", editField: "avgrades_classe_subjects.idgrades_subject" },
                { data: "avgrades_types.name", editField: "avgrades_classe_subjects.idgrades_type" },
                { data: "avgrades_categories.name", editField: "avgrades_classe_subjects.idgrades_category" },
                { data: "avgrades_subcategories.name", editField: "avgrades_classe_subjects.idgrades_subcategory" }
            ],
            order: [ 1, 'asc' ],
            tableTools: {
                sRowSelect: "os",
                sRowSelector: 'td:first-child',
                aButtons: [
                    { sExtends: "editor_create", editor: editor },
                    { sExtends: "editor_edit",   editor: editor },
                    { sExtends: "editor_remove", editor: editor }
                ]
            },
            "language": {
                        "url": "../includes/idioma/datatables/<?=$language['datatables']?>"
        }
        } );
    } );

Could you please tell me I am doing anything wrong?

allan

So the problem here is that the Field->options() method won't automatically apply the where conditions from the main Editor instance. Rather you would need to use a closure function for the options to query the database and get the options that you want.

There is an example of options() being used in a closure function here. Click the "Server script" tab below the table - lines 28 to 42 are the ones of interest.

Allan