[Summary view]

Differences Between: [Versions 311 and 403]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  namespace core_user;
  18  
  19  /**
  20   * Unit tests for \core_user\fields
  21   *
  22   * @package core
  23   * @copyright 2014 The Open University
  24   * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  25   * @covers \core_user\fields
  26   */
  27  class fields_test extends \advanced_testcase {
  28  
  29      /**
  30       * Tests getting the user picture fields.
  31       */
  32      public function test_get_picture_fields() {
  33          $this->assertEquals(['id', 'picture', 'firstname', 'lastname', 'firstnamephonetic',
  34                  'lastnamephonetic', 'middlename', 'alternatename', 'imagealt', 'email'],
  35                  fields::get_picture_fields());
  36      }
  37  
  38      /**
  39       * Tests getting the user name fields.
  40       */
  41      public function test_get_name_fields() {
  42          $this->assertEquals(['firstnamephonetic', 'lastnamephonetic', 'middlename', 'alternatename',
  43                  'firstname', 'lastname'],
  44                  fields::get_name_fields());
  45  
  46          $this->assertEquals(['firstname', 'lastname',
  47                  'firstnamephonetic', 'lastnamephonetic', 'middlename', 'alternatename'],
  48                  fields::get_name_fields(true));
  49      }
  50  
  51      /**
  52       * Tests getting the identity fields.
  53       */
  54      public function test_get_identity_fields() {
  55          global $DB, $CFG, $COURSE;
  56  
  57          $this->resetAfterTest();
  58  
  59          require_once($CFG->dirroot . '/user/profile/lib.php');
  60  
  61          // Create custom profile fields, one with each visibility option.
  62          $generator = self::getDataGenerator();
  63          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'a', 'name' => 'A',
  64                  'visible' => PROFILE_VISIBLE_ALL]);
  65          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'b', 'name' => 'B',
  66                  'visible' => PROFILE_VISIBLE_PRIVATE]);
  67          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'c', 'name' => 'C',
  68                  'visible' => PROFILE_VISIBLE_NONE]);
  69          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'd', 'name' => 'D',
  70                  'visible' => PROFILE_VISIBLE_TEACHERS]);
  71  
  72          // Set the extra user fields to include email, department, and all custom profile fields.
  73          set_config('showuseridentity', 'email,department,profile_field_a,profile_field_b,' .
  74                  'profile_field_c,profile_field_d');
  75          set_config('hiddenuserfields', 'email');
  76  
  77          // Create a test course and a student in the course.
  78          $course = $generator->create_course();
  79          $coursecontext = \context_course::instance($course->id);
  80          $user = $generator->create_user();
  81          $anotheruser = $generator->create_user();
  82          $usercontext = \context_user::instance($anotheruser->id);
  83          $generator->enrol_user($user->id, $course->id, 'student');
  84  
  85          // When no context is provided, it does no access checks and should return all specified (other than non-visible).
  86          $this->assertEquals(['email', 'department', 'profile_field_a', 'profile_field_b', 'profile_field_d'],
  87                  fields::get_identity_fields(null));
  88  
  89          // If you turn off custom profile fields, you don't get those.
  90          $this->assertEquals(['email', 'department'], fields::get_identity_fields(null, false));
  91  
  92          // Request in context as an administator.
  93          $this->setAdminUser();
  94          $this->assertEquals(['email', 'department', 'profile_field_a', 'profile_field_b',
  95                  'profile_field_c', 'profile_field_d'],
  96                  fields::get_identity_fields($coursecontext));
  97          $this->assertEquals(['email', 'department'],
  98                  fields::get_identity_fields($coursecontext, false));
  99  
 100          // Request in context as a student - they don't have any of the capabilities to see identity
 101          // fields or profile fields.
 102          $this->setUser($user);
 103          $this->assertEquals([], fields::get_identity_fields($coursecontext));
 104  
 105          // Give the student the basic identity fields permission (also makes them count as 'teacher'
 106          // for the teacher-restricted field).
 107          $COURSE = $course; // Horrible hack, because PROFILE_VISIBLE_TEACHERS relies on this global.
 108          $roleid = $DB->get_field('role', 'id', ['shortname' => 'student']);
 109          role_change_permission($roleid, $coursecontext, 'moodle/site:viewuseridentity', CAP_ALLOW);
 110          $this->assertEquals(['department', 'profile_field_a', 'profile_field_d'],
 111                  fields::get_identity_fields($coursecontext));
 112          $this->assertEquals(['department'],
 113                  fields::get_identity_fields($coursecontext, false));
 114  
 115          // Give them permission to view hidden user fields.
 116          role_change_permission($roleid, $coursecontext, 'moodle/course:viewhiddenuserfields', CAP_ALLOW);
 117          $this->assertEquals(['email', 'department', 'profile_field_a', 'profile_field_d'],
 118                  fields::get_identity_fields($coursecontext));
 119          $this->assertEquals(['email', 'department'],
 120                  fields::get_identity_fields($coursecontext, false));
 121  
 122          // Also give them permission to view all profile fields.
 123          role_change_permission($roleid, $coursecontext, 'moodle/user:viewalldetails', CAP_ALLOW);
 124          $this->assertEquals(['email', 'department', 'profile_field_a', 'profile_field_b',
 125                  'profile_field_c', 'profile_field_d'],
 126                  fields::get_identity_fields($coursecontext));
 127          $this->assertEquals(['email', 'department'],
 128                  fields::get_identity_fields($coursecontext, false));
 129  
 130          // Even if we give them student role in the user context they can't view anything...
 131          $generator->role_assign($roleid, $user->id, $usercontext->id);
 132          $this->assertEquals([], fields::get_identity_fields($usercontext));
 133  
 134          // Give them basic permission.
 135          role_change_permission($roleid, $usercontext, 'moodle/site:viewuseridentity', CAP_ALLOW);
 136          $this->assertEquals(['department', 'profile_field_a', 'profile_field_d'],
 137                  fields::get_identity_fields($usercontext));
 138          $this->assertEquals(['department'],
 139                  fields::get_identity_fields($usercontext, false));
 140  
 141          // Give them the hidden user fields permission (it's a different one).
 142          role_change_permission($roleid, $usercontext, 'moodle/user:viewhiddendetails', CAP_ALLOW);
 143          $this->assertEquals(['email', 'department', 'profile_field_a', 'profile_field_d'],
 144                  fields::get_identity_fields($usercontext));
 145          $this->assertEquals(['email', 'department'],
 146                  fields::get_identity_fields($usercontext, false));
 147  
 148          // Also give them permission to view all profile fields.
 149          role_change_permission($roleid, $usercontext, 'moodle/user:viewalldetails', CAP_ALLOW);
 150          $this->assertEquals(['email', 'department', 'profile_field_a', 'profile_field_b',
 151                  'profile_field_c', 'profile_field_d'],
 152                  fields::get_identity_fields($usercontext));
 153          $this->assertEquals(['email', 'department'],
 154                  fields::get_identity_fields($usercontext, false));
 155      }
 156  
 157      /**
 158       * Test getting identity fields, when one of them refers to a non-existing custom profile field
 159       */
 160      public function test_get_identity_fields_invalid(): void {
 161          $this->resetAfterTest();
 162  
 163          $this->getDataGenerator()->create_custom_profile_field([
 164              'datatype' => 'text',
 165              'shortname' => 'real',
 166              'name' => 'I\'m real',
 167          ]);
 168  
 169          // The "fake" profile field does not exist.
 170          set_config('showuseridentity', 'email,profile_field_real,profile_field_fake');
 171  
 172          $this->assertEquals([
 173              'email',
 174              'profile_field_real',
 175          ], fields::get_identity_fields(null));
 176      }
 177  
 178      /**
 179       * Tests the get_required_fields function.
 180       *
 181       * This function composes the results of get_identity/name/picture_fields, so we are not going
 182       * to test the details of the identity permissions as that was already covered. Just how they
 183       * are included/combined.
 184       */
 185      public function test_get_required_fields() {
 186          $this->resetAfterTest();
 187  
 188          // Set up some profile fields.
 189          $generator = self::getDataGenerator();
 190          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'a', 'name' => 'A']);
 191          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'b', 'name' => 'B']);
 192          set_config('showuseridentity', 'email,department,profile_field_a');
 193  
 194          // What happens if you don't ask for anything?
 195          $fields = fields::empty();
 196          $this->assertEquals([], $fields->get_required_fields());
 197  
 198          // Try each invidual purpose.
 199          $fields = fields::for_identity(null);
 200          $this->assertEquals(['email', 'department', 'profile_field_a'], $fields->get_required_fields());
 201          $fields = fields::for_userpic();
 202          $this->assertEquals(fields::get_picture_fields(), $fields->get_required_fields());
 203          $fields = fields::for_name();
 204          $this->assertEquals(fields::get_name_fields(), $fields->get_required_fields());
 205  
 206          // Try combining them all. There should be no duplicates (e.g. email), and the 'id' field
 207          // should be moved to the start.
 208          $fields = fields::for_identity(null)->with_name()->with_userpic();
 209          $this->assertEquals(['id', 'email', 'department', 'profile_field_a', 'picture',
 210                  'firstname', 'lastname', 'firstnamephonetic', 'lastnamephonetic', 'middlename',
 211                  'alternatename', 'imagealt'], $fields->get_required_fields());
 212  
 213          // Add some specified fields to a default result.
 214          $fields = fields::for_identity(null, true)->including('city', 'profile_field_b');
 215          $this->assertEquals(['email', 'department', 'profile_field_a', 'city', 'profile_field_b'],
 216                  $fields->get_required_fields());
 217  
 218          // Remove some fields, one of which actually is in the list.
 219          $fields = fields::for_identity(null, true)->excluding('email', 'city');
 220          $this->assertEquals(['department', 'profile_field_a'], $fields->get_required_fields());
 221  
 222          // Add and remove fields.
 223          $fields = fields::for_identity(null, true)->including('city', 'profile_field_b')->excluding('city', 'department');
 224          $this->assertEquals(['email', 'profile_field_a', 'profile_field_b'],
 225                  $fields->get_required_fields());
 226  
 227          // Request the list without profile fields, check that still works with both sources.
 228          $fields = fields::for_identity(null, false)->including('city', 'profile_field_b')->excluding('city', 'department');
 229          $this->assertEquals(['email'], $fields->get_required_fields());
 230      }
 231  
 232      /**
 233       * Tests the get_required_fields function when you use the $limitpurposes parameter.
 234       */
 235      public function test_get_required_fields_limitpurposes() {
 236          $this->resetAfterTest();
 237  
 238          // Set up some profile fields.
 239          $generator = self::getDataGenerator();
 240          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'a', 'name' => 'A']);
 241          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'b', 'name' => 'B']);
 242          set_config('showuseridentity', 'email,department,profile_field_a');
 243  
 244          // Create a fields object with all three purposes, plus included and excluded fields.
 245          $fields = fields::for_identity(null, true)->with_name()->with_userpic()
 246              ->including('city', 'profile_field_b')->excluding('firstnamephonetic', 'middlename', 'alternatename');
 247  
 248          // Check the result with all purposes.
 249          $this->assertEquals(['id', 'email', 'department', 'profile_field_a', 'picture',
 250                  'firstname', 'lastname', 'lastnamephonetic', 'imagealt', 'city',
 251                  'profile_field_b'],
 252                  $fields->get_required_fields([fields::PURPOSE_IDENTITY, fields::PURPOSE_NAME,
 253                  fields::PURPOSE_USERPIC, fields::CUSTOM_INCLUDE]));
 254  
 255          // Limit to identity and custom includes.
 256          $this->assertEquals(['email', 'department', 'profile_field_a', 'city', 'profile_field_b'],
 257                  $fields->get_required_fields([fields::PURPOSE_IDENTITY, fields::CUSTOM_INCLUDE]));
 258  
 259          // Limit to name fields.
 260          $this->assertEquals(['firstname', 'lastname', 'lastnamephonetic'],
 261                  $fields->get_required_fields([fields::PURPOSE_NAME]));
 262      }
 263  
 264      /**
 265       * There should be an exception if you try to 'limit' purposes to one that wasn't even included.
 266       */
 267      public function test_get_required_fields_limitpurposes_not_in_constructor() {
 268          $fields = fields::for_identity(null);
 269          $this->expectExceptionMessage('$limitpurposes can only include purposes defined in object');
 270          $fields->get_required_fields([fields::PURPOSE_USERPIC]);
 271      }
 272  
 273      /**
 274       * Sets up data and a fields object for all the get_sql tests.
 275       *
 276       * @return fields Constructed fields object for testing
 277       */
 278      protected function init_for_sql_tests(): fields {
 279          $generator = self::getDataGenerator();
 280          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'a', 'name' => 'A']);
 281          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'b', 'name' => 'B']);
 282  
 283          // Create a couple of users. One doesn't have a profile field set, so we can test that.
 284          $generator->create_user(['profile_field_a' => 'A1', 'profile_field_b' => 'B1',
 285                  'city' => 'C1', 'department' => 'D1', 'email' => 'e1@example.org',
 286                  'idnumber' => 'XXX1', 'username' => 'u1']);
 287          $generator->create_user(['profile_field_a' => 'A2',
 288                  'city' => 'C2', 'department' => 'D2', 'email' => 'e2@example.org',
 289                  'idnumber' => 'XXX2', 'username' => 'u2']);
 290  
 291          // It doesn't matter how we construct it (we already tested get_required_fields which is
 292          // where all those values are actually used) so let's just list the fields we want manually.
 293          return fields::empty()->including('department', 'city', 'profile_field_a', 'profile_field_b');
 294      }
 295  
 296      /**
 297       * Tests getting SQL (and actually using it).
 298       */
 299      public function test_get_sql_variations() {
 300          global $DB;
 301          $this->resetAfterTest();
 302  
 303          $fields = $this->init_for_sql_tests();
 304          fields::reset_unique_identifier();
 305  
 306          // Basic SQL.
 307          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams, 'mappings' => $mappings] =
 308                  (array)$fields->get_sql();
 309          $sql = "SELECT idnumber
 310                         $selects
 311                    FROM {user}
 312                         $joins
 313                   WHERE idnumber LIKE ?
 314                ORDER BY idnumber";
 315          $records = $DB->get_records_sql($sql, array_merge($joinparams, ['X%']));
 316          $this->assertCount(2, $records);
 317          $expected1 = (object)['profile_field_a' => 'A1', 'profile_field_b' => 'B1',
 318                  'city' => 'C1', 'department' => 'D1', 'idnumber' => 'XXX1'];
 319          $expected2 = (object)['profile_field_a' => 'A2', 'profile_field_b' => null,
 320                  'city' => 'C2', 'department' => 'D2', 'idnumber' => 'XXX2'];
 321          $this->assertEquals($expected1, $records['XXX1']);
 322          $this->assertEquals($expected2, $records['XXX2']);
 323  
 324          $this->assertEquals([
 325                  'department' => '{user}.department',
 326                  'city' => '{user}.city',
 327                  'profile_field_a' => $DB->sql_compare_text('uf1d_1.data', 255),
 328                  'profile_field_b' => $DB->sql_compare_text('uf1d_2.data', 255)], $mappings);
 329  
 330          // SQL using named params.
 331          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams] =
 332                  (array)$fields->get_sql('', true);
 333          $sql = "SELECT idnumber
 334                         $selects
 335                    FROM {user}
 336                         $joins
 337                   WHERE idnumber LIKE :idnum
 338                ORDER BY idnumber";
 339          $records = $DB->get_records_sql($sql, array_merge($joinparams, ['idnum' => 'X%']));
 340          $this->assertCount(2, $records);
 341          $this->assertEquals($expected1, $records['XXX1']);
 342          $this->assertEquals($expected2, $records['XXX2']);
 343  
 344          // SQL using alias for user table.
 345          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams, 'mappings' => $mappings] =
 346                  (array)$fields->get_sql('u');
 347          $sql = "SELECT idnumber
 348                         $selects
 349                    FROM {user} u
 350                         $joins
 351                   WHERE idnumber LIKE ?
 352                ORDER BY idnumber";
 353          $records = $DB->get_records_sql($sql, array_merge($joinparams, ['X%']));
 354          $this->assertCount(2, $records);
 355          $this->assertEquals($expected1, $records['XXX1']);
 356          $this->assertEquals($expected2, $records['XXX2']);
 357  
 358          $this->assertEquals([
 359                  'department' => 'u.department',
 360                  'city' => 'u.city',
 361                  'profile_field_a' => $DB->sql_compare_text('uf3d_1.data', 255),
 362                  'profile_field_b' => $DB->sql_compare_text('uf3d_2.data', 255)], $mappings);
 363  
 364          // Returning prefixed fields.
 365          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams] =
 366                  (array)$fields->get_sql('', false, 'u_');
 367          $sql = "SELECT idnumber
 368                         $selects
 369                    FROM {user}
 370                         $joins
 371                   WHERE idnumber LIKE ?
 372                ORDER BY idnumber";
 373          $records = $DB->get_records_sql($sql, array_merge($joinparams, ['X%']));
 374          $this->assertCount(2, $records);
 375          $expected1 = (object)['u_profile_field_a' => 'A1', 'u_profile_field_b' => 'B1',
 376                  'u_city' => 'C1', 'u_department' => 'D1', 'idnumber' => 'XXX1'];
 377          $this->assertEquals($expected1, $records['XXX1']);
 378  
 379          // Renaming the id field. We need to use a different set of fields so it actually has the
 380          // id field.
 381          $fields = fields::for_userpic();
 382          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams] =
 383                  (array)$fields->get_sql('', false, '', 'userid');
 384          $sql = "SELECT idnumber
 385                         $selects
 386                    FROM {user}
 387                         $joins
 388                   WHERE idnumber LIKE ?
 389                ORDER BY idnumber";
 390          $records = $DB->get_records_sql($sql, array_merge($joinparams, ['X%']));
 391          $this->assertCount(2, $records);
 392  
 393          // User id was renamed.
 394          $this->assertObjectNotHasAttribute('id', $records['XXX1']);
 395          $this->assertObjectHasAttribute('userid', $records['XXX1']);
 396  
 397          // Other fields are normal (just try a couple).
 398          $this->assertObjectHasAttribute('firstname', $records['XXX1']);
 399          $this->assertObjectHasAttribute('imagealt', $records['XXX1']);
 400  
 401          // Check the user id is actually right.
 402          $this->assertEquals('XXX1',
 403                  $DB->get_field('user', 'idnumber', ['id' => $records['XXX1']->userid]));
 404  
 405          // Rename the id field and also use a prefix.
 406          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams] =
 407                  (array)$fields->get_sql('', false, 'u_', 'userid');
 408          $sql = "SELECT idnumber
 409                         $selects
 410                    FROM {user}
 411                         $joins
 412                   WHERE idnumber LIKE ?
 413                ORDER BY idnumber";
 414          $records = $DB->get_records_sql($sql, array_merge($joinparams, ['X%']));
 415          $this->assertCount(2, $records);
 416  
 417          // User id was renamed.
 418          $this->assertObjectNotHasAttribute('id', $records['XXX1']);
 419          $this->assertObjectNotHasAttribute('u_id', $records['XXX1']);
 420          $this->assertObjectHasAttribute('userid', $records['XXX1']);
 421  
 422          // Other fields are prefixed (just try a couple).
 423          $this->assertObjectHasAttribute('u_firstname', $records['XXX1']);
 424          $this->assertObjectHasAttribute('u_imagealt', $records['XXX1']);
 425  
 426          // Without a leading comma.
 427          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams] =
 428                  (array)$fields->get_sql('', false, '', '', false);
 429          $sql = "SELECT $selects
 430                    FROM {user}
 431                         $joins
 432                   WHERE idnumber LIKE ?
 433                ORDER BY idnumber";
 434          $records = $DB->get_records_sql($sql, array_merge($joinparams, ['X%']));
 435          $this->assertCount(2, $records);
 436          foreach ($records as $key => $record) {
 437              // ID should be the first field used by get_records_sql.
 438              $this->assertEquals($key, $record->id);
 439              // Check 2 other sample properties.
 440              $this->assertObjectHasAttribute('firstname', $record);
 441              $this->assertObjectHasAttribute('imagealt', $record);
 442          }
 443      }
 444  
 445      /**
 446       * Tests what happens if you use the SQL multiple times in a query (i.e. that it correctly
 447       * creates the different identifiers).
 448       */
 449      public function test_get_sql_multiple() {
 450          global $DB;
 451          $this->resetAfterTest();
 452  
 453          $fields = $this->init_for_sql_tests();
 454  
 455          // Inner SQL.
 456          ['selects' => $selects1, 'joins' => $joins1, 'params' => $joinparams1] =
 457                  (array)$fields->get_sql('u1', true);
 458          // Outer SQL.
 459          $fields2 = fields::empty()->including('profile_field_a', 'email');
 460          ['selects' => $selects2, 'joins' => $joins2, 'params' => $joinparams2] =
 461                  (array)$fields2->get_sql('u2', true);
 462  
 463          // Crazy combined query.
 464          $sql = "SELECT username, details.profile_field_b AS innerb, details.city AS innerc
 465                         $selects2
 466                    FROM {user} u2
 467                         $joins2
 468               LEFT JOIN (
 469                            SELECT u1.id
 470                                   $selects1
 471                              FROM {user} u1
 472                                   $joins1
 473                             WHERE idnumber LIKE :idnum
 474                         ) details ON details.id = u2.id
 475                ORDER BY username";
 476          $records = $DB->get_records_sql($sql, array_merge($joinparams1, $joinparams2, ['idnum' => 'X%']));
 477          // The left join won't match for admin.
 478          $this->assertNull($records['admin']->innerb);
 479          $this->assertNull($records['admin']->innerc);
 480          // It should match for one of the test users though.
 481          $expected1 = (object)['username' => 'u1', 'innerb' => 'B1', 'innerc' => 'C1',
 482                  'profile_field_a' => 'A1', 'email' => 'e1@example.org'];
 483          $this->assertEquals($expected1, $records['u1']);
 484      }
 485  
 486      /**
 487       * Tests the get_sql function when there are no fields to retrieve.
 488       */
 489      public function test_get_sql_nothing() {
 490          $fields = fields::empty();
 491          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams] = (array)$fields->get_sql();
 492          $this->assertEquals('', $selects);
 493          $this->assertEquals('', $joins);
 494          $this->assertEquals([], $joinparams);
 495      }
 496  
 497      /**
 498       * Tests get_sql when there are no custom fields; in this scenario, the joins and joinparams
 499       * are always blank.
 500       */
 501      public function test_get_sql_no_custom_fields() {
 502          $fields = fields::empty()->including('city', 'country');
 503          ['selects' => $selects, 'joins' => $joins, 'params' => $joinparams, 'mappings' => $mappings] =
 504                  (array)$fields->get_sql('u');
 505          $this->assertEquals(', u.city, u.country', $selects);
 506          $this->assertEquals('', $joins);
 507          $this->assertEquals([], $joinparams);
 508          $this->assertEquals(['city' => 'u.city', 'country' => 'u.country'], $mappings);
 509      }
 510  
 511      /**
 512       * Tests the format of the $selects string, which is important particularly for backward
 513       * compatibility.
 514       */
 515      public function test_get_sql_selects_format() {
 516          global $DB;
 517  
 518          $this->resetAfterTest();
 519          fields::reset_unique_identifier();
 520  
 521          $generator = self::getDataGenerator();
 522          $generator->create_custom_profile_field(['datatype' => 'text', 'shortname' => 'a', 'name' => 'A']);
 523  
 524          // When we list fields that include custom profile fields...
 525          $fields = fields::empty()->including('id', 'profile_field_a');
 526  
 527          // Supplying an alias: all fields have alias.
 528          $selects = $fields->get_sql('u')->selects;
 529          $this->assertEquals(', u.id, ' . $DB->sql_compare_text('uf1d_1.data', 255) . ' AS profile_field_a', $selects);
 530  
 531          // No alias: all files have {user} because of the joins.
 532          $selects = $fields->get_sql()->selects;
 533          $this->assertEquals(', {user}.id, ' . $DB->sql_compare_text('uf2d_1.data', 255) . ' AS profile_field_a', $selects);
 534  
 535          // When the list doesn't include custom profile fields...
 536          $fields = fields::empty()->including('id', 'city');
 537  
 538          // Supplying an alias: all fields have alias.
 539          $selects = $fields->get_sql('u')->selects;
 540          $this->assertEquals(', u.id, u.city', $selects);
 541  
 542          // No alias: fields do not have alias at all.
 543          $selects = $fields->get_sql()->selects;
 544          $this->assertEquals(', id, city', $selects);
 545      }
 546  
 547      /**
 548       * Data provider for {@see test_get_sql_fullname}
 549       *
 550       * @return array
 551       */
 552      public function get_sql_fullname_provider(): array {
 553          return [
 554              ['firstname lastname', 'FN LN'],
 555              ['lastname, firstname', 'LN, FN'],
 556              ['alternatename \'middlename\' lastname!', 'AN \'MN\' LN!'],
 557              ['[firstname lastname alternatename]', '[FN LN AN]'],
 558              ['firstnamephonetic lastnamephonetic', 'FNP LNP'],
 559              ['firstname alternatename lastname', 'FN AN LN'],
 560          ];
 561      }
 562  
 563      /**
 564       * Test sql_fullname_display method with various fullname formats
 565       *
 566       * @param string $fullnamedisplay
 567       * @param string $expectedfullname
 568       *
 569       * @dataProvider get_sql_fullname_provider
 570       */
 571      public function test_get_sql_fullname(string $fullnamedisplay, string $expectedfullname): void {
 572          global $DB;
 573  
 574          $this->resetAfterTest();
 575  
 576          set_config('fullnamedisplay', $fullnamedisplay);
 577          $user = $this->getDataGenerator()->create_user([
 578              'firstname' => 'FN',
 579              'lastname' => 'LN',
 580              'firstnamephonetic' => 'FNP',
 581              'lastnamephonetic' => 'LNP',
 582              'middlename' => 'MN',
 583              'alternatename' => 'AN',
 584          ]);
 585  
 586          [$sqlfullname, $params] = fields::get_sql_fullname('u');
 587          $fullname = $DB->get_field_sql("SELECT {$sqlfullname} FROM {user} u WHERE u.id = :id", $params + [
 588              'id' => $user->id,
 589          ]);
 590  
 591          $this->assertEquals($expectedfullname, $fullname);
 592      }
 593  
 594      /**
 595       * Test sql_fullname_display when one of the configured name fields is null
 596       */
 597      public function test_get_sql_fullname_null_field(): void {
 598          global $DB;
 599  
 600          $this->resetAfterTest();
 601  
 602          set_config('fullnamedisplay', 'firstname lastname alternatename');
 603          $user = $this->getDataGenerator()->create_user([
 604              'firstname' => 'FN',
 605              'lastname' => 'LN',
 606          ]);
 607  
 608          // Set alternatename field to null, ensure we still get result in later assertion.
 609          $user->alternatename = null;
 610          user_update_user($user, false);
 611  
 612          [$sqlfullname, $params] = fields::get_sql_fullname('u');
 613          $fullname = $DB->get_field_sql("SELECT {$sqlfullname} FROM {user} u WHERE u.id = :id", $params + [
 614              'id' => $user->id,
 615          ]);
 616  
 617          $this->assertEquals('FN LN ', $fullname);
 618      }
 619  }