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

//DataTables PHP library to be used with Editor

    //use DataTables\Editor;
    Editor::inst($db, 'lecturers', 'ID')
                        ->message('Please select a lecturer.')
        ->leftJoin('lecturercourserecords', 'lecturercourserecords.lecturer', '=', 'lecturers.ID')
                ->link('lecturers.ID', 'lecturercourserecords.lecturer')                
                ->link('courses.Code', 'lecturercourserecords.course')
                ->order('name asc')


<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 }

This question has an accepted answers - jump to answer


  • 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.


  • 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.