Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-29812

Course report - participation report, breaks when selecting some English letter from the "First Name" filter

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0.5, 2.1.2, 2.2
    • Fix Version/s: 2.0.6, 2.1.3
    • Component/s: Course, Logging, Reports
    • Labels:
      None
    • Testing Instructions:
      Hide

      1/ Create a course with 30 students enrolled
      2/ Do some activity
      3/ Go to course admin ► Reports ► Participation report
      4/ Display some participation records by choosing appropiate options
      5/ Filter results by firstname

      Expected result
      ------------------
      Results are displayed filtered by firstname

      Actual result
      --------------
      SQL error

      Show
      1/ Create a course with 30 students enrolled 2/ Do some activity 3/ Go to course admin ► Reports ► Participation report 4/ Display some participation records by choosing appropiate options 5/ Filter results by firstname Expected result ------------------ Results are displayed filtered by firstname Actual result -------------- SQL error
    • Affected Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE, MOODLE_22_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE, MOODLE_21_STABLE
    • Pull from Repository:

      Description

      I was getting an SQL error:

      Debug info: Unknown column 'firstname' in 'where clause'
      SELECT COUNT(DISTINCT(ra.userid))
      FROM mdl_role_assignments ra
       
      WHERE ra.contextid IN (661,2524,1) AND ra.roleid = ? AND firstname LIKE ? ESCAPE '\\'
      [array (
      0 => 16,
      1 => 'A%',
      )]
      Stack trace:
      line 394 of /lib/dml/moodle_database.php: dml_read_exception thrown
      line 794 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      line 1280 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
      line 1355 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
      line 1526 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      line 232 of /course/report/participation/index.php: call to moodle_database->count_records_sql()

      by added the line "JOIN

      {user} u ON u.id = ra.userid" just after line 225 that is : "FROM {role_assignments} ra"
      Issue was solved

      see full code at line 224:

       

      $countsql = "SELECT COUNT(DISTINCT(ra.userid))
      FROM {role_assignments} ra
      JOIN {user}

      u ON u.id = ra.userid
      WHERE ra.contextid $relatedcontexts AND ra.roleid = :roleid";

       

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            poltawski Dan Poltawski added a comment -

            Note, this needs to have more than per page users to reproduce it

            Show
            poltawski Dan Poltawski added a comment - Note, this needs to have more than per page users to reproduce it
            Hide
            poltawski Dan Poltawski added a comment -

            Integrators: this can be cherry picked into 20_STABLE and master

            Show
            poltawski Dan Poltawski added a comment - Integrators: this can be cherry picked into 20_STABLE and master
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week.

            TIA and ciao

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - The main moodle.git repository has just been updated with latest weekly modifications. You may wish to rebase your PULL branches to simplify history and avoid any possible merge conflicts. This would also make integrator's life easier next week. TIA and ciao
            Hide
            nadavkav Nadav Kavalerchik added a comment -

            Thanks Eloy

            Show
            nadavkav Nadav Kavalerchik added a comment - Thanks Eloy
            Hide
            nebgor Aparup Banerjee added a comment -

            Thanks for the report and fix guys!
            This has been integrated now and up for testing.

            Show
            nebgor Aparup Banerjee added a comment - Thanks for the report and fix guys! This has been integrated now and up for testing.
            Hide
            ankit_frenz Ankit Agarwal added a comment -

            all Looking good!
            Passing
            Thanks!

            Show
            ankit_frenz Ankit Agarwal added a comment - all Looking good! Passing Thanks!
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            Somebody is allergic to use the "Test passed" transition button, lol!

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Somebody is allergic to use the "Test passed" transition button, lol!
            Hide
            stronk7 Eloy Lafuente (stronk7) added a comment -

            Yes, you got this finally upstream, just in time for Moodle 2.2beta. Congrats and thanks!

            Ciao

            Show
            stronk7 Eloy Lafuente (stronk7) added a comment - Yes, you got this finally upstream, just in time for Moodle 2.2beta. Congrats and thanks! Ciao
            Hide
            brugger Gisele Brugger added a comment -

            Humm
            In Moodle 2.7 (Build: 20140512)
            I have this problem

            see error:

            Debug info:
            Error code: missingkeyinsql
            Stack trace:
            line 871 of /lib/dml/moodle_database.php: dml_exception thrown
            line 1009 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->fix_sql_params()
            line 1476 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
            line 1549 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
            line 1759 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
            line 222 of /report/participation/index.php: call to moodle_database->count_records_sql()

            i am debugging code with echo

            see in line 214 ../moodle/report/completion/index.php

            if ($twhere)

            { echo "<br>twhere= ".$twhere."<br>"; echo "<br>countsql = ".$countsql."<br>"; echo "<br>"; print_r( $tparams); echo "<br>params="; print_r($params); $matchcount = $DB->count_records_sql($countsql.' AND '.$twhere, $params); }

            else

            { $matchcount = $totalcount; }

            Result is:

            twhere= firstname LIKE :ifirstc1 ESCAPE '
            '

            countsql = SELECT COUNT(DISTINCT(ra.userid)) FROM

            {role_assignments}

            ra JOIN

            {user}

            u ON u.id = ra.userid WHERE ra.contextid IN (:relatedctx13,:relatedctx14,:relatedctx15,:relatedctx16) AND ra.roleid = :roleid

            Array ( [ifirstc1] => J% )
            params=Array ( [relatedctx13] => 61 [relatedctx14] => 75 [relatedctx15] => 46 [relatedctx16] => 1 [roleid] => 5 [instanceid] => 291 [timefrom] => 1405306800 )

            This is my prit screen https://docs.google.com/file/d/0B0vy23qkbnL5VkNXZ21UTHBNVjQ/edit?usp=drivesdk

            Thank you

            Show
            brugger Gisele Brugger added a comment - Humm In Moodle 2.7 (Build: 20140512) I have this problem see error: Debug info: Error code: missingkeyinsql Stack trace: line 871 of /lib/dml/moodle_database.php: dml_exception thrown line 1009 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->fix_sql_params() line 1476 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql() line 1549 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql() line 1759 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql() line 222 of /report/participation/index.php: call to moodle_database->count_records_sql() i am debugging code with echo see in line 214 ../moodle/report/completion/index.php if ($twhere) { echo "<br>twhere= ".$twhere."<br>"; echo "<br>countsql = ".$countsql."<br>"; echo "<br>"; print_r( $tparams); echo "<br>params="; print_r($params); $matchcount = $DB->count_records_sql($countsql.' AND '.$twhere, $params); } else { $matchcount = $totalcount; } Result is: twhere= firstname LIKE :ifirstc1 ESCAPE ' ' countsql = SELECT COUNT(DISTINCT(ra.userid)) FROM {role_assignments} ra JOIN {user} u ON u.id = ra.userid WHERE ra.contextid IN (:relatedctx13,:relatedctx14,:relatedctx15,:relatedctx16) AND ra.roleid = :roleid Array ( [ifirstc1] => J% ) params=Array ( [relatedctx13] => 61 [relatedctx14] => 75 [relatedctx15] => 46 [relatedctx16] => 1 [roleid] => 5 [instanceid] => 291 [timefrom] => 1405306800 ) This is my prit screen https://docs.google.com/file/d/0B0vy23qkbnL5VkNXZ21UTHBNVjQ/edit?usp=drivesdk Thank you
            Hide
            brugger Gisele Brugger added a comment -

            sorry path is ../moodle/report/participation/index.php

            Show
            brugger Gisele Brugger added a comment - sorry path is ../moodle/report/participation/index.php
            Hide
            marina Marina Glancy added a comment -

            Thanks Gisele, I created an issue MDL-47052 about it

            Show
            marina Marina Glancy added a comment - Thanks Gisele, I created an issue MDL-47052 about it
            Hide
            brugger Gisele Brugger added a comment -

            Thank you very much Marina..

            Its resolved : only add this code

            $params = array_merge($params, $tparams);

            Show
            brugger Gisele Brugger added a comment - Thank you very much Marina.. Its resolved : only add this code $params = array_merge($params, $tparams);

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  28/Nov/11