Extended Fields
The extended event fields were introduced with Agenda 1.1.0.
Each event in Agenda can use predefined extended fields. The field values are stored as JSON encoded array in a text field. So sorting and filtering by extended fields is not possible.
To define the extended fields the system setting agenda.extended_event_fields
has to be filled with a nested JSON encoded array. The following value gives an
example:
[
{
"name": "price",
"label": "Price",
"allow_blank": true,
"read_only": false,
"label_style": "margin-top: 5px"
},
{
"fields": [
{
"xtype": "numberfield",
"name": "minimum",
"renderer": "agenda-combo-example-resource",
"label": "Minimum number of participants",
"width": 0.5,
"column_label": "min.",
"column_width": 50,
"hidden": false
},
{
"xtype": "numberfield",
"name": "maximum",
"renderer": "agenda-combo-example-resource",
"label": "Maximum number of participants",
"width": 0.5,
"column_label": "max.",
"column_width": 50,
"hidden": false
}
]
}
]
It will add the fields price, and the fields minimum and maximum in two columns to the event create/update window.
Each array will create one extended field. The name key is required. The
label key will be used, when it exists. Otherwise, a lexicon entry with the
field name prefixed by agenda.extended. is searched in the lexicon. An xtype
key will be used, when it exists. Otherwise, the xtype defaults to textfield.
A renderer key will be used, when it exists. The renderer can be defined in
the file referenced in the agenda.extended_xtypes_script system setting. If
you want to test the above example, you must remove the lines with the renderer
key, otherwise the Agenda custom manager page will be empty due to JavaScript
errors. The field can have some field options. allow_blank, read_only and
label_style are currently avaliable
If a nested fields key contains an array value, this value will be used for
subfields in columns. The width key inside is then used for the subfield
column width.
The extended fields can also generate columns in the events grid. The
column_label key is required to add a column in the grid. The column_width
key will define the width of the grid column. The column_width defaults to 100.
The column can be hidden in the grid with the hidden key.
Extended fields are available as placeholder with the prefix extended. in the
event row template. The price field from the example will be available with
the [[+extended.price]] placeholder.
The setting can use @FILE or @CHUNK bindings. Path
placeholders like {core_path}, {base_path} and {assets_path} can be used.
All paths have to stay inside the MODX base path because of security reasons.
Repeat fields
The extended repeat fields were introduced with Agenda 1.4.0.
The fields are defined in the system setting cursus.extended_repeat_fields.
They work the same as the extended event fields, can use @FILE or @CHUNK
bindings and are shown in the edit repeat window and the repeats grid.
Location fields
The extended location fields were introduced with Agenda 1.4.0.
The fields are defined in the system setting cursus.extended_location_fields.
They work the same as the extended event fields, can use @FILE or @CHUNK
bindings and are shown in the edit location window and the locations grid.
Filtering by extended fields
If you want to filter the events, repeats or locations by extended fields, you can use the JSON_EXTRACT method of MySQL. This is quite ineffective and can be slow for larger tables. It is better to use virtual columns and add an index to those columns.
If you want to filter your event dates by the extended field foreignId, you have
to create a virtual column foreignId in the event date table and add an index for
that column:
ALTER TABLE `modx_agenda_event_date` ADD COLUMN `foreignId`
VARCHAR(10) GENERATED ALWAYS AS (`extended` ->> '$.foreignId') VIRTUAL;
CREATE INDEX `foreignId` ON `modx_agenda_event_date`(`foreignId`);
In the Agenda snippet calls you can now use a where clause with the following JSON:
&where=`{"EventDates.foreignId:=": "<text>"}`
Or use the following xPDO where clause in your own PHP code:
$c->where = [
'EventDates.foreignId:=' => '<text>'
];
If you want to add those virtual columns in a package resolver, you can use the following resolver example code:
<?php
/**
* Resolve virtual columns for JSON fields
*
* @package agenda
* @subpackage build
*
* @var array $options
* @var xPDOObject $object
*/
$success = false;
if ($object->xpdo) {
/** @var xPDO $modx */
$modx =& $object->xpdo;
if (!function_exists('addVirtualColumn')) {
function addVirtualColumn($modx, $classname, $jsonname, $fieldname, $fieldtype)
{
$tableName = $modx->getTableName($classname);
$tableName = str_replace('`', '', $tableName);
$dbname = $modx->getOption('dbname');
$c = $modx->prepare('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = :dbName AND table_name = :tableName');
$c->bindParam(':dbName', $dbname);
$c->bindParam(':tableName', $tableName);
$c->execute();
$availableColumns = $c->fetchAll(PDO::FETCH_COLUMN, 0);
$availableColumns = array_flip($availableColumns);
if (in_array($fieldname, $availableColumns)) {
$modx->exec('DROP INDEX `' . $fieldname . '` ON ' . $modx->getTableName($classname) . ';');
$modx->exec('ALTER TABLE ' . $modx->getTableName($classname) . ' DROP COLUMN `' . $fieldname . '`;');
$modx->log(xPDO::LOG_LEVEL_INFO, ' -- altered virtual column to ' . $modx->getTableName($classname) . ': ' . $fieldname);
} else {
$modx->log(xPDO::LOG_LEVEL_INFO, ' -- added virtual column to ' . $modx->getTableName($classname) . ': ' . $fieldname);
}
$modx->exec('ALTER TABLE ' . $modx->getTableName($classname) . ' ADD COLUMN `' . $fieldname . '` ' . $fieldtype . ' GENERATED ALWAYS AS (`' . $jsonname . '` ->> \'$.' . $fieldname . '\') VIRTUAL;');
if (!in_array($fieldtype, ['TEXT'])) {
$modx->exec('CREATE INDEX `' . $fieldname . '` ON ' . $modx->getTableName($classname) . '(`' . $fieldname . '`);');
}
}
}
if (!function_exists('removeVirtualColumn')) {
function removeVirtualColumn($modx, $classname, $fieldname)
{
$tableName = $modx->getTableName($classname);
$tableName = str_replace('`', '', $tableName);
$dbname = $modx->getOption('dbname');
$c = $modx->prepare('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = :dbName AND table_name = :tableName');
$c->bindParam(':dbName', $dbname);
$c->bindParam(':tableName', $tableName);
$c->execute();
$availableColumns = $c->fetchAll(PDO::FETCH_COLUMN, 0);
$availableColumns = array_flip($availableColumns);
if (in_array($fieldname, $availableColumns)) {
$modx->exec('DROP INDEX `' . $fieldname . '` ON ' . $modx->getTableName($classname) . ';');
$modx->exec('ALTER TABLE ' . $modx->getTableName($classname) . ' DROP COLUMN `' . $fieldname . '`;');
$modx->log(xPDO::LOG_LEVEL_INFO, ' -- removed virtual column from ' . $modx->getTableName($classname) . ': ' . $fieldname);
}
}
}
$corePath = $modx->getOption('agenda.core_path', null, $modx->getOption('core_path') . 'components/agenda/');
/** @var Agenda $agenda */
$agenda = $modx->getService('agenda', 'Agenda', $corePath . 'model/agenda/', array(
'core_path' => $corePath
));
switch ($options[xPDOTransport::PACKAGE_ACTION]) {
case xPDOTransport::ACTION_INSTALL:
case xPDOTransport::ACTION_UPGRADE:
addVirtualColumn($modx, 'AgendaEvents', 'extended', 'teaser', $fieldtype = 'TEXT');
addVirtualColumn($modx, 'AgendaEventDates', 'extended', 'foreignId', $fieldtype = 'VARCHAR(10)');
addVirtualColumn($modx, 'AgendaEventDates', 'extended', 'highlight', $fieldtype = 'VARCHAR(5)');
addVirtualColumn($modx, 'AgendaEventDates', 'extended', 'community', $fieldtype = 'VARCHAR(5)');
$success = true;
break;
case xPDOTransport::ACTION_UNINSTALL:
removeVirtualColumn($modx, 'AgendaEvents', 'teaser');
removeVirtualColumn($modx, 'AgendaEventDates', 'foreignId');
removeVirtualColumn($modx, 'AgendaEventDates', 'highlight');
removeVirtualColumn($modx, 'AgendaEventDates', 'community');
$success = true;
break;
}
}
return $success;