function privatemsg_sql_list

1 privatemsg.module privatemsg_sql_list(&$fragments, $account, $argument = 'list')
7-1 privatemsg.module privatemsg_sql_list($account, $argument = 'list')
7-2 privatemsg.module privatemsg_sql_list($account, $argument = 'list')
6-2 privatemsg.module privatemsg_sql_list(&$fragments, $account, $argument = 'list')

Query definition to load a list of threads.

Parameters

$account: User object for which the messages are being loaded.

$argument: string argument which can be used in the query builder to modify the thread listing.

See also

hook_query_privatemsg_list_alter()

Related topics

File

./privatemsg.module, line 1026
Allows users to send private messages to other users.

Code

function privatemsg_sql_list($account, $argument = 'list') {
  $query = db_select('pm_message', 'pm')->extend('TableSort')->extend('PagerDefault');
  $query->join('pm_index', 'pmi', 'pm.mid = pmi.mid');

  // Create count query;
  $count_query = db_select('pm_message', 'pm');
  $count_query->addExpression('COUNT(DISTINCT pmi.thread_id)', 'count');
  $count_query->join('pm_index', 'pmi', 'pm.mid = pmi.mid');
  $count_query->condition('pmi.recipient', $account->uid)->condition('pmi.type', array('hidden', 'user'))->condition('pmi.deleted', 0);
  $query->setCountQuery($count_query);


  // Required columns
  $query->addField('pmi', 'thread_id');
  $query->addExpression('MIN(pm.subject)', 'subject');
  $query->addExpression('MAX(pm.timestamp)', 'last_updated');
  $query->addExpression('MAX(pm.has_tokens)', 'has_tokens');
  $query->addExpression('SUM(pmi.is_new)', 'is_new');

  // Needed to for tracking replies.
  $query->addExpression('MAX(pm.reply_to_mid)', 'last_reply_to_mid');

  // Load enabled columns
  $fields = privatemsg_get_enabled_headers();

  if (in_array('count', $fields)) {
    // We only want the distinct number of messages in this thread.
    $query->addExpression('COUNT(distinct pmi.mid)', 'count');
  }
  if (in_array('participants', $fields)) {
    // Query for a string with uids, for example "1,6,7". This needs a subquery on PostgreSQL.
    if (db_driver() == 'pgsql') {
      $query->addExpression("array_to_string(array(SELECT DISTINCT pmia.type || '_' || pmia.recipient
                                                          FROM {pm_index} pmia
                                                          WHERE pmia.type <> 'hidden' AND pmia.thread_id = pmi.thread_id AND pmia.recipient <> :current), ',')", 'participants', array(':current' => $account->uid));
    }
    else {
      $query->addExpression("(SELECT GROUP_CONCAT(DISTINCT CONCAT(pmia.type, '_', pmia.recipient))
                                     FROM {pm_index} pmia
                                     WHERE pmia.type <> 'hidden' AND pmia.thread_id = pmi.thread_id AND pmia.recipient <> :current)", 'participants', array(':current' => $account->uid));
    }
  }
  if (in_array('thread_started', $fields)) {
    $query->addExpression('MIN(pm.timestamp)', 'thread_started');
  }
  return $query->condition('pmi.recipient', $account->uid)->condition('pmi.type', array('hidden', 'user'))->condition('pmi.deleted', 0)->groupBy('pmi.thread_id')->orderByHeader(privatemsg_get_headers())->limit(variable_get('privatemsg_per_page', 25));
}