These queries were proposed by Berdir (maintainer).

Try something like this (untested):

un-answered (We look for threads where the user is recipient but not author):
SELECT COUNT(DISTINCT thread_id) FROM {pm_index} pmi WHERE pmi.uid = %d AND thread_id NOT IN (SELECT thread_id FROM {pm_index} pmsi INNER JOIN {pm_message} pm ON pm.mid = pmsi.mid WHERE pmi.uid = %d AND pm.author = %d)

%d = user id, you need to remove the curly braces if you want to execute the query directly.

Not author of the first message. This is almost the same as above, we only want an additional check to only look for the author of the first message (mid = thread_id), so we join on mid = thread_id
SELECT COUNT(DISTINCT thread_id) FROM {pm_index} pmi WHERE pmi.uid = %d AND thread_id NOT IN (SELECT thread_id FROM {pm_index} pmsi INNER JOIN {pm_message} pm ON pm.mid = pmsi.thread_id WHERE pmi.uid = %d AND pm.author = %d)

Here are some examples of how to use this idea to extend the API of the module for Drupal 7 (the following two functions are not actually part of the API).

/**
 * Return the number of threads in which a user is a participant but did not
 * start the thread.
 *
 * @param $uid
 *   The user ID of the participant.
 *
 * @ingroup api
 */
function privatemsg_thread_count_received ($uid) {
  $query = "SELECT COUNT(DISTINCT thread_id) FROM {pm_index} pmi WHERE pmi.recipient = :uid AND thread_id NOT IN (SELECT thread_id FROM {pm_index} pmsi INNER JOIN {pm_message} pm ON pm.mid = pmsi.thread_id WHERE pmi.recipient = :uid AND pm.author = :uid)";
  return db_query($query, array(':uid' => $uid))->fetchField();
}

/**
 * Return the number of threads to which a participant has not replied.
 * Note: This count includes threads to which the participant cannot reply.
 *
 * @param $uid
 *   The user ID of the participant.
 *
 * @ingroup api
 */
function privatemsg_thread_count_noreply ($uid) {
  $query = "SELECT COUNT(DISTINCT thread_id) FROM {pm_index} pmi WHERE pmi.recipient = :uid AND thread_id NOT IN (SELECT thread_id FROM {pm_index} pmsi INNER JOIN {pm_message} pm ON pm.mid = pmsi.mid WHERE pmi.recipient = :uid AND pm.author = :uid)";
  return db_query($query, array(':uid' => $uid))->fetchField();
}

Comments

steve-loves-drupal’s picture

anyone? :(

steve-loves-drupal’s picture

Can someone help me make a slight modification to this? How would it be possible to print the total count for ALL messages in the INBOX and also for OUTBOX? I've been trying so many variations of the above code with no luck. Would really appreciate any help.

Thank you