function privatemsg_sql_list in Privatemsg 6

Same name and namespace in other branches

1. 6.2 privatemsg.module \privatemsg_sql_list()
2. 7.2 privatemsg.module \privatemsg_sql_list()
3. 7 privatemsg.module \privatemsg_sql_list()

Query definition to load a list of threads.

Parameters

$fragments: Query fragments array.

$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.

Related topics

File

./privatemsg.module, line 1140

Allows users to send private messages to other users.

Code

function privatemsg_sql_list(&$fragments, $account, $argument = 'list') {
  $fragments['primary_table'] = '{pm_message} pm';

  // Load enabled columns.
  $fields = array_filter(variable_get('privatemsg_display_fields', array(
    'participants',
  )));

  // Required columns.
  $fragments['select'][] = 'pmi.thread_id';

  // We have to use MIN as the subject might not be the same in some threads.
  // MIN() does not have a useful meaning except that it helps to correctly
  // aggregate the thread on PostgreSQL.
  $fragments['select'][] = 'MIN(pm.subject) as subject';
  $fragments['select'][] = 'MAX(pm.timestamp) as last_updated';

  // We use SUM so that we can count the number of unread messages.
  $fragments['select'][] = 'SUM(pmi.is_new) as is_new';

  // Select number of messages in the thread if the count is
  // set to be displayed.
  if (in_array('count', $fields)) {
    $fragments['select'][] = 'COUNT(distinct pmi.mid) as count';
  }
  if (in_array('participants', $fields)) {

    // Query for a string with uid's, for example "1,6,7".
    // @todo: Replace this with a single query similiar to the tag list.
    if ($GLOBALS['db_type'] == 'pgsql') {

      // PostgreSQL does not know GROUP_CONCAT, so a subquery is required.
      $fragments['select'][] = "array_to_string(array(SELECT DISTINCT textin(int4out(pmia.uid))\n                                                            FROM {pm_index} pmia\n                                                            WHERE pmia.thread_id = pmi.thread_id), ',') AS participants";
    }
    else {
      $fragments['select'][] = '(SELECT GROUP_CONCAT(DISTINCT pmia.uid SEPARATOR ",")
                                                            FROM {pm_index} pmia
                                                            WHERE pmia.thread_id = pmi.thread_id) AS participants';
    }
  }
  if (in_array('thread_started', $fields)) {
    $fragments['select'][] = 'MIN(pm.timestamp) as thread_started';
  }
  $fragments['inner_join'][] = 'INNER JOIN {pm_index} pmi ON pm.mid = pmi.mid';

  // Only load undeleted messages of the current user and group by thread.
  $fragments['where'][] = 'pmi.uid = %d';
  $fragments['query_args']['where'][] = $account->uid;
  $fragments['where'][] = 'pmi.deleted = 0';
  $fragments['group_by'][] = 'pmi.thread_id';
  $order_by_first = 'MAX(pmi.is_new) DESC, ';

  // MySQL 4.1 does not allow to order by aggregate functions. MAX() is used
  // to avoid a ordering bug with multiple new messages.
  if ($GLOBALS['db_type'] != 'pgsql' && version_compare(db_version(), '5.0.0') < 0) {
    $order_by_first = 'is_new DESC, ';
  }

  // tablesort_sql() generates a ORDER BY string. However, the "ORDER BY " part
  // is not needed and added by the query builder. Discard the first 9
  // characters of the string.
  $order_by = drupal_substr(tablesort_sql(_privatemsg_list_headers(FALSE, array_merge(array(
    'subject',
    'last_updated',
  ), $fields)), $order_by_first), 9);
  $fragments['order_by'][] = $order_by;
}