DB Not Updated When Using Options

BoSmith

Hi,
I have been able to use the code below with no problems, ie. reading and writing to db and displaying data in Editor. But when I did a further refinement of the data as shown in the examples it no longer updates the DB, but the data is displayed in the Editor as desired. The only field that is not updated in the DB is the one with the added options to it.
I thank you in advance for any help.

Example from Manual:

Field::inst( 'users.site' )
    ->options( 'sites', 'id', 'name', function ($q) {
        $q->where( 'name', 'L%', 'LIKE' );
    } );

My Code:

public function classes_process($post)
    {
        Editor::inst($this->editorDb, $this->table_name, 'id')
            ->fields(
                Field::inst('courses.title')
                    ->validator('Validate::notEmpty'),
                Field::inst($this->table_name . '.course_id')
                    ->options('courses', 'id', 'title'),

                Field::inst('users.last_name')
                    ->validator('Validate::notEmpty'),
                Field::inst($this->table_name . '.teacher_id')
**                    ->options('users', 'level', 'last_name', function ($q) {
                        $q->where('level', '%t%', 'LIKE');
                    }),**

                Field::inst('cohorts.cohort_name'),
                Field::inst($this->table_name . '.cohort_id')
                    ->options('cohorts', 'id', 'cohort_name')
            )
            ->leftJoin('courses', 'courses.id', '=', $this->table_name . '.course_id')
            ->leftJoin('users', 'users.id', '=', $this->table_name . '.teacher_id')
            ->leftJoin('cohorts', 'cohorts.id', '=', $this->table_name . '.cohort_id')
            ->process($_POST)
            ->json();
    }

allan

Is it the course_id or teacher_id field which isn't woking? Or both?

Are you able to give me a link to the page so I can take a look and see what is going wrong please?

Thanks,
Allan

BoSmith

Hi,
Thanks for the reply, it is the teacher_id that is not working correctly. The code without options just shows all of my users. The code with the options shows only the teachers as wanted. But the DB is not updated with the teacher_id on Edit or New.
Here is the JS code for the editor.

(function ($) {

    $(document).ready(function () {
        var editor = new $.fn.dataTable.Editor({
            ajax: 'info_process',
            serverSide: false,
            processing: true,
            table: '#classes',
            fields: [
                {label: "Course:", name: "classes.course_id", type: "select"},
                {label: "Teacher:", name: "classes.teacher_id", type: "select"},
                {label: "Cohort:", name: "classes.cohort_id", type: "select"}
            ],

        });


        //added for custom error messages on delete forms
        editor.on( 'postSubmit', function ( e, json, data, action ){
            if(json.error != null){
                json.error = "Error: Record cannot be deleted, used in other table(s)";
                return false;
            }
            return true;
        });

        var fname = 'Classes';
        var table = $('#classes').DataTable({
            dom: 'Bfrtip',
            ajax: 'info_process',
            serverSide: false,
            processing: true,
            scrollY: 200,
            scrollX: true,
            columns: [
                {data: "courses.title"},
                {data: "users.last_name"},
                {data: "cohorts.cohort_name"}
            ],
            select: true,
            lengthChange: false,
            buttons: [
                {extend: 'create', editor: editor},
                {extend: 'edit', editor: editor},
                {
                    extend: "selectedSingle",
                    text: 'Duplicate',
                    action: function (e, dt, node, config) {
                        // Place the selected row into edit mode (but hidden),
                        // then get the values for all fields in the form
                        var values = editor.edit(
                            table.row({selected: true}).index(),
                            false
                        )
                            .val();

                        // Create a new entry (discarding the previous edit) and
                        // set the values from the read values
                        editor
                            .create({
                                title: 'Duplicate record',
                                buttons: 'Create from existing'
                            })
                            .set(values);
                    }
                },
                {extend: 'remove', editor: editor},
                {
                    extend: 'collection',
                    text: 'Export',
                    buttons: [
                        'copy',
                        {extend: 'pdf', filename: '*', title: fname},
                        {extend: 'excel', filename: '*', title: fname},
                        {extend: 'csv', filename: '*', title: fname},
                        'print'
                    ]
                }
            ]
        });
    });

}(jQuery));

And this is the code for processing the AJAX data

    public function classes_process($post)
    {
        Editor::inst($this->editorDb, $this->table_name, 'id')
            ->fields(
                Field::inst('courses.title')
                    ->validator('Validate::notEmpty'),
                Field::inst($this->table_name . '.course_id')
                    ->options('courses', 'id', 'title'),

                Field::inst('users.last_name')
                    ->validator('Validate::notEmpty'),
            
                // THIS ONE SHOWS ALL
                Field::inst($this->table_name . '.teacher_id')
                    ->options('users', 'id', 'last_name'),
            
                  // THIS ONE SHOWS ONLY TEACHERS, BUT NOT SAVE TO DB
//                Field::inst($this->table_name . '.teacher_id')
//                    ->options('users', 'level', 'last_name', function ($q) {
//                        $q->where('level', '%t%', 'LIKE');
//                    }),

                Field::inst('cohorts.cohort_name'),
                Field::inst($this->table_name . '.cohort_id')
                    ->options('cohorts', 'id', 'cohort_name')
            )
            ->leftJoin('courses', 'courses.id', '=', $this->table_name . '.course_id')
            ->leftJoin('users', 'users.id', '=', $this->table_name . '.teacher_id')
            ->leftJoin('cohorts', 'cohorts.id', '=', $this->table_name . '.cohort_id')
            ->process($_POST)
            ->json();
    }

I have tried several methods with little (no) success.
Thank you

allan

That's very odd indeed! If you select a teacher for the full list, does that save to the database?

I'll try to create an example here and debug it locally.

Allan

BoSmith

Yes it does. All works perfectly, just have to deal with selecting the correct teacher out of a couple hundred users.
Thanks for helping

BoSmith

I think I got it to work
I have been testing it and it updates with just teacher view.
Just teacher being displayed I mean.

Field::inst($this->table_name . '.teacher_id')
                    ->options('users', 'id', 'last_name', function ($q) {
                        $q->where('level', '%t%', 'LIKE');
                    }),

All I did was changed the 'level' in options for 'id' instead. Works like a charm.

Thanks for looking in to it. Is great tool set, the trick is to use it correctly.
Thanks
Bo

allan

Hi Bo,

Thanks for posting back. Great to hear you've got it working now!

Regards,
Allan