You are here

Home » API reference » Privatemsg 6.2 » privatemsg.module

function privatemsg_sql_list in Privatemsg 6.2

Same name and namespace in other branches
  1. 6 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

Query Builder
Privatemsg does use its own simple query builder which allows to extend SELECT-Queries in an easy way. The function _privatemsg_assemble_query creates the query, based on an array $fragments with the following content. Except primary_table, each key…

File

./privatemsg.module, line 1035
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 = privatemsg_get_enabled_headers();

  // 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 pmia.type || '_' || textin(int4out(pmia.recipient))\n                                                            FROM {pm_index} pmia\n                                                            WHERE pmia.type <> 'hidden' AND pmia.thread_id = pmi.thread_id AND NOT (pmia.type = 'user' AND pmia.recipient = %d)), ',') AS participants";
    }
    else {
      $fragments['select'][] = "(SELECT GROUP_CONCAT(DISTINCT CONCAT(pmia.type, '_', pmia.recipient) SEPARATOR ',')\n                                                            FROM {pm_index} pmia\n                                                            WHERE pmia.type <> 'hidden' AND pmia.thread_id = pmi.thread_id AND NOT (pmia.type = 'user' AND pmia.recipient = %d)) AS participants";
    }
    $fragments['query_args']['select'][] = $account->uid;
  }
  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.recipient = %d AND pmi.type IN ('user', 'hidden')";
  $fragments['query_args']['where'][] = $account->uid;
  $fragments['where'][] = 'pmi.deleted = 0';
  $fragments['group_by'][] = 'pmi.thread_id';

  // 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_get_headers()), 9);
  $fragments['order_by'][] = $order_by;
}