user/tests/fields_test.php

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