Show related record count in Admin List View
4 posts by 3 authors in: Forums > CMS Builder
Last Post: October 20, 2014 (RSS)
By kitsguru - October 16, 2014
I am looking to add the related record count when a table has related records:
TableA has related records in TableB
When in CMS Admin List View for TableA I want to show the related record count for TableB similar to the Section Editor view which shows total record count for a table.
Ideally I would like to add something to the ListPage Fields in the section editor that would show the count.
__relatedRecords001__(count)
This would retrieve the name of TableB from the INI file with the link information
'__relatedRecords001__' => array(
'order' => '1366215778',
'label' => 'Links',
'type' => 'relatedRecords',
'relatedTable' => 'links',
'relatedLimit' => '25',
'relatedView' => '',
'relatedModify' => '',
'relatedErase' => '',
'relatedCreate' => '',
'relatedWhere' => 'category=\'<?php echo mysql_escape(@$RECORD[\'num\']) ?>\'',
'relatedMoreLink' => 'category_match=<?php echo htmlspecialchars(@$RECORD[\'num\']) ?>',
),
I can write the plugin but haven't figured the proper hooks to use. Any suggestions are welcome.
OR maybe this should be a feature request for a future version
By gregThomas - October 16, 2014 - edited: October 16, 2014
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
PHP Programmer - interactivetools.com
By kitsguru - October 18, 2014
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.
By Dave - October 20, 2014
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.
interactivetools.com