How to show additional field from link table in Mjoin

Kuo7

I have 3 tables in an Mjoin relationship: The lecturercourserecords table is the link table for the lecturers and the courses table, where 1 lecturer can teach 1 or more courses. This is displayed well in Datatables where I have lecturers listed with the course(s). I can perform CRUD operations on the number of courses assigned to the lecturers however I want, but I kept running into error (DataTables warning: table id=lecturerCourseDetails - An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'lecturercourserecords.year_taught' in 'field list') when I tried to get the year_taught column of the lecturercourses table to display alongside lecturers and courses in Datatables.
Any help or pointer is appreciated.
Thanks in advance.

Server script

<?php
//DataTables PHP library to be used with Editor
    include('Editor-PHP-1.9.0/lib/DataTables.php');
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Mjoin,
        DataTables\Editor\Options,
        DataTables\Editor\Validate,
        DataTables\Editor\ValidateOptions;

    //use DataTables\Editor;
    Editor::inst($db, 'lecturers', 'ID')
        ->fields(
            Field::inst('lecturers.ID')
                ->options(Options::inst()
                    ->table('vNonCurrentLecturers') /* getting data from a view */
                    ->value('vlecturer')
                    ->label('vname')
                    )
                    ->validator(Validate::dbValues())
                    ->validator(Validate::notEmpty(ValidateOptions::inst()
                        ->message('Please select a lecturer.')
                    )
                ),
            Field::inst('lecturers.Name'),
            Field::inst('lecturercourserecords.year_taught')
        )
        ->join(
            Mjoin::inst('courses')
                ->link('lecturers.ID', 'lecturercourserecords.lecturer')                
                ->link('courses.Code', 'lecturercourserecords.course')
                ->order('name asc')
                ->fields(
                    Field::inst('Code')
                        ->validator('Validate::required')
                        ->options(Options::inst()
                            ->table('courses')
                            ->value('Code')
                            ->label('NameCourseCode')
                        ),
                    Field::inst('Name')
                )
        )
        ->process($_POST)
        ->json();

<?php
>
?>

Javascript

<script type="text/javascript" language="javascript">
                var editor; // use a global for the submit and return data rendering
             
                $(document).ready(function() {
                    editor = new $.fn.dataTable.Editor( {
                        ajax: "registrar_lecturersCoursesDetails.php",
                        table: "#lecturerCourseDetails",
                        fields: [ 
                            {
                                label: "Lecturer:",
                                name: "lecturers.ID",
                                type: "select",
                                placeholder: "Select a lecturer"
                            },
                            {
                                label: "Year:",
                                name: "lecturercourserecords.year_taught"
                            },
                            {
                                "label": "Course:",
                                "name": "courses[].Code",
                                "type": "checkbox"
                            }                   
                        ]
                  } );
             
                  $("#lecturerCourseDetails").DataTable( {
                      dom: "Bfrtip",
                      ajax: {
                          url: "registrar_lecturersCoursesDetails.php",
                          type: "POST"
                      },
                      serverSide: true,
                      order: [[ 0, 'asc' ]],
                      columns: [
                            { data: "lecturers.Name" },
                            { data: "lecturercourserecords.year_taught"},
                          { data: "courses", render: "[, ].Name" }
                      ],
                      /* disabling search on the second column (courses) because the courses column is client-side generated 
                      and since server-side processing is enabled, the server knows nothing about the courses! */
                      "columnDefs": [
                          {
                              "orderable": false,
                              "searchable": false,
                              "targets": 1  
                          }
                      ],
                      select: true,
                      buttons: [
                          { extend: "create", editor: editor },
                          { extend: "edit",   editor: editor },
                          { extend: "remove", editor: editor }
                      ]
                  });
                });
           </script>

Kuo7

EDIT: ...year_taught column of the lecturercourses table... should read ...year_taught column of the lecturercourserecords table...
I meant to say lecturercourserecords table and not lecturercourses table. Sorry.

allan

Hi,

I'm afraid there is no way to do that with the Editor PHP libraries at the moment. Sort of semi-intentionally because of the way Editor handles the link tables for Mjoin. What happens to the link table on edit is that it will delete the links and then readd them, rather than doing a diff. The result is that any meta information (other columns in the link table) is lost.

You could do a left join to the link table to get the extra information, but that wouldn't be one-to-many. The other option is that you could do another Mjoin to just the link table and pull the information out of it that way. If you don't need to be able to edit it, that's the way I would do it.

The best solution would really be for Mjoin to offer a leftJoin option itself. I'm sorry to say that it doesn't currently have that ability, although it is on the feature list!

Allan

Kuo7

Thank you Alan.
For now, I'll just write an SQL trigger to insert values for year_taught upon inserting records and tinker with the code to show only the records for the current year.