Duplicate records shown when using Mjoin

Duplicate records shown when using Mjoin

Kuo7Kuo7 Posts: 8Questions: 3Answers: 0
edited May 15 in Free community support

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();
?>

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: "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>

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 50,077Questions: 1Answers: 7,411 Site admin
    Answer ✓

    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

  • Kuo7Kuo7 Posts: 8Questions: 3Answers: 0

    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.

Sign In or Register to comment.