function privatemsg_filter_sql_tags in Privatemsg 7.2

Same name and namespace in other branches

1. 6.2 privatemsg_filter/privatemsg_filter.module \privatemsg_filter_sql_tags()
2. 6 privatemsg_filter/privatemsg_filter.module \privatemsg_filter_sql_tags()
3. 7 privatemsg_filter/privatemsg_filter.module \privatemsg_filter_sql_tags()

Query definition to get the tags in use by the specified user.

Parameters

$user: User object for whom we want the tags.

$threads: Array of thread ids, defaults to all threads of a user.

$limit: Limit the number of tags *per thread*.

File

privatemsg_filter/privatemsg_filter.module, line 894

Allows users to tag private messages and to filter based upon those tags.

Code

function privatemsg_filter_sql_tags($user = NULL, $threads = NULL, $limit = NULL, $showHidden = FALSE) {
  $query = db_select('pm_tags', 't')
    ->fields('t', array(
    'tag_id',
    'tag',
    'public',
  ))
    ->orderBy('t.tag', 'ASC');
  if (!empty($threads)) {
    $query
      ->addField('ti', 'thread_id');
    $query
      ->join('pm_tags_index', 'ti', 'ti.tag_id = t.tag_id');
    $query
      ->condition('ti.thread_id', $threads);
  }
  else {
    $query
      ->addExpression('COUNT(ti.thread_id)', 'count');
    $query
      ->leftJoin('pm_tags_index', 'ti', 'ti.tag_id = t.tag_id');
    $query
      ->groupBy('t.tag_id')
      ->groupBy('t.tag')
      ->groupBy('t.public');
  }
  if (!empty($user)) {
    $query
      ->condition('ti.uid', $user->uid);
  }
  if (!$showHidden) {
    $query
      ->condition(db_or()
      ->condition('t.hidden', 0)
      ->isNull('t.hidden'));
  }

  // Only select n tags per thread (ordered per tag_id), see
  // http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/.
  //
  // It does select how many tags for that thread/uid combination exist that
  // have a lower tag_id and does only select those that have less than $limit.
  //
  // This should only have a very minor performance impact as most users won't
  // tag a thread with 1000 different tags.
  if ($limit) {
    $query
      ->where('(SELECT count(*) FROM {pm_tags_index} AS pmtic
                    WHERE pmtic.thread_id = ti.thread_id
                    AND pmtic.uid = ti.uid
                    AND pmtic.tag_id < ti.tag_id) < :limit', array(
      ':limit' => $limit,
    ));
  }
  elseif (!empty($thread_id) || !empty($user)) {
    $query
      ->orderBy('t.tag', 'ASC');
  }
  return $query;
}