Duplicate records shown when using Mjoin

Kuo7

So I'm building a students academic records system. I'm trying to display lecturers and the courses they are teaching by using the Mjoin example where I have a link table, lecturercourserecords which will store lecturers and their courses and two (2) linked tables, lecturers and courses that store records for lecturers and courses respectively.
The problem is if a lecturer is teaching more than 1 course, the table generated shows duplicate rows for that particular lecturer depending on the number of courses taught. I need datatables to show only 1 row for every lecturer, and a lecturer teaching more than 1 course should also show in 1 row with the courses being comma-separated. Furthermore, the number of records in lecturercourserecords matches the one generated by datatables. The rows displayed in datatables should be less than those in lecturercourserecords as I am expecting courses taught by lecturers to be comma-separated and thus be shown in 1 row.
Any pointer/help 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('lecturercourserecords.lecturer')
                ->options(Options::inst()
                    ->table('lecturers')
                    ->value('ID')
                    ->label('Name')
                    )
                    ->validator(Validate::dbValues())
                    ->validator(Validate::notEmpty(ValidateOptions::inst()
                        ->message('Please select a lecturer.')
                    )
                ),
            Field::inst('lecturers.Name')           
        )
        ->leftJoin('lecturercourserecords', 'lecturercourserecords.lecturer', '=', 'lecturers.ID')
        ->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('Name')
                        ),
                    Field::inst('Name')
                )
        )
        ->process($_POST)
        ->json();

<?php
>
```
?>


**Javascript**
```js
<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: "lecturercourserecords.lecturer",
                    type: "select",
                    placeholder: "Select a lecturer"
                },
                {
                    "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: "courses", render: "[, ].Name" }
              ],
              "columnDefs": [
                  {
                      "orderable": false,
                      "searchable": false,
                      "targets": 1  
                  }
              ],
              select: true,
              buttons: [
                  { extend: "create", editor: editor },
                  { extend: "edit",   editor: editor },
                  { extend: "remove", editor: editor }
              ]
         });
    });
</script>

allan

It sounds like you don't really need a left join at all.

Remove Field::inst('lecturercourserecords.lecturer') and the leftJoin. You are already getting the list of lecturers and their names, then the Mjoin will do the link between the tables for each lecturer.

Allan

Kuo7

Thank you Allan!
Removing the leftJoin and replacing Field::inst('lecturercourserecords.lecturer') with Field::inst('lecturers.ID') and name: "lecturercourserecords.lecturer" with name: "lecturers.ID" solved my problem.