Let's say we have this code in core and it has addTag('user_search'). This means we are able to alter this query from realname module.

/**
 * Implements hook_search_execute().
 */
function user_search_execute($keys = NULL, $conditions = NULL) {
  $find = array();
  // Replace wildcards with MySQL/PostgreSQL wildcards.
  $keys = preg_replace('!\*+!', '%', $keys);
  $query = db_select('users')->extend('PagerDefault');
  // Tag the user search query to allow other modules to alter the query.
  $query->addTag('user_search');
  $query->fields('users', array('uid'));
  if (user_access('administer users')) {
    // Administrators can also search in the otherwise private email field,
    // and they don't need to be restricted to only active users.
    $query->fields('users', array('mail'));
    $query->condition(db_or()->
      condition('name', '%' . db_like($keys) . '%', 'LIKE')->
      condition('mail', '%' . db_like($keys) . '%', 'LIKE'));
  }
  else {
    // Regular users can only search via usernames, and we do not show them
    // blocked accounts.
    $query->condition('name', '%' . db_like($keys) . '%', 'LIKE')
      ->condition('status', 1);
  }
  $uids = $query
    //->limit(15)
    ->execute()
    ->fetchCol();
...
}

So I added this code to realname and tried to alter the query. The JOIN works well, but I cannot get the condition into proper place and the $keys need to be collected from $query, but I do not understand how. There is zero documentation available.

/**
 * Implements hook_query_alter().
 */
function realname_query_alter(QueryAlterableInterface $query) {
  if ($query->hasTag('user_search')) {
    // Join realname module table. (WORKS)
    $query->leftJoin('realname', 'r', 'r.uid = users.uid');

    // Extend the or condition (BROKEN)
    $query->condition(db_or()->
      condition('r.realname', '%' . db_like($keys) . '%', 'LIKE'));
    );
  }
}

Can someone help here, please?