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 Greg,

Thanks for the suggestion. Storing the data is a good idea, however your solution would require updating the plugin for every instance of showing the count for related records.

I want the plugin to automatically detect the related records and show the count accordingly. TableA parent of TableB, TableC parent of TableD etc. This information is available in the INI file for TableA or TableC. The plugin should be able to read this information and use it.

CMSB does not appear to take full advantage of a relational database such as MySQL using foreign keys, views, stored procedures and triggers. Although I can still do this to a certain extend but I am unsure of the impact on CMSB. Essentially CMSB is treated as a flat file system.

I have written extremely complex relational database systems for different front ends over the past 25 years. I would really like to incorporate the business logic for the database in the database itself and enforce data integrity at that level. Not all the apps I create are always accessed via a browser. In the end, it should not matter whether or not a record is added from CMSB or another front end interface using another language such as PERL or a desktop application written in C or any of its variants, Visual basic, or even a iOS or android App. The business logic does not change nor should the front end even need to deal with it.

Do you know if I can use stored procedures for CRUD (Create, Read, Update, Delete) and triggers of data integrity operations with CMSB? In this way, an attempt to delete TableA record while it has TableB records associated with it would fail, or could cascade down and delete all related records.

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.