You are here

Query Builder in Privatemsg 6.2

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

Namesort descending Location Description
hook_privatemsg_sql_autocomplete_alter ./privatemsg.api.php Query to search for autocomplete usernames.
hook_privatemsg_sql_list_alter ./privatemsg.api.php Display a list of threads.
hook_privatemsg_sql_load_alter ./privatemsg.api.php Query definition to load a message.
hook_privatemsg_sql_messages_alter ./privatemsg.api.php Query definition to load messages of one or multiple threads.
hook_privatemsg_sql_participants_alter ./privatemsg.api.php Alter the query that loads the participants of a thread.
hook_privatemsg_sql_unread_count_alter ./privatemsg.api.php Loads all unread messages of a user (only the count query is used).
privatemsg_filter_add_tag_submit privatemsg_filter/privatemsg_filter.module Form callback for adding a tag to threads.
privatemsg_filter_form privatemsg_filter/privatemsg_filter.module Form to show and allow modification of tagging information of a conversation.
privatemsg_filter_form_submit privatemsg_filter/privatemsg_filter.module
privatemsg_filter_privatemsg_message_insert privatemsg_filter/privatemsg_filter.module Implements hook_privatemsg_message_insert().
privatemsg_filter_privatemsg_message_recipient_changed privatemsg_filter/privatemsg_filter.module Implements hook_privatemsg_message_recipient_changed().
privatemsg_filter_privatemsg_sql_autocomplete_alter privatemsg_filter/privatemsg_filter.module Limit the user autocomplete for the filter widget.
privatemsg_filter_privatemsg_sql_autocomplete_alter privatemsg_filter/privatemsg_filter.module Limit the user autocomplete for the filter widget.
privatemsg_filter_privatemsg_sql_list_alter privatemsg_filter/privatemsg_filter.module Hook into the query builder to add the tagging info to the correct query
privatemsg_filter_privatemsg_view_messages_alter privatemsg_filter/privatemsg_filter.module Hook into the view messages page to add a form for tagging purposes.
privatemsg_filter_remove_tag_submit privatemsg_filter/privatemsg_filter.module Form callback for removing a tag to threads.
privatemsg_filter_show_tags privatemsg_filter/privatemsg_filter.module
privatemsg_filter_sql_tags privatemsg_filter/privatemsg_filter.module Query definition to fetch tags.
privatemsg_filter_sql_tags privatemsg_filter/privatemsg_filter.module Query definition to fetch tags.
privatemsg_filter_sql_tags_autocomplete privatemsg_filter/privatemsg_filter.module Query definition to get autocomplete suggestions for tags
privatemsg_filter_sql_tags_autocomplete privatemsg_filter/privatemsg_filter.module Query definition to get autocomplete suggestions for tags
privatemsg_filter_tags_autocomplete privatemsg_filter/privatemsg_filter.module Return autocomplete results for tags.
privatemsg_filter_user privatemsg_filter/privatemsg_filter.module Implement hook_user().
privatemsg_sql_autocomplete ./privatemsg.module Query definition to search for username autocomplete suggestions.
privatemsg_sql_deleted ./privatemsg.module Query Builder function to load all messages that should be flushed.
privatemsg_sql_list ./privatemsg.module Query definition to load a list of threads.
privatemsg_sql_load ./privatemsg.module Query function for loading a single or multiple messages.
privatemsg_sql_messages ./privatemsg.module Query definition to load messages of one or multiple threads.
privatemsg_sql_participants ./privatemsg.module Load all participants of a thread.
privatemsg_sql_privatemsg_query_settings ./privatemsg.module
privatemsg_sql_unread_count ./privatemsg.module Query definition to count unread messages.
privatemsg_user ./privatemsg.module
_privatemsg_assemble_query ./privatemsg.module Generates a query based on a query id.