function privatemsg_sql_list in Privatemsg 7

Same name and namespace in other branches

1. 6.2 privatemsg.module \privatemsg_sql_list()
2. 6 privatemsg.module \privatemsg_sql_list()
3. 7.2 privatemsg.module \privatemsg_sql_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 1043

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');

  // Load enabled columns
  $fields = array_filter(variable_get('privatemsg_display_fields', array(
    'participants',
  )));
  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\n                                                          FROM {pm_index} pmia\n                                                          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))\n                                     FROM {pm_index} pmia\n                                     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_list_headers(array_merge(array(
    'subject',
    'last_updated',
  ), $fields)))
    ->limit(variable_get('privatemsg_per_page', 25));
}