PluginList sublist

New in Tiki27, permitting to improve PluginList, and fixes/enhancements are being backported to the Tiki26 branch. Once it is more stable/robust, it may also be backported to Tiki24.

This was one of the recommendations from this large project: FOSDEM 2024

Related Merge request: PluginList SUBLIST MR

Example Usage

PluginList SUBLIST gives the ability to join several trackers and get mixed results into a single array, greatly optimizing the performance when subqueries and sub-list plugins are needed.
Steps to follow:

Create a tracker called Region with ID, Name as fields (tracker_id = 1)
Create an other tracker called Hospital with ID, Name and Region (Item Link pointing to region tracker) (tracker_id = 2)
Fill all trackers with data as well
Apply this code:

Copy to clipboard

!!! Stock table template
{LIST()}
  {filter type="trackeritem"}
  {filter field="tracker_id" exact="2"} 
  {SUBLIST(name="region")}
    {filter field="tracker_id" exact="1"} 
    {filter field="object_id" exact="$parent.tracker_field_hospitalRegion$"}
    {FORMAT(name="name")}{display name="tracker_field_regionName" default=""}{FORMAT}
  {SUBLIST}
  {OUTPUT(template="table")}
    {column field="hospital" label="Hospital" mode="raw"}
    {column field="region" label="Region" mode="raw"}
  {OUTPUT}
  {FORMAT(name="hospital")}{display name="tracker_field_hospitalName"  default="" }{FORMAT}
  {FORMAT(name="region")}{display name="region.name" default=""}{FORMAT}
{LIST}

First List Plugin joins tracker 1 (tracker Region) and tracker 2 (tracker Hospital) with an Item Link field in tracker 2 (tracker Hospital) pointing to tracker 1 (tracker Region) called tracker_field_hospitalRegion....Sublist filters the results by the ID of the record coming from the parent tracker(here tracker Region).

Note: The underlying implementation makes sure to execute only one query for all the records found in the parent list block and then distributes the results to the relevant resultset rows.

Output template is using the stock table template with wiki formatting the name field of tracker 1 (tracker Region) and then wiki formatting the table column for region name using nested keys like 'region.name'.
Region is the key name in SUBLIST block, so it adds an array of records in the parent row under the 'region' key. Then, it formats and adds the tracker 1 (tracker Region) field(s) to that key for each parent record.
The given above code in step 4, would produce (accordingly to the data filled in trackers...step 3 ):

Click to expand

Example Using Pivot table

You can use SUBLIST in pivottable as you normally would in a plugin list - define the SUBLIST and either format some of the columns there or leave all columns (skip formatting blocks in sublist). In order for the results to appear in the main pivottable array of data, you must include the fields in the display blocks in the main pivottable.
Assure that you have the two trackers as documented above, then apply this code:

Copy to clipboard

{PIVOTTABLE(data="tracker:2" cols="region.regionName" vals="hospitalName" colOrder="key_a_to_z" rowOrder="key_a_to_z" rendererName="Table" aggregatorName="List Unique Values" inclusions="{}" menuLimit="500" aggregateDetails="object_type" highlightMine="n" highlightGroup="n" chartHoverBar="y")}
{display name="tracker_field_hospitalName"}
{display name="region.tracker_field_regionName"}
{SUBLIST(name="region" required="2")}
  {filter field="tracker_id" exact="1"}
  {filter field="object_id" exact="$parent.tracker_field_hospitalRegion$"}
{SUBLIST}
{PIVOTTABLE}

This example shows how to include 2 remote tracker fields (tracker_field_hospitalName, tracker_field_regionName) in the parent result set using the keys 'tracker_field_hospitalName' and 'region.tracker_field_regionName'. These 2 will appear as fields in the PluginPivotTable and you would be able to use them as normal fields.

Note that this works for non-multiple use-cases. If you have multiple results, they will appear as an array and there might be problems handling the data in pivottable.

Also note that the required="2" parameter filters out all of the parent tracker entries that don't have at least one sublist element.
From Matrix chat:

.../...to include parent list results that has at least one sublist result, you can use required="1" parameter on the sublist clause. Also, if you have multiple visited doctors, specify multiple="1". Then, each row of the resultset will contain "visited_doctors" param as an array.

The given above code would produce:

Click to expand