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

          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