Show related record count in Admin List View

Hi Kitsguru,

If you wanted to display this information on the list page, the best method might be to have a text field that stores this meta information when you save it. I created a blog system recently that counted the number of blog records for each category, you could probably do somthing similar. 

Here are the basic plugin hooks:

addAction('record_postsave', 'updateMetaData', null, 1);
addAction('record_posterase', 'updateMetaData', null, 1);


function updateMetaData($tableName) {
 //Check if we're using correct table, and update the meta field if required.
 if($tableName != 'blog'){ return; }

  //Code here to loop through all TableA records, and count related TableB records and add it to the count field.

}

Then you'd just need to add this meta field to the list page, and ensure users who can edit records can't see it by making it a system field.

Let me know if you have any questions. 

Thanks!

Greg

Hi Jeff, 

To run on all the hosting accounts we want to run on we need to support long outdated versions of MySQL (9 years old) and PHP (7 years old).   So we're slowing creeping ahead with the functionality but that's a major limiting factor as well as the mysql permissions allowed on most hosts.  

You could, of course, create anything custom you wanted on your mysql install, you'd just want to make that the software as it is now doesn't interfere with it.

A good example of how counts of related records can be done is in the Newsletter Builder plugin.    For the "Mailing Lists" section it calculates the counts of users in each list on Edit or Save (of the CMS menus) and shows them on the "Mailing Lists" List page along with a "recalculate" link in the header.  The user lists could be from mysql queries or other tables we don't know about so that was a way to handle that.

For the "Messages" list page we calculate the "Opens" in realtime from a log table using a mysql count query.  This can work well if there's no performance problems.  Here's some code for that.  As you can see we either override what's output for specific fields or just makeup fields and execute queries for those to show the output.  

Also, you'll notice the function is called for both the list header values and list row values and we're determining which is which by the number of arguments passed to the function.  If you don't like that you can always just use two functions.


// plugin hooks
addFilter('listHeader_displayLabel',   'nlb_message_listColumns', null, 3);
addFilter('listRow_displayValue',      'nlb_message_listColumns', null, 4);

//
function nlb_message_listColumns($displayValue, $tableName, $fieldname, $record = array()) {
  global $TABLE_PREFIX;
  if ($tableName != '_nlb_messages') { return $displayValue; } // skip all by our table
  $isHeader = !$record; // we detect the header hook by checking if the 4th argument is set
  $isRow    = !$isHeader;

  //
  if ($fieldname == 'sent_date') {
    if ($isHeader) { return $displayValue; }
    if ($isRow && $record['send'] == 'scheduled') {
      return $record['scheduled_date'];
    }
  }


  //
  if ($fieldname == '_opens_') {
    if ($isHeader) { return t("Opens"); }
    if ($isRow) {
      if ($record['send'] != 'all') { return ''; } // only show for sent newsletters
      $where = mysql_escapef("messageNum = ? AND eventNum = ? AND subscriberNum != 0", $record['num'], 5);
      $query = "SELECT COUNT(DISTINCT(subscriberNum)) FROM {$TABLE_PREFIX}_nlb_log WHERE $where";
      list($count) = mysql_get_query($query, true);
      return number_format($count);
    }
  }

  //
  return $displayValue; // for all other fields
}

Hope that helps, let me know any questions.