Query Builder in Privatemsg 6

Same name and namespace in other branches

1. 6.2 privatemsg.api.php \sql
2. 7.2 privatemsg.api.php \sql
3. 7 privatemsg.api.php \sql

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 is an array itself to allow multiple values

• primary_table: The main table to select from
• select: The fields that should be selected. This can be a simple field, a field with alias or even a subquery.
• inner_join: The tables that should be joined. This is not specific to inner joins. Example: INNER JOIN pm_index pmi ON (pmi.mid = pm.mid)
• where: The where conditions. The conditions are always AND, but it is possible to use OR inside a condition. Example: ⁽pmi.is_new = 1 OR pmi.deleted = 1)
• order_by: Order By values, example: pm.timestamp ASC
• query_args: It is possible to use the placeholders like %s in each part of the query. The values of query_args are then inserted into these. query_args consists of three arrays (join, where, having), one for each key that currently supports arguments.

Use _privatemsg_assemble_query

The privatemsg_assemble_query function takes a query_id as first argument and optionally one or multiple arguments. query_id can either be a string ('some_id') or an array('group_name', 'query_id'), if a string is supplied, group_name defaults to 'privatemsg'. Returned is an array with the keys 'query' (normal query) and 'count' (count query for pager).

For the actual query data, the function group_name_sql_query_id is executed, this functions does have $fragments as first parameter and then the optional parameters.

A short example:

// First, create the sql function.
function privatemsg_sql_getsubject(&$fragments, $mid, $uid) {
  // Set the primary table.
  $fragments['primary_table'] = '{pm_message} pm';

  // Add a field.
  $fragments['select'][] = 'pm.subject';

  // Join another table.
  $fragment['inner_join'][] = 'JOIN {pm_index} pi ON (pi.mid = pm.mid)';
  $fragment['query_args']['join'][] $uid;

  // And finally add a condition.
  $fragments['where'][] = 'pm.mid = %d';
  $fragments['query_args']['where'][] = $mid;
}

// Now we can use that query everywhere.
$query = _privatemsg_assemble_query('getsubject', 5);
$result = db_query($query['query']);

Extend existing queries

To extend a privatemsg query, use hook_group_name_sql_query_id_alter. This hook does use the same parameters as the sql function.

Example:

function mymodule_privatemsg_sql_getsubject_alter(&$fragments, $mid) {

  // we want to load the body too..
  $fragments['select'][] = 'pm.body';
}

• List of sql query hooks.
  • list: List of messages, Parameters: $fragments, $accounty
  • list_sent: List of sent messages, Parameters: $fragments, $accounty
  • load: Load a single message, Parameters: $fragments, $pmid, $account
  • messages: Load the messages of a thread, Parameters: $fragments, $thread_id, $account
  • autocomplete: Searching usernames for the autocomplete feature, Parameters: $fragments, $search, $names
  • participants: Loads all participants of a thread, Parameters: $fragments, $thread_id
  • unread_count: Number of unread messages for a user, Parameters: $fragments, $account

• The following query_id's are used in pm_block_user
  • threadautors: Return all authors of one or multiple threads, Parameters: $fragments, $threads

File

./privatemsg.api.php, line 21

Privatemsg API Documentation

Functions