Wunderbyte Table can be used instead of the table_sql class and then supports all the actions via Ajax.
This local plugin was developed to allow the use of the table_sql class within modals and tabs. There is no special configuration required, BUT with special configuration, your table will be much more powerful. After the installation of the local_wunderbyte_table, you can create an extended class to wunderbyte_table (instead of table_sql).
class booking_table extends wunderbyte_table {}
If your extended table class contains another class (eg myplugin_class), make sure you can instantiate it with only the cmid. Errors will be thrown if this is not possible.
From 1.1.1 on, wunderbyte_table is loaded only once the corresponding div and none of it's parent is hidden (display:none) and it will add a visbility listener on the next hidden parent element. Unhiding will trigger loading of the table.
The included demo.php is only meant to demonstrate the working of the table.
That's all it takes. Switching pages, sorting, hiding columns and downloading will now run via ajax.
Wunderbyte Table uses webservices to support features like ajax reload, infinite scroll, action buttons etc. As it is a generic project it can't know about the security requirements for the projects it actually is used in. We use Wunderbyte Table in combination with shortcodes, where we can't know where in Moodle the table actually will show up and sometimes, this will be on a location without any login requirements. Therefore, the key requirelogin, which is set normally to "true" can also be set to false:
$table->requirelogin = false;
We also provide the capability 'local/wunderbyte_table:canaccess' which normally is set to 'allow' for the archetype 'user'. This capability check is only required when requirelogin is set to true. Changing this will result in a change for all wunderbyte table on the moodle instance.
If only a single table should be protected, you can use the requirecapability key on a specific table to require special capabilities. When this key is not set, it falls back to 'local/wunderbyte_table:canaccess'.
$table->requirecapability = 'local/myplugin:canaccess';
Methods that should be called via the actionbutton functionality (webservice executeaction) must include the action prefix ie "action_deleterow". While the transmitted methodname is "deleterow", the actual method which will be called via the webservice is action_deleterow. This prevents any other method to be called than a method defined for this purpose.
Wunderbyte Table comes with a responsive table template, but it can and should be overriden in your project. To do so, set in your instance of your extended class (eg $yourtable) the template to your proper template, like
$yourtable->tabletemplate = 'mod_yourtemplate/yourtable'
where yourtable corresponds to yourtable.mustache in your own project.
With Version 1.2.1 Wunderbyte Table supports multiple templates in one project. The structure has to be like this:
One new feature is caching. Wunderbyte_table will automatically pass every sql call to the MODE_APPLICATION cache with the key being the hashed request.
A request for page one will be cached with a different key than a request for page 2 etc.
Invalidation of the cache is being done by
cache_helper::purge_by_event('changesinwunderbytetable');
If you don't run this every time your table changes, you won't see the changes in your DB reflected in the output of wunderbyte table, unless you otherwise purge the cache.
If you use more than one table in your plugin or if there is a possibility that more than one Plugin uses local_wunderbyte_table on your system, you should provide your own cache definitons in your plugin. Use the define_cache('mod_myplugin', 'mycachename') function to set your own caches.
This description is only relevant in one case: If you override a value from your table via the col_mycolumn function in the wunderbyte_table class and you use a mustache template and renderer to echo html (eg. to render a button or a modal etc.) AND if this mustache template includes javascript, then you will encounter the problem, that the JS won't be automatically included. The reason is that the js would be added to the page footer via the renderer, but it is simply skipped in this particular usecase. Therefore, you need to add the JS instead of your column template to the table template. Any JS which is on this labe (corresponding to table.mustache in the wunderbyte_table project), will be executed after the table is correctly rendered. You have to make sure to write your js in a way that your can find the necessary variables (eg. the ids of your rows) without being able to pass them directly via the mustache template.
If you want to enable scrolling within your table, you can set the stickyheader property to true. With infinite scroll, your table will automatically reload additional rows once you scrolled to its bottom. To enable infinite scroll, you can define the number of rows that will be loaded - first on init and later with each reload - in the infinitescroll variable. Sticky Header and Infinite Scroll can be combined. If you don't use infinite scroll, pagination options will be displayed at the bottom of your table.
You can add a number of action buttons to your table. If you combine them with "$yourtable->addcheckbox = true", you will be able to select single lines and execute your function with it. The methods will need to be implemented in your child class of wunderbyte table and they will be called via ajax. Example:
$mytable->addcheckbox = true;
$mytable->actionbuttons[] = [
'label' => get_string('deleterow', 'mod_myproject'), // Name of your action button.
'methodname' => 'deleterow', // The method needs to be added to your child of wunderbyte_table class including "action_" prefix ie action_deleterow.
'data' => [ // Will be added eg as data-id = $values->id, so values can be transmitted to the method above.
'id' => 'id'
]
];
You can choose if your Action Button needs element(s) to be selected in order to trigger an action. If this is set to false, you can trigger actions with or without actions selected.
$mytable->actionbuttons[] = [
'selectionmandatory' => false,
]
You can choose if your Action Button triggers a modal with the "nomodal" param:
$mytable->actionbuttons[] = [
'nomodal' => false,
]
If your Action Button works with or without selected elements, you can choose a different bodystring for the modal it may trigger.
$mytable->actionbuttons[] = [
'data' => [
'id' => 'id',
'titlestring' => 'deletedatatitle', // Will be shown in modal title
'bodystring' => 'deletedatabody', // Will be shown in modal body in case elements are selected
'labelcolumn' => 'firstname', // The Labelcolumn is important because it will be picked for human verification in the modal.
'noselectionbodystring' => 'specialbody', // Will be displayed in modal bode in case no data is selected
'submitbuttonstring' => 'deletedatasubmit', // Modal Button String
'component' => 'local_wunderbyte_table', // Localization of strings
]
]
You can choose if your Action Button transmits a single or multiple call(s) in case multiple elements are selected.
$mytable->actionbuttons[] = [
'id' => -1, // This forces single call execution.
]
If you want your Action Button to trigger a form instead of a simple modal, hand over namespace and title.
$mytable->actionbuttons[] = [
'formname' => 'local_myplugin\\form\\edit_mytableentry', // To include a dynamic form to open and edit entry in modal.
'data' => [
'title' => get_string('title'), // Localized title to be displayed as title in dynamic form (formname). Default is "title".
'submitbuttonstring' => get_string('send'), // Localized title to be displayed in submit button in dynamic form (formname). Default is "Save changes".
]
]
You can also use actionbuttons in a column to treat the corresponding record.
public function col_action($values) { // Button will be added to action column.
$data[] = [
'label' => get_string('delete', 'core'), // Name of your action button.
'class' => 'btn btn-danger', // Use bootstrap 4.
'href' => '#', // You can either use the link, or JS, or both.
'iclass' => 'fa fa-cog', // Add an icon before the label.
'arialabel' => 'cogwheel', // Add an aria-label string to your icon.
'title' => 'Edit', // Will be displayed when hovered over icon.
'id' => $values->id.'-'.$this->uniqueid, // Access the data of the record.
'name' => $this->uniqueid.'-'.$values->id,
'methodname' => 'deleteitem', // The method needs to be added to your child of wunderbyte_table class including "action_" prefix ie action_deleterow.
'nomodal' => true,
'data' => [ // Will be added eg as data-id = $values->id, so values can be transmitted to the method above.
'id' => $values->id,
]
];
WB Table provides direct filter, search and sort functionality.
$standardfilter = new standardfilter('username', get_string('username', 'my_plugin'));
$table->add_filter($standardfilter);
$table->define_fulltextsearchcolumns(['username', 'firstname', 'lastname']);
$table->define_sortablecolumns(['id', 'username', 'firstname', 'lastname']);
In order for them to work, you must obey some rules: Filter basically just add another 'where' to the sql. The column name must therefor be like the filtercolumn. If the "WHERE columnname like '%myname%'" doesn't work, because you would need to write "WHERE s1.columnname like '%myname%'", then the filter will not work. You would need to wrap your SQL so to eliminate the need for the columnname prefix.
Filter, search and sort params selection triggers URL update. These params can also be applied to table on load via URL. These functions are not available if more than one table is displayed per page (multitable display).
As for the filter, you have these further functionalities:
Here is an example how to set this up: By the way: 'id' will aways be obmitted, as it is not a useful filter in any case.
$standardfilter = new standardfilter('sport', get_string('sport', 'my_plugin'));
$table->add_filter($standardfilter);
// This filter works, when you have a column with english weekdays.
// They will be localized to the users language.
$weekdays = new weekdays('weekday', get_string('weekday', 'my_plugin'));
$table->add_filter($weekdays);
When having eg three values in your dataset, one, two, three, and you want them to appear always in the right order in your filter, you can use $standardfilter->add_options(['one', 'two', 'three']) to achieve that result.
If you only have 0 for inactive and 1 for active, you can use
$standardfilter->add_options([
0 => get_string('inactive', 'myplugin'),
1 => get_string('active', 'myplugin'),
]);
The hierarchical filter will allow to order your results in a special way. Assume you have the following values: one, two, three, four, five, six in your database. They belong to three different categories. You can then add the filter like this:
$hierarchicalfilter = new hierarchicalfilter('x', get_string('x', 'my_plugin'));
$hierarchicalfilter->add_options([
'one' => [
'localizedname' => get_string('one', 'myplugin'),
'parent' => get_string('category A'),
],
'two' => [
'localizedname' => get_string('two', 'myplugin'),
'parent' => get_string('category B'),
],
'three' => [
'localizedname' => get_string('three', 'myplugin'),
'parent' => get_string('category A'),
],
...,
'other' => [
'localizedname' => get_string('other', 'myplugin'),
'parent' => get_string('category Other'),
]
]);
$table->add_filter($hierarchicalfilter);
The hierarchical filter will just add a (only one!) layer for the defined values. With 'other', we have implemented a catch function which will take care of all values you didn't define. In our example above, if suddenly we find seven and eight in the DB, they would be added the the "Other" Category. To rename this category, just add it with the key "other" and your own localized string.
For columns that contain date and time values (as Unix timestamp) you can enable a datepicker to allow users to filter the values:
$datepicker = new datepicker('enddate', get_string('enddate'));
// For the datepicker, we need to add special options.
$datepicker->add_options(
'standard',
'<',
get_string('apply_filter', 'local_wunderbyte_table'),
'now',
);
$table->add_filter($datepicker);
A special type of datepicker filter is the timespan filter which will take the input of two date- and timepickers and apply to two columnvalues of a record. This enables comparison of two timespans. Possible operations are 'within', 'overlapboth', 'overlapstart', 'overlapend', 'before', 'after' and 'flexoverlap'. "Overlapstart" filter will only display records with starttime before and ending within the timespan of the filter, "within" filter will display records starting after and ending before the values of the filter timespan. "Flexoverlap" will include all kinds of overlaping: overlapping the beginning, the end, both sides or within. The possibleoperations array is containing a whitelist, if none specified, all are applied.
$datepicker = new datepicker(
'startdate',
get_string('timespan', 'local_wunderbyte_table'),
'enddate'
);
// For the datepicker, we need to add special options.
$datepicker->add_options(
'in between',
'<',
get_string('apply_filter', 'local_wunderbyte_table'),
'1680130800',
'now'
);
$table->add_filter($datepicker);
By default filters are displayed next to the table and can be hidden with the filterbutton. If you want them to be hidden on load, set filteronloadinactive = true in the instance of your table.
The Hourslist filter can extract full hours from a unix timestamp and thus only show results which e.g. happened at a certain time. One example would be in mod_booking to show only courses which start at a given time (like 10:00 - 11:00).
This only works with mariadb, mysql and postgres db.
Example of a filter json for this:
$hourslistfilter = new hourlist('timemodified', get_string('hourlastmodified', 'local_wunderbyte_table'));
$table->add_filter($hourslistfilter);
The filter function also supports columns with multiple values stored as string with a separator.
You can define the separator like this:
$standardfilter = new standardfilter('mycolname', get_string('mystring', 'mod_myplugin'));
$standardfilter->add_options(
[
'explode' => ',', // In this example, a comma is the separator, you might need another one.
]
);
$table->add_filter($standardfilter);
The filter function also supports columns storing one or multiple JSON objects. You can define the attribute of the JSON object which should be used for the filter:
$standardfilter = new standardfilter('mycolname', get_string('mystring', 'mod_myplugin'));
$standardfilter->add_options(
[
'jsonattribute' => 'name', // Replace 'name' with the actual attribute name.
]
);
$table->add_filter($standardfilter);
If the output template you want to use doesn't support clickable headers to sort (eg because you use cards), you might want to use the sort select. Just add
$table->cardsort = true;
to your wunderbyte_table classs (look in the _container templates to understand how this works).
Each column defined as sortable will display carets in the table header. They have the same functionality as sortcolumn select & changesortorder element displayed in top of table.
If you want to define default sorting on table load, set:
$table->sort_default_column = 'columnname';
$table->sort_default_order = SORT_ASC; // Or SORT_DESC.
The fulltext search is triggerd when more than 3 characters are typed into the searchfield. Fulltext search is checking values from all columns defined in $table->define_fulltextsearchcolumns(["column1", "column2"]). If you want to look for values in a specific column, use columnname:searchterm. This can be applied for numerous queries and combined with regular fulltext search. Combinations are seperated via whitespace and/or comma. If you want to use values containing whitespaces, use double (or single) quotes ie. "Localized Column":"value including whitespace". Searchterms will be used like wildcards, while quoted values and numbers trigger exact search (no wildcard). If the "Enter" key is pressed, search will be toggled immediately, no matter how many characters are set.
WB Table has implemented the javascript and most of the php to support reordering out of the box. To turn this on, just use this command:
$table->sortablerows = true;
This will add a column with drag n drop handles. But you will need to add a method "action_reorderrows" to actually do the reordering to your wb table child class. For obvious reasons, this can't be done generically. You'll find a non functional template for this method in your wunderbyte_table.php in the classes folder.
If you want to display multiple tables on one page, tabs can be enabled in templates.
For the display of localized names in tableheaders, use the define_headers function.
$columns = [
'id' => get_string('id', 'local_wunderbyte_table'),
'username' => get_string('username'),
'firstname' => get_string('firstname'),
'lastname' => get_string('lastname'),
'email' => get_string('email'),
'action' => get_string('action'),
];
$table->define_headers(array_values($columns));
$table->define_columns(array_keys($columns));
To lazy load wunderbyte table (eg. for loading in tabs or modals) you need to call $table->lazyout() instead of $table->out. While out will return the html to echo, lazyout echos right away. If you want the html of lazyout, use $table->lazyouthtml();
The plugin can be also installed by putting the contents of this directory to
{your/moodle/dirroot}/local/wunderbyte_table}
Afterwards, log in to your Moodle site as an admin and go to Site administration > Notifications to complete the installation.
Alternatively, you can run
$ php admin/cli/upgrade.php
to complete the installation from the command line.
2023 Wunderbyte GmbH info@wunderbyte.at
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program. If not, see https://www.gnu.org/licenses/.