Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 4.3.x will end 7 October 2024 (12 months).
  • Bug fixes for security issues in 4.3.x will end 21 April 2025 (18 months).
  • PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.2.x is supported too.

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  use core_text;
  20  
  21  /**
  22   * Class for retrieving information about user fields that are needed for displaying user identity.
  23   *
  24   * @package core_user
  25   */
  26  class fields {
  27      /** @var string Prefix used to identify custom profile fields */
  28      const PROFILE_FIELD_PREFIX = 'profile_field_';
  29      /** @var string Regular expression used to match a field name against the prefix */
  30      const PROFILE_FIELD_REGEX = '~^' . self::PROFILE_FIELD_PREFIX . '(.*)$~';
  31  
  32      /** @var int All fields required to display user's identity, based on server configuration */
  33      const PURPOSE_IDENTITY = 0;
  34      /** @var int All fields required to display a user picture */
  35      const PURPOSE_USERPIC = 1;
  36      /** @var int All fields required for somebody's name */
  37      const PURPOSE_NAME = 2;
  38      /** @var int Field required by custom include list */
  39      const CUSTOM_INCLUDE = 3;
  40  
  41      /** @var \context|null Context in use */
  42      protected $context;
  43  
  44      /** @var bool True to allow custom user fields */
  45      protected $allowcustom;
  46  
  47      /** @var bool[] Array of purposes (from PURPOSE_xx to true/false) */
  48      protected $purposes;
  49  
  50      /** @var string[] List of extra fields to include */
  51      protected $include;
  52  
  53      /** @var string[] List of fields to exclude */
  54      protected $exclude;
  55  
  56      /** @var int Unique identifier for different queries generated in same request */
  57      protected static $uniqueidentifier = 1;
  58  
  59      /** @var array|null Associative array from field => array of purposes it was used for => true */
  60      protected $fields = null;
  61  
  62      /**
  63       * Protected constructor - use one of the for_xx methods to create an object.
  64       *
  65       * @param int $purpose Initial purpose for object or -1 for none
  66       */
  67      protected function __construct(int $purpose = -1) {
  68          $this->purposes = [
  69              self::PURPOSE_IDENTITY => false,
  70              self::PURPOSE_USERPIC => false,
  71              self::PURPOSE_NAME => false,
  72          ];
  73          if ($purpose != -1) {
  74              $this->purposes[$purpose] = true;
  75          }
  76          $this->include = [];
  77          $this->exclude = [];
  78          $this->context = null;
  79          $this->allowcustom = true;
  80      }
  81  
  82      /**
  83       * Constructs an empty user fields object to get arbitrary user fields.
  84       *
  85       * You can add fields to retrieve with the including() function.
  86       *
  87       * @return fields User fields object ready for use
  88       */
  89      public static function empty(): fields {
  90          return new fields();
  91      }
  92  
  93      /**
  94       * Constructs a user fields object to get identity information for display.
  95       *
  96       * The function does all the required capability checks to see if the current user is allowed
  97       * to see them in the specified context. You can pass context null to get all the fields without
  98       * checking permissions.
  99       *
 100       * If the code can only handle fields in the main user table, and not custom profile fields,
 101       * then set $allowcustom to false.
 102       *
 103       * Note: After constructing the object you can use the ->with_xx, ->including, and ->excluding
 104       * functions to control the required fields in more detail. For example:
 105       *
 106       * $fields = fields::for_identity($context)->with_userpic()->excluding('email');
 107       *
 108       * @param \context|null $context Context; if supplied, includes only fields the current user should see
 109       * @param bool $allowcustom If true, custom profile fields may be included
 110       * @return fields User fields object ready for use
 111       */
 112      public static function for_identity(?\context $context, bool $allowcustom = true): fields {
 113          $fields = new fields(self::PURPOSE_IDENTITY);
 114          $fields->context = $context;
 115          $fields->allowcustom = $allowcustom;
 116          return $fields;
 117      }
 118  
 119      /**
 120       * Constructs a user fields object to get information required for displaying a user picture.
 121       *
 122       * Note: After constructing the object you can use the ->with_xx, ->including, and ->excluding
 123       * functions to control the required fields in more detail. For example:
 124       *
 125       * $fields = fields::for_userpic()->with_name()->excluding('email');
 126       *
 127       * @return fields User fields object ready for use
 128       */
 129      public static function for_userpic(): fields {
 130          return new fields(self::PURPOSE_USERPIC);
 131      }
 132  
 133      /**
 134       * Constructs a user fields object to get information required for displaying a user full name.
 135       *
 136       * Note: After constructing the object you can use the ->with_xx, ->including, and ->excluding
 137       * functions to control the required fields in more detail. For example:
 138       *
 139       * $fields = fields::for_name()->with_userpic()->excluding('email');
 140       *
 141       * @return fields User fields object ready for use
 142       */
 143      public static function for_name(): fields {
 144          return new fields(self::PURPOSE_NAME);
 145      }
 146  
 147      /**
 148       * On an existing fields object, adds the fields required for displaying user pictures.
 149       *
 150       * @return $this Same object for chaining function calls
 151       */
 152      public function with_userpic(): fields {
 153          $this->purposes[self::PURPOSE_USERPIC] = true;
 154          return $this;
 155      }
 156  
 157      /**
 158       * On an existing fields object, adds the fields required for displaying user full names.
 159       *
 160       * @return $this Same object for chaining function calls
 161       */
 162      public function with_name(): fields {
 163          $this->purposes[self::PURPOSE_NAME] = true;
 164          return $this;
 165      }
 166  
 167      /**
 168       * On an existing fields object, adds the fields required for displaying user identity.
 169       *
 170       * The function does all the required capability checks to see if the current user is allowed
 171       * to see them in the specified context. You can pass context null to get all the fields without
 172       * checking permissions.
 173       *
 174       * If the code can only handle fields in the main user table, and not custom profile fields,
 175       * then set $allowcustom to false.
 176       *
 177       * @param \context|null Context; if supplied, includes only fields the current user should see
 178       * @param bool $allowcustom If true, custom profile fields may be included
 179       * @return $this Same object for chaining function calls
 180       */
 181      public function with_identity(?\context $context, bool $allowcustom = true): fields {
 182          $this->context = $context;
 183          $this->allowcustom = $allowcustom;
 184          $this->purposes[self::PURPOSE_IDENTITY] = true;
 185          return $this;
 186      }
 187  
 188      /**
 189       * On an existing fields object, adds extra fields to be retrieved. You can specify either
 190       * fields from the user table e.g. 'email', or profile fields e.g. 'profile_field_height'.
 191       *
 192       * @param string ...$include One or more fields to add
 193       * @return $this Same object for chaining function calls
 194       */
 195      public function including(string ...$include): fields {
 196          $this->include = array_merge($this->include, $include);
 197          return $this;
 198      }
 199  
 200      /**
 201       * On an existing fields object, excludes fields from retrieval. You can specify either
 202       * fields from the user table e.g. 'email', or profile fields e.g. 'profile_field_height'.
 203       *
 204       * This is useful when constructing queries where your query already explicitly references
 205       * certain fields, so you don't want to retrieve them twice.
 206       *
 207       * @param string ...$exclude One or more fields to exclude
 208       * @return $this Same object for chaining function calls
 209       */
 210      public function excluding(...$exclude): fields {
 211          $this->exclude = array_merge($this->exclude, $exclude);
 212          return $this;
 213      }
 214  
 215      /**
 216       * Gets an array of all fields that are required for the specified purposes, also taking
 217       * into account the $includes and $excludes settings.
 218       *
 219       * The results may include basic field names (columns from the 'user' database table) and,
 220       * unless turned off, custom profile field names in the format 'profile_field_myfield'.
 221       *
 222       * You should not rely on the order of fields, with one exception: if there is an id field
 223       * it will be returned first. This is in case it is used with get_records calls.
 224       *
 225       * The $limitpurposes parameter is useful if you want to get a different set of fields than the
 226       * purposes in the constructor. For example, if you want to get SQL for identity + user picture
 227       * fields, but you then want to only get the identity fields as a list. (You can only specify
 228       * purposes that were also passed to the constructor i.e. it can only be used to restrict the
 229       * list, not add to it.)
 230       *
 231       * @param array $limitpurposes If specified, gets fields only for these purposes
 232       * @return string[] Array of required fields
 233       * @throws \coding_exception If any unknown purpose is listed
 234       */
 235      public function get_required_fields(array $limitpurposes = []): array {
 236          // The first time this is called, actually work out the list. There is no way to 'un-cache'
 237          // it, but these objects are designed to be short-lived so it doesn't need one.
 238          if ($this->fields === null) {
 239              // Add all the fields as array keys so that there are no duplicates.
 240              $this->fields = [];
 241              if ($this->purposes[self::PURPOSE_IDENTITY]) {
 242                  foreach (self::get_identity_fields($this->context, $this->allowcustom) as $field) {
 243                      $this->fields[$field] = [self::PURPOSE_IDENTITY => true];
 244                  }
 245              }
 246              if ($this->purposes[self::PURPOSE_USERPIC]) {
 247                  foreach (self::get_picture_fields() as $field) {
 248                      if (!array_key_exists($field, $this->fields)) {
 249                          $this->fields[$field] = [];
 250                      }
 251                      $this->fields[$field][self::PURPOSE_USERPIC] = true;
 252                  }
 253              }
 254              if ($this->purposes[self::PURPOSE_NAME]) {
 255                  foreach (self::get_name_fields() as $field) {
 256                      if (!array_key_exists($field, $this->fields)) {
 257                          $this->fields[$field] = [];
 258                      }
 259                      $this->fields[$field][self::PURPOSE_NAME] = true;
 260                  }
 261              }
 262              foreach ($this->include as $field) {
 263                  if ($this->allowcustom || !preg_match(self::PROFILE_FIELD_REGEX, $field)) {
 264                      if (!array_key_exists($field, $this->fields)) {
 265                          $this->fields[$field] = [];
 266                      }
 267                      $this->fields[$field][self::CUSTOM_INCLUDE] = true;
 268                  }
 269              }
 270              foreach ($this->exclude as $field) {
 271                  unset($this->fields[$field]);
 272              }
 273  
 274              // If the id field is included, make sure it's first in the list.
 275              if (array_key_exists('id', $this->fields)) {
 276                  $newfields = ['id' => $this->fields['id']];
 277                  foreach ($this->fields as $field => $purposes) {
 278                      if ($field !== 'id') {
 279                          $newfields[$field] = $purposes;
 280                      }
 281                  }
 282                  $this->fields = $newfields;
 283              }
 284          }
 285  
 286          if ($limitpurposes) {
 287              // Check the value was legitimate.
 288              foreach ($limitpurposes as $purpose) {
 289                  if ($purpose != self::CUSTOM_INCLUDE && empty($this->purposes[$purpose])) {
 290                      throw new \coding_exception('$limitpurposes can only include purposes defined in object');
 291                  }
 292              }
 293  
 294              // Filter the fields to include only those matching the purposes.
 295              $result = [];
 296              foreach ($this->fields as $key => $purposes) {
 297                  foreach ($limitpurposes as $purpose) {
 298                      if (array_key_exists($purpose, $purposes)) {
 299                          $result[] = $key;
 300                          break;
 301                      }
 302                  }
 303              }
 304              return $result;
 305          } else {
 306              return array_keys($this->fields);
 307          }
 308      }
 309  
 310      /**
 311       * Gets fields required for user pictures.
 312       *
 313       * The results include only basic field names (columns from the 'user' database table).
 314       *
 315       * @return string[] All fields required for user pictures
 316       */
 317      public static function get_picture_fields(): array {
 318          return ['id', 'picture', 'firstname', 'lastname', 'firstnamephonetic', 'lastnamephonetic',
 319                  'middlename', 'alternatename', 'imagealt', 'email'];
 320      }
 321  
 322      /**
 323       * Gets fields required for user names.
 324       *
 325       * The results include only basic field names (columns from the 'user' database table).
 326       *
 327       * Fields are usually returned in a specific order, which the fullname() function depends on.
 328       * If you specify 'true' to the $strangeorder flag, then the firstname and lastname fields
 329       * are moved to the front; this is useful in a few places in existing code. New code should
 330       * avoid requiring a particular order.
 331       *
 332       * @param bool $differentorder In a few places, a different order of fields is required
 333       * @return string[] All fields used to display user names
 334       */
 335      public static function get_name_fields(bool $differentorder = false): array {
 336          $fields = ['firstnamephonetic', 'lastnamephonetic', 'middlename', 'alternatename',
 337                  'firstname', 'lastname'];
 338          if ($differentorder) {
 339              return array_merge(array_slice($fields, -2), array_slice($fields, 0, -2));
 340          } else {
 341              return $fields;
 342          }
 343      }
 344  
 345      /**
 346       * Gets all fields required for user identity. These fields should be included in tables
 347       * showing lists of users (in addition to the user's name which is included as standard).
 348       *
 349       * The results include basic field names (columns from the 'user' database table) and, unless
 350       * turned off, custom profile field names in the format 'profile_field_myfield', note these
 351       * fields will always be returned lower cased to match how they are returned by the DML library.
 352       *
 353       * This function does all the required capability checks to see if the current user is allowed
 354       * to see them in the specified context. You can pass context null to get all the fields
 355       * without checking permissions.
 356       *
 357       * @param \context|null $context Context; if not supplied, all fields will be included without checks
 358       * @param bool $allowcustom If true, custom profile fields will be included
 359       * @return string[] Array of required fields
 360       * @throws \coding_exception
 361       */
 362      public static function get_identity_fields(?\context $context, bool $allowcustom = true): array {
 363          global $CFG;
 364  
 365          // Only users with permission get the extra fields.
 366          if ($context && !has_capability('moodle/site:viewuseridentity', $context)) {
 367              return [];
 368          }
 369  
 370          // Split showuseridentity on comma (filter needed in case the showuseridentity is empty).
 371          $extra = array_filter(explode(',', $CFG->showuseridentity));
 372  
 373          // If there are any custom fields, remove them if necessary (either if allowcustom is false,
 374          // or if the user doesn't have access to see them).
 375          foreach ($extra as $key => $field) {
 376              if (preg_match(self::PROFILE_FIELD_REGEX, $field, $matches)) {
 377                  $allowed = false;
 378                  if ($allowcustom) {
 379                      require_once($CFG->dirroot . '/user/profile/lib.php');
 380  
 381                      // Ensure the field exists (it may have been deleted since user identity was configured).
 382                      $field = profile_get_custom_field_data_by_shortname($matches[1], false);
 383                      if ($field !== null) {
 384                          $fieldinstance = profile_get_user_field($field->datatype, $field->id, 0, $field);
 385                          $allowed = $fieldinstance->is_visible($context);
 386                      }
 387                  }
 388                  if (!$allowed) {
 389                      unset($extra[$key]);
 390                  }
 391              }
 392          }
 393  
 394          // For standard user fields, access is controlled by the hiddenuserfields option and
 395          // some different capabilities. Check and remove these if the user can't access them.
 396          $hiddenfields = array_filter(explode(',', $CFG->hiddenuserfields));
 397          $hiddenidentifiers = array_intersect($extra, $hiddenfields);
 398  
 399          if ($hiddenidentifiers) {
 400              if (!$context) {
 401                  $canviewhiddenuserfields = true;
 402              } else if ($context->get_course_context(false)) {
 403                  // We are somewhere inside a course.
 404                  $canviewhiddenuserfields = has_capability('moodle/course:viewhiddenuserfields', $context);
 405              } else {
 406                  // We are not inside a course.
 407                  $canviewhiddenuserfields = has_capability('moodle/user:viewhiddendetails', $context);
 408              }
 409  
 410              if (!$canviewhiddenuserfields) {
 411                  // Remove hidden identifiers from the list.
 412                  $extra = array_diff($extra, $hiddenidentifiers);
 413              }
 414          }
 415  
 416          // Re-index the entries and return.
 417          $extra = array_values($extra);
 418          return array_map([core_text::class, 'strtolower'], $extra);
 419      }
 420  
 421      /**
 422       * Gets SQL that can be used in a query to get the necessary fields.
 423       *
 424       * The result of this function is an object with fields 'selects', 'joins', 'params', and
 425       * 'mappings'.
 426       *
 427       * If not empty, the list of selects will begin with a comma and the list of joins will begin
 428       * and end with a space. You can include the result in your existing query like this:
 429       *
 430       * SELECT (your existing fields)
 431       *        $selects
 432       *   FROM {user} u
 433       *   JOIN (your existing joins)
 434       *        $joins
 435       *
 436       * When there are no custom fields then the 'joins' result will always be an empty string, and
 437       * 'params' will be an empty array.
 438       *
 439       * The $fieldmappings value is often not needed. It is an associative array from each field
 440       * name to an SQL expression for the value of that field, e.g.:
 441       *   'profile_field_frog' => 'uf1d_3.data'
 442       *   'city' => 'u.city'
 443       * This is helpful if you want to use the profile fields in a WHERE clause, becuase you can't
 444       * refer to the aliases used in the SELECT list there.
 445       *
 446       * The leading comma is included because this makes it work in the pattern above even if there
 447       * are no fields from the get_sql() data (which can happen if doing identity fields and none
 448       * are selected). If you want the result without a leading comma, set $leadingcomma to false.
 449       *
 450       * If the 'id' field is included then it will always be first in the list. Otherwise, you
 451       * should not rely on the field order.
 452       *
 453       * For identity fields, the function does all the required capability checks to see if the
 454       * current user is allowed to see them in the specified context. You can pass context null
 455       * to get all the fields without checking permissions.
 456       *
 457       * If your code for any reason cannot cope with custom fields then you can turn them off.
 458       *
 459       * You can have either named or ? params. If you use named params, they are of the form
 460       * uf1s_2; the first number increments in each call using a static variable in this class and
 461       * the second number refers to the field being queried. A similar pattern is used to make
 462       * join aliases unique.
 463       *
 464       * If your query refers to the user table by an alias e.g. 'u' then specify this in the $alias
 465       * parameter; otherwise it will use {user} (if there are any joins for custom profile fields)
 466       * or simply refer to the field by name only (if there aren't).
 467       *
 468       * If you need to use a prefix on the field names (for example in case they might coincide with
 469       * existing result columns from your query, or if you want a convenient way to split out all
 470       * the user data into a separate object) then you can specify one here. For example, if you
 471       * include name fields and the prefix is 'u_' then the results will include 'u_firstname'.
 472       *
 473       * If you don't want to prefix all the field names but only change the id field name, use
 474       * the $renameid parameter. (When you use this parameter, it takes precedence over any prefix;
 475       * the id field will not be prefixed, while all others will.)
 476       *
 477       * @param string $alias Optional (but recommended) alias for user table in query, e.g. 'u'
 478       * @param bool $namedparams If true, uses named :parameters instead of indexed ? parameters
 479       * @param string $prefix Optional prefix for all field names in result, e.g. 'u_'
 480       * @param string $renameid Renames the 'id' field if specified, e.g. 'userid'
 481       * @param bool $leadingcomma If true the 'selects' list will start with a comma
 482       * @return \stdClass Object with necessary SQL components
 483       */
 484      public function get_sql(string $alias = '', bool $namedparams = false, string $prefix = '',
 485              string $renameid = '', bool $leadingcomma = true): \stdClass {
 486          global $DB;
 487  
 488          $fields = $this->get_required_fields();
 489  
 490          $selects = '';
 491          $joins = '';
 492          $params = [];
 493          $mappings = [];
 494  
 495          $unique = self::$uniqueidentifier++;
 496          $fieldcount = 0;
 497  
 498          if ($alias) {
 499              $usertable = $alias . '.';
 500          } else {
 501              // If there is no alias, we still need to use {user} to identify the table when there
 502              // are joins with other tables. When there are no customfields then there are no joins
 503              // so we can refer to the fields by name alone.
 504              $gotcustomfields = false;
 505              foreach ($fields as $field) {
 506                  if (preg_match(self::PROFILE_FIELD_REGEX, $field, $matches)) {
 507                      $gotcustomfields = true;
 508                      break;
 509                  }
 510              }
 511              if ($gotcustomfields) {
 512                  $usertable = '{user}.';
 513              } else {
 514                  $usertable = '';
 515              }
 516          }
 517  
 518          foreach ($fields as $field) {
 519              if (preg_match(self::PROFILE_FIELD_REGEX, $field, $matches)) {
 520                  // Custom profile field.
 521                  $shortname = $matches[1];
 522  
 523                  $fieldcount++;
 524  
 525                  $fieldalias = 'uf' . $unique . 'f_' . $fieldcount;
 526                  $dataalias = 'uf' . $unique . 'd_' . $fieldcount;
 527                  if ($namedparams) {
 528                      $withoutcolon = 'uf' . $unique . 's' . $fieldcount;
 529                      $placeholder = ':' . $withoutcolon;
 530                      $params[$withoutcolon] = $shortname;
 531                  } else {
 532                      $placeholder = '?';
 533                      $params[] = $shortname;
 534                  }
 535                  $joins .= " JOIN {user_info_field} $fieldalias ON " .
 536                                   $DB->sql_equal($fieldalias . '.shortname', $placeholder, false) . "
 537                         LEFT JOIN {user_info_data} $dataalias ON $dataalias.fieldid = $fieldalias.id
 538                                   AND $dataalias.userid = {$usertable}id";
 539                  // For Oracle we need to convert the field into a usable format.
 540                  $fieldsql = $DB->sql_compare_text($dataalias . '.data', 255);
 541                  $selects .= ", $fieldsql AS $prefix$field";
 542                  $mappings[$field] = $fieldsql;
 543              } else {
 544                  // Standard user table field.
 545                  $selects .= ", $usertable$field";
 546                  if ($field === 'id' && $renameid && $renameid !== 'id') {
 547                      $selects .= " AS $renameid";
 548                  } else if ($prefix) {
 549                      $selects .= " AS $prefix$field";
 550                  }
 551                  $mappings[$field] = "$usertable$field";
 552              }
 553          }
 554  
 555          // Add a space to the end of the joins list; this means it can be appended directly into
 556          // any existing query without worrying about whether the developer has remembered to add
 557          // whitespace after it.
 558          if ($joins) {
 559              $joins .= ' ';
 560          }
 561  
 562          // Optionally remove the leading comma.
 563          if (!$leadingcomma) {
 564              $selects = ltrim($selects, ' ,');
 565          }
 566  
 567          return (object)['selects' => $selects, 'joins' => $joins, 'params' => $params,
 568                  'mappings' => $mappings];
 569      }
 570  
 571      /**
 572       * Similar to {@see \moodle_database::sql_fullname} except it returns all user name fields as defined by site config, in a
 573       * single select statement suitable for inclusion in a query/filter for a users fullname, e.g.
 574       *
 575       * [$select, $params] = fields::get_sql_fullname('u');
 576       * $users = $DB->get_records_sql_menu("SELECT u.id, {$select} FROM {user} u", $params);
 577       *
 578       * @param string|null $tablealias User table alias, if set elsewhere in the query, null if not required
 579       * @param bool $override If true then the alternativefullnameformat format rather than fullnamedisplay format will be used
 580       * @return array SQL select snippet and parameters
 581       */
 582      public static function get_sql_fullname(?string $tablealias = 'u', bool $override = false): array {
 583          global $DB;
 584  
 585          $unique = self::$uniqueidentifier++;
 586  
 587          $namefields = self::get_name_fields();
 588  
 589          // Create a dummy user object containing all name fields.
 590          $dummyuser = (object) array_combine($namefields, $namefields);
 591          $dummyfullname = fullname($dummyuser, $override);
 592  
 593          // Extract any name fields from the fullname format in the order that they appear.
 594          $matchednames = array_values(order_in_string($namefields, $dummyfullname));
 595          $namelookup = $namepattern = $elements = $params = [];
 596  
 597          foreach ($namefields as $index => $namefield) {
 598              $namefieldwithalias = $tablealias ? "{$tablealias}.{$namefield}" : $namefield;
 599  
 600              // Coalesce the name fields to ensure we don't return null.
 601              $emptyparam = "uf{$unique}ep_{$index}";
 602              $namelookup[$namefield] = "COALESCE({$namefieldwithalias}, :{$emptyparam})";
 603              $params[$emptyparam] = '';
 604  
 605              $namepattern[] = '\b' . preg_quote($namefield) . '\b';
 606          }
 607  
 608          // Grab any content between the name fields, inserting them after each name field.
 609          $chunks = preg_split('/(' . implode('|', $namepattern) . ')/', $dummyfullname);
 610          foreach ($chunks as $index => $chunk) {
 611              if ($index > 0) {
 612                  $elements[] = $namelookup[$matchednames[$index - 1]];
 613              }
 614  
 615              if (core_text::strlen($chunk) > 0) {
 616                  // If content is just whitespace, add to elements directly (also Oracle doesn't support passing ' ' as param).
 617                  if (preg_match('/^\s+$/', $chunk)) {
 618                      $elements[] = "'$chunk'";
 619                  } else {
 620                      $elementparam = "uf{$unique}fp_{$index}";
 621                      $elements[] = ":{$elementparam}";
 622                      $params[$elementparam] = $chunk;
 623                  }
 624              }
 625          }
 626  
 627          return [$DB->sql_concat(...$elements), $params];
 628      }
 629  
 630      /**
 631       * Gets the display name of a given user field.
 632       *
 633       * Supports field names from the 'user' database table, and custom profile fields supplied in
 634       * the format 'profile_field_xx'.
 635       *
 636       * @param string $field Field name in database
 637       * @return string Field name for display to user
 638       * @throws \coding_exception
 639       */
 640      public static function get_display_name(string $field): string {
 641          global $CFG;
 642  
 643          // Custom fields have special handling.
 644          if (preg_match(self::PROFILE_FIELD_REGEX, $field, $matches)) {
 645              require_once($CFG->dirroot . '/user/profile/lib.php');
 646              $fieldinfo = profile_get_custom_field_data_by_shortname($matches[1], false);
 647              // Use format_string so it can be translated with multilang filter if necessary.
 648              return $fieldinfo ? format_string($fieldinfo->name) : $field;
 649          }
 650  
 651          // Some fields have language strings which are not the same as field name.
 652          switch ($field) {
 653              case 'picture' : {
 654                  return get_string('pictureofuser');
 655              }
 656          }
 657          // Otherwise just use the same lang string.
 658          return get_string($field);
 659      }
 660  
 661      /**
 662       * Resets the unique identifier used to ensure that multiple SQL fragments generated in the
 663       * same request will have different identifiers for parameters and table aliases.
 664       *
 665       * This is intended only for use in unit testing.
 666       */
 667      public static function reset_unique_identifier() {
 668          self::$uniqueidentifier = 1;
 669      }
 670  
 671      /**
 672       * Checks if a field name looks like a custom profile field i.e. it begins with profile_field_
 673       * (does not check if that profile field actually exists).
 674       *
 675       * @param string $fieldname Field name
 676       * @return string Empty string if not a profile field, or profile field name (without profile_field_)
 677       */
 678      public static function match_custom_field(string $fieldname): string {
 679          if (preg_match(self::PROFILE_FIELD_REGEX, $fieldname, $matches)) {
 680              return $matches[1];
 681          } else {
 682              return '';
 683          }
 684      }
 685  }