function _user_relationships_generate_query in User Relationships 7

Same name and namespace in other branches

1. 5.3 user_relationships_api/user_relationships_api.module \_user_relationships_generate_query()
2. 5.2 user_relationships.module \_user_relationships_generate_query()
3. 6 user_relationships_api/user_relationships_api.module \_user_relationships_generate_query()

Helper function to generate the main and count queries from a list of parameters and options

2 calls to _user_relationships_generate_query()

theme_user_relationship_implications_page in user_relationship_implications/user_relationship_implications.module

Categorized list of relationships for a given user

user_relationships_load in ./user_relationships.module

Load relationship objects from the database.

File

./user_relationships.module, line 105

User Relationships API. Module shell.

Code

function _user_relationships_generate_query($param = array(), $options = array()) {
  extract($options, EXTR_SKIP);

  // #479486.
  $twoway_reverse_clause = FALSE;

  // Doing a JOIN on the {user_relationship_types} table is rather slow as
  // MySQL has to use ALL for the table join. On most sites, there will only be
  // a few relationship types, and there will nearly always be a far higher
  // number of relationships. So, we split the query out into PHP saving us
  // from having to do the JOIN.
  $relationship_types = user_relationships_types_load();
  $oneway_relationship_type_ids = array();
  foreach ($relationship_types as $rt) {
    if ($rt->is_oneway) {
      $oneway_relationship_type_ids[] = $rt->rtid;
    }
  }

  // Turn the conditions into a query.
  $query = db_select('user_relationships', 'ur');
  $query
    ->innerJoin('user_relationship_types', 'urt', 'urt.rtid = ur.rtid');
  foreach ($param as $key => $value) {
    if (!isset($value)) {
      continue;
    }
    switch ($key) {
      case 'between':
        $or = db_or()
          ->condition(db_and()
          ->condition('ur.requester_id', $value[0])
          ->condition('ur.requestee_id', $value[1]));
        $and = db_and()
          ->condition('ur.requestee_id', $value[0])
          ->condition('ur.requester_id', $value[1]);

        //#479486 do not include reverse records of two-way relationships by default
        if (!isset($include_twoway_reverse)) {
          $and
            ->condition(db_or()
            ->condition('ur.approved', 1, '<>')
            ->condition('urt.is_oneway', 0, '<>'));
          if (!empty($oneway_relationship_type_ids)) {
            $and
              ->condition(db_or()
              ->condition('ur.approved', 1, '<>')
              ->condition('ur.rtid', $oneway_relationship_type_ids));
          }
          else {
            $and
              ->condition(db_or()
              ->condition('ur.approved', 1, '<>'));
          }
          $twoway_reverse_clause = TRUE;
        }
        $or
          ->condition($and);
        $query
          ->condition($or);
        break;
      case 'user':

        //#479486 when showing all user's relationships, do not include reverse records of two-way relationships
        if (!isset($include_twoway_reverse)) {
          if (!empty($oneway_relationship_type_ids)) {
            $query
              ->condition(db_or()
              ->condition('ur.requester_id', $value)
              ->condition(db_and()
              ->condition(db_or()
              ->condition('ur.approved', 1, '<>')
              ->condition('ur.rtid', $oneway_relationship_type_ids))
              ->condition('ur.requestee_id', $value)));
          }
          else {
            $query
              ->condition(db_or()
              ->condition('ur.requester_id', $value)
              ->condition(db_and()
              ->condition(db_or()
              ->condition('ur.approved', 1, '<>'))
              ->condition('ur.requestee_id', $value)));
          }
          $twoway_reverse_clause = TRUE;
        }
        else {
          $query
            ->condition(db_or()
            ->condition('ur.requester_id', $value)
            ->condition('ur.requestee_id', $value));
        }
        $arguments[] = $value;
        break;
      case 'exclude_rtids':
        $query
          ->condition('ur.rtid', $value, 'NOT IN');
        break;
      case 'rid':
      case 'requester_id':
      case 'requestee_id':

        // #479486 these columns automatically should exclude duplicates.
        $twoway_reverse_clause = TRUE;
      default:
        $types_cols = array(
          'name',
          'plural_name',
          'is_oneway',
          'is_reciprocal',
          'requires_approval',
          'expires_val',
        );
        $prefix = !in_array($key, $types_cols) ? 'ur' : 'urt';
        $query
          ->condition($prefix . '.' . $key, $value);
    }
  }

  //#479486 add a general clause that removed reverse direction from two-way relationship results, unless it's been addressed above
  if (!$twoway_reverse_clause && !isset($include_twoway_reverse)) {
    if (!empty($oneway_relationship_type_ids)) {
      $query
        ->condition(db_or()
        ->condition('ur.rtid', $oneway_relationship_type_ids)
        ->condition('ur.approved', 1, '<>')
        ->where('ur.requester_id < ur.requestee_id'));
    }
    else {
      $query
        ->condition(db_or()
        ->condition('ur.approved', 1, '<>')
        ->where('ur.requester_id < ur.requestee_id'));
    }
    $twoway_reverse_clause = TRUE;
  }
  if (!empty($only_count) || !empty($paging)) {
    $count = clone $query;
    $count
      ->addExpression('COUNT(DISTINCT rid)', 'count');
  }
  $query
    ->distinct()
    ->fields('ur')
    ->fields('urt');
  if (isset($include_user_info) && $include_user_info) {
    $query
      ->addField('requesters', 'name', 'requester_name');
    $query
      ->addField('requesters', 'mail', 'requester_mail');
    $query
      ->addField('requesters', 'data', 'requester_data');
    $query
      ->addField('requesters', 'picture', 'requester_picture');
    $query
      ->addField('requestees', 'name', 'requestee_name');
    $query
      ->addField('requestees', 'mail', 'requestee_mail');
    $query
      ->addField('requestees', 'data', 'requestee_data');
    $query
      ->addField('requestees', 'picture', 'requestee_picture');
    $query
      ->innerJoin('users', 'requesters', 'ur.requester_id = requesters.uid');
    $query
      ->innerJoin('users', 'requestees', 'ur.requestee_id = requestees.uid');
  }
  if (!empty($order)) {
    if (is_array($order)) {
      $query
        ->orderBy($order[0], $order[1]);
    }
    else {
      $query
        ->orderBy($order);
    }
  }
  if (!empty($limit)) {
    $offset = !empty($offset) ? $offset : 0;
    $query
      ->range($offset, $limit);
  }
  if (!empty($only_count)) {
    return $count;
  }
  if (!empty($paging)) {
    $query = $query
      ->extend('PagerDefault')
      ->limit($paging);
    $query
      ->setCountQuery($count);
  }
  if (!empty($header)) {
    $query = $query
      ->extend('TableSort')
      ->orderByHeader($header);
  }
  return $query;
}