Report Constructor Plugin

Overview

The Report Constructor Plugin provides a reusable, database-agnostic mechanism to build and execute dynamic SQL reports. It automatically resolves JOIN paths between tables and generates reports based on developer-defined providers.

Features

  • Dynamic Query Building: Generates SQL queries based on selected axes, formulas, and filters
  • Automatic JOIN Resolution: Uses graph traversal to find optimal JOIN paths between tables
  • Multi-Database Support: Works with PostgreSQL, MySQL, and MSSQL
  • Provider-Based Architecture: Developers define reports through provider classes
  • Type-Safe Filters: Built-in support for select, multiple, datetime_range, and autocomplete filters
  • Totals Calculation:
  • Column totals (sum of all rows for each formula)
  • Row totals (sum of the SUM and COUNT formulas for each row) - calculated at SQL level
  • Grand total row with database-specific optimizations (GROUPING SETS, ROLLUP)
  • CSV Export: CSV export with the totals. The exporter writes the rows directly to the output.
  • Policy Constraints: Flexible access control at the provider level
  • Pivot Reports (Date-as-Columns):
  • Render dates on the X-axis as columns (pivot table)
  • Support multiple Y-axes (hierarchical rows)
  • Require exactly one formula and a minimum of one Y-axis for pivot reports
  • Fill missing dates in the selected range with zeros in both Web and CSV exports

Requirements

  • The Festi Theme package. The plugin extends Festi\Theme\Plugin\DisplayPlugin and uses Festi\Theme\Stage\StageBuilder.
  • The Modules plugin. The plugin class implements plugin\Modules\domain\scope\ISystemSpaceModule.
  • A PostgreSQL, MySQL or MSSQL database.
  • The tables modules and users_types in the project database. The install dump adds rows that use them.
  • jQuery and the select2 library on the page, for the autocomplete filter.

The plugin has no init.php. The class domain/menu/ReportConstructorMenuItemsFetcher.php extends libs\menu\AbstractMenuItemsFetcher. That base class is not in this repository. The project must supply it before it uses the menu class.

Installation

Install the plugin with the Festi CLI. Run the command from the root of the project repository:

./vendor/bin/festi-plugin --mode install --path ./src/dashboard/ --dump ./dumps/ \
    --plugin ReportConstructor \
    --repository [email protected]:FestiPlugins/php_festi_plugin_reportconstructor.git

The CLI example was not run for this page.

The command does these steps:

  1. It adds the repository to the plugins directory as a git submodule.
  2. It copies install/install.<database type>.sql into the dumps directory as a new migration.
  3. It applies the migrations that the database does not have.

Commit the submodule and the new migration together.

The install dump does these steps:

  • It creates the festi_tables_relations table and its indexes.
  • It adds the plugin to the festi_plugins table and to the modules table.
  • It adds the URL rules of the three report methods to the backend area.
  • It adds the permission section report_constructor_view and gives access to the user type admin.

The install dump does not contain table relations. Add the relations of your project (see Configuration).

Configuration

Table relations

The plugin reads the relations between tables from the festi_tables_relations table. Add the relations of your project in a migration.

Create an empty migration file:

./vendor/bin/festi-make-migration --dump ./dumps/

Put the relations into the new file. The example uses the PostgreSQL quotes for the column table:

-- Example: Add relations for your tables
INSERT INTO festi_tables_relations ("table", target_table, on_clause, type) VALUES
('orders', 'customers', 'orders.id_customer = customers.id', 'inner'),
('orders', 'products', 'orders.id_product = products.id', 'inner'),
('products', 'categories', 'products.id_category = categories.id', 'left');

Apply the migration:

./vendor/bin/festi-migrate --path ./src/dashboard/ --dump ./dumps/

See Database Relations for the columns.

Permissions

See Permissions.

Usage

  1. Write a provider class (see Creating a Report Provider).
  2. Register the provider in your plugin (see Registering a Provider).
  3. Open the report page (see URL Access).
  4. Select the X axis, the Y axes, the formulas and the filters.
  5. Select Generate to show the report, or Export to download the CSV file.

The report page also reads the query parameters x_axis, y_axes[], formulas[] and filters[...]. It uses them as the initial values of the form.

Creating a Report Provider

Dynamic Base Table Selection

The Report Constructor supports dynamic base table selection based on the selected formula. This allows different formulas to use different starting tables for their queries.

How it works: 1. When a report is generated, ReportService calls setSelectedFormulas() on the provider 2. The provider's getBaseTable() method can then access $this->_selectedFormulas 3. The default implementation uses formula->getBaseTable() which falls back to formula->getTable() 4. Providers can override getBaseTable() to provide custom logic or fallbacks

Benefits: - Different formulas can start from different tables - No need to hardcode base table mappings - Cleaner separation: table for JOINs, baseTable for query starting point - Automatic fallback when baseTable is not specified

Example: 

// Formula uses 'attendance_types' for JOINs but 'schools_attendance' as base
new Formula(
    'avg_attendance',
    'ROUND(COUNT(CASE WHEN attendance_types.ident = \'present\' THEN 1 END) / NULLIF(COUNT(*), 0) * 100, 2)',
    __('Average Attendance (%)'),
    Formula::AGGREGATION_AVG,
    IReportRenderer::FORMAT_NUMBER,
    'attendance_types',      // table: for JOIN resolution
    'schools_attendance'     // baseTable: starting point (different from table)
)

// Most formulas: table and baseTable are the same
new Formula(
    'total_orders',
    'COUNT(orders.id)',
    __('Total Orders'),
    Formula::AGGREGATION_COUNT,
    IReportRenderer::FORMAT_INTEGER,
    'orders'  // Both table and baseTable default to 'orders'
)

Basic Provider Structure

<?php

namespace plugin\YourPlugin\reports;

use plugin\ReportConstructor\domain\provider\ReportConstructorProvider;
use plugin\ReportConstructor\domain\model\Axes;
use plugin\ReportConstructor\domain\model\Formula;
use plugin\ReportConstructor\domain\model\FilterDef;
use plugin\ReportConstructor\domain\render\IReportRenderer;

class YourReportProvider extends ReportConstructorProvider
{
    /**
     * Base table for the report
     * Default implementation uses the base table from the first selected formula
     * Override this method to provide a custom base table or fallback
     */
    public function getBaseTable(): string
    {
        // Try to get base table from selected formula (parent implementation)
        if (!empty($this->_selectedFormulas)) {
            $firstFormula = $this->_selectedFormulas[0];
            $baseTable = $firstFormula->getBaseTable();

            if ($baseTable !== null) {
                return $baseTable;
            }
        }

        // Fallback to default base table
        return 'your_base_table';
    }

    public function getXAxis(): array
    {
        return [
            new Axes('axis_id', 'table.column', 'table', 'Label'),
        ];
    }

    public function getFormulas(): array
    {
        return [
            new Formula(
                'formula_id',
                'COUNT(*)',
                'Total Count',
                Formula::AGGREGATION_COUNT,
                IReportRenderer::FORMAT_INTEGER,
                'your_table'  // table parameter
            ),
        ];
    }

    public function getFilters(): array
    {
        return [];
    }

    public function getPolicyConstraints(): array
    {
        // Implement your own access control logic
        // Examples:
        // - Filter by current user: 'table.id_user' => $this->getUserID()
        // - Filter by organization: 'table.id_org' => $this->getSessionValue('id_org')
        // - No restrictions: return []
        //
        // You can also use the selected formula's base table for dynamic constraints.
        // getSchoolID() is a method of your provider. The base class does not have it.
        // if (!empty($this->_selectedFormulas)) {
        //     $baseTable = $this->_selectedFormulas[0]->getBaseTable();
        //     return [$baseTable . '.id_school' => $this->getSchoolID()];
        // }

        return [];
    }
}

Pivot Provider

To render a pivot (dates-as-columns) report, create a provider that returns true from isPivot() and define your X-axis as a date field:

use plugin\ReportConstructor\domain\render\IReportRenderer;

class OrdersPivotProvider extends ReportConstructorProvider
{
    public function isPivot(): bool
    {
        return true; // enable pivot mode
    }

    public function getXAxis(): array
    {
        return [new Axes('date', 'DATE(orders.created_at)', 'orders', __('Date'), null, IReportRenderer::FORMAT_DATE, 'd/m/Y')];
    }

    public function getYAxis(): array
    {
        return [
            new Axes('customer', 'customers.name', 'customers', __('Customer')),
        ];
    }

    public function getFormulas(): array
    {
        return [
            new Formula(
                'count',
                'COUNT(orders.id)',
                __('Orders'),
                Formula::AGGREGATION_COUNT,
                IReportRenderer::FORMAT_INTEGER,
                'orders'  // Base table will default to 'orders'
            )
        ];
    }

    public function getFilters(): array
    {
        return [new FilterDef('date_range', FilterDef::TYPE_DATETIME_RANGE, __('Date Range'), 'orders.created_at')];
    }

    public function getPolicyConstraints(): array
    {
        return []; // no restrictions
    }
}

Axes Definition

Axes define the dimensions for grouping data:

new Axes(
    'student',                    // Unique identifier
    'users.full_name',           // SQL expression
    'users',                     // Table name (for JOIN resolution)
    __('Student'),               // Human-readable label
    'users.full_name ASC',       // Optional sort expression
    IReportRenderer::FORMAT_RAW, // Optional display format (number, integer, percentage, date, datetime, raw)
    null                         // Optional custom pattern (e.g. 'd/m/Y' for dates)
)

For date-based axes you can override the default pattern:

new Axes(
    'month',
    'DATE_TRUNC(\'month\', orders.created_at)',
    'orders',
    __('Month'),
    null,
    IReportRenderer::FORMAT_DATE,
    'M Y' // renders like "Jan 2025"
);

Formula Definition

Formulas define aggregation metrics:

new Formula(
    'total_count',                        // Unique identifier
    'COUNT(*)',                           // SQL expression
    __('Total'),                          // Label
    Formula::AGGREGATION_COUNT,           // Aggregation type
    IReportRenderer::FORMAT_INTEGER,      // Display format
    'table_name',                         // Optional: Primary table reference (for JOIN resolution)
    'base_table_name'                     // Optional: Base table when this formula is selected
                                          //            (defaults to table if not specified)
)

Parameter Order: 1. ident - Unique identifier 2. expression - SQL expression 3. label - Human-readable label 4. aggregationType - Aggregation type (defaults to AGGREGATION_COUNT) 5. format - Display format (defaults to FORMAT_NUMBER) 6. table - Primary table reference (optional, for JOIN resolution) 7. baseTable - Base table when formula is selected (optional, defaults to table)

Supported aggregation types: - Formula::AGGREGATION_SUM - Formula::AGGREGATION_AVG - Formula::AGGREGATION_COUNT - Formula::AGGREGATION_MIN - Formula::AGGREGATION_MAX

Dynamic Base Table Selection:

When a formula is selected, the provider's getBaseTable() method automatically uses the formula's base table. This allows different formulas to use different starting tables:

// Example: Formula uses 'attendance_types' for JOINs but 'schools_attendance' as base table
new Formula(
    'avg_attendance',
    'ROUND(COUNT(CASE WHEN attendance_types.ident = \'present\' THEN 1 END) / NULLIF(COUNT(*), 0) * 100, 2)',
    __('Average Attendance (%)'),
    Formula::AGGREGATION_AVG,
    IReportRenderer::FORMAT_NUMBER,
    'attendance_types',      // table: used for JOIN resolution
    'schools_attendance'     // baseTable: starting point for query
)

// Example: Most formulas use the same table for both
new Formula(
    'total_orders',
    'COUNT(orders.id)',
    __('Total Orders'),
    Formula::AGGREGATION_COUNT,
    IReportRenderer::FORMAT_INTEGER,
    'orders'  // Both table and baseTable default to 'orders'
)

Note on NULL values: If your data contains NULL values, handle them in the formula expression: 

// Example: Use COALESCE for default values
new Formula(
    'avg_grade',
    'AVG(COALESCE(assignments_results.grade, 0))',
    __('Average Grade'),
    Formula::AGGREGATION_AVG,
    IReportRenderer::FORMAT_NUMBER,
    'assignments_results'
)

// Example: Filter out NULLs in aggregation
new Formula(
    'avg_grade',
    'AVG(CASE WHEN assignments_results.grade IS NOT NULL THEN CAST(assignments_results.grade AS DECIMAL) END)',
    __('Average Grade'),
    Formula::AGGREGATION_AVG,
    IReportRenderer::FORMAT_NUMBER,
    'assignments_results'
)

Filter Definition

Filters allow users to narrow down report data:

Select Filter

new FilterDef(
    'status',
    FilterDef::TYPE_SELECT,
    __('Status'),
    'orders.status',
    [
        'values' => $statuses,  // From database query
        'value_key' => 'id',
        'value_label' => 'name',
    ],
    false  // required
)

Multiple Select Filter

new FilterDef(
    'categories',
    FilterDef::TYPE_MULTIPLE,
    __('Categories'),
    'products.id_category',
    [
        'values' => $categories,  // From database query
        'value_key' => 'id',
        'value_label' => 'name',
    ]
)

Datetime Range Filter

new FilterDef(
    'date_range',
    FilterDef::TYPE_DATETIME_RANGE,
    __('Date Range'),
    'orders.created_at',
    [],
    true  // required
)

Autocomplete Filter

The autocomplete filter provides a searchable dropdown with AJAX loading. It supports multiple selections and loads the first 10 records by default when opened.

new FilterDef(
    'student',
    FilterDef::TYPE_AUTOCOMPLETE,
    __('Students'),
    'schools_study_groups2students.id_student',
    [
        'autocomplete_url' => '/company/123/users/autocomplete/students/',
        'limit' => 20,                    // Optional: Max results per request (default: 10)
        'term_key' => 'term',             // Optional: Query parameter name for search term (default: 'term')
        'value_key' => 'value',           // Optional: Query parameter name for value lookup (default: 'value')
        'allow_empty' => false            // Optional: Add an empty item to the result list (default: false)
    ],
    false  // required
)

Options: - autocomplete_url (required): The AJAX endpoint URL for loading data. The endpoint should accept: - term parameter: Search term for filtering results - value parameter: Comma-separated IDs for loading existing selections - limit parameter: Maximum number of results to return - Response format: { results: [{ key: 'Display Name', value: 123 }, ...] } - limit: Maximum number of results per request (default: 10) - term_key: Query parameter name for search term (default: 'term') - value_key: Query parameter name for value lookup (default: 'value') - allow_empty: When true, the filter adds an empty item (...) to the top of the result list (default: false)

The filter template throws a SystemException when autocomplete_url is empty. A filter that is not required shows a control to clear the selection.

Backend Endpoint Requirements: The autocomplete endpoint should: 1. Accept term parameter for search filtering 2. Accept value parameter (comma-separated IDs) for loading existing selections 3. Accept limit parameter for result limiting 4. Return JSON in format: { results: [{ key: 'Display Name', value: 123 }, ...] } 5. When term is empty, return the first N records (default: 10) 6. When value is provided, return matching records by ID

Example Backend Endpoint: 

/**
 * @urlRule ~^/company/([0-9]+)/users/autocomplete/students/$~
 */
public function onJsonAutocompleteStudents(Response $response, int $idSchool): bool
{
    $response->setType(Response::JSON);

        $term = $_GET['term'] ?? null;
        $value = $_GET['value'] ?? null;
        $limit = (int)($_GET['limit'] ?? \plugin\ReportConstructor\domain\model\FilterDef::DEFAULT_AUTOCOMPLETE_LIMIT);

    $search = [];

    if (!empty($value)) {
        // Handle comma-separated values for multiple selections
        $valueIds = array_filter(array_map('intval', explode(',', $value)));
        if (count($valueIds) === 1) {
            $search['users.id'] = $valueIds[0];
        } else if (count($valueIds) > 1) {
            $search['users.id&IN'] = $valueIds;
        }
    } else if (!empty($term)) {
        // Search by term
        $search['users.full_name&ILIKE'] = "%{$term}%";
    }
    // If both empty, return first N records

    $list = $this->getStudentsBySchool($idSchool, $search, $limit);

    $results = array_map(
        static fn($student) => [
            'value' => $student->getID(),
            'key'   => $student->getFullName(),
        ],
        $list
    );

    $response->results = $results;
    return true;
}

Filter Behavior: - Supports multiple selections (values stored as comma-separated IDs) - Uses &IN operator in WHERE clauses with array values - Automatically converts comma-separated strings to arrays - Removes spaces around each value and removes empty values. It does not convert the values to integers.

Registering a Provider

In your plugin's onInit() method:

public function onInit(): void
{
    parent::onInit();

    $reportConstructor = $this->core->getPluginInstance('ReportConstructor');

    $reportConstructor->registerProvider(
        'orders-report',
        new OrdersReportProvider()
    );
}

Row Totals

The plugin automatically calculates row totals at the SQL level. Each row includes a row_total column that sums the values of the SUM and COUNT formulas.

The SQL below is an example of the query that the plugin generates. You do not run it by hand:

-- Example generated SQL with row total (simplified):
SELECT
    users.full_name AS axis_student,
    COUNT(*) AS formula_total_assignments,
    SUM(grade) AS formula_total_grade,
    AVG(grade) AS formula_avg_grade,
    (COALESCE(COUNT(*), 0) + COALESCE(SUM(grade), 0)) AS row_total
FROM assignments_results
...

How it works: - Only formulas with the aggregation type SUM or COUNT are in the row total. Other types (AVG, MIN, MAX) are not. - Each formula expression in the row total is wrapped in COALESCE(..., 0) to handle NULLs - Values are summed: formula1 + formula2 + formula3 + ... - When no selected formula has the type SUM or COUNT, the row total is 0 - Row total appears as the last column in both HTML and CSV output - Column total row also includes a row total - Grand total row includes the grand total row total

Visual Display: - The table shows the column with the header Row Total. - static/css/report-constructor.css sets the colors of the column.

Permissions

The install dump adds the permission section report_constructor_view. It connects the three report methods to this section in the festi_section_actions table. By default, only the user type admin has access.

To give access to other user types, add a migration to your project.

Create an empty migration file:

./vendor/bin/festi-make-migration --dump ./dumps/

Put this SQL into the new file. Replace the user type identifiers with the identifiers of your project. The example is for PostgreSQL:

INSERT INTO "festi_sections_user_types_permission" ("id_section", "id_user_type", "value")
SELECT
    (SELECT "id" FROM "festi_sections" WHERE "ident"='report_constructor_view'),
    "users_types"."id",
    '6'
FROM "users_types"
WHERE "ident" IN ('head_teacher', 'director');

Apply the migration:

./vendor/bin/festi-migrate --path ./src/dashboard/ --dump ./dumps/

The class ReportConstructorSections has the constant VIEW:

use plugin\ReportConstructor\ReportConstructorSections;

if ($this->hasUserPermissionToSection(ReportConstructorSections::VIEW)) {
    // User has access
}

Note: The value of ReportConstructorSections::VIEW is common_report_constructor. The install dump adds the section report_constructor_view, not common_report_constructor. Thus a check with the constant uses a section that the install dump does not add. The class ReportConstructorMenuItemsFetcher makes this check for the menu item.

URL Access

Once registered, reports are accessible via:

  • Display: /reports-constructor/{providerIdent}/
  • Generate: /reports-constructor/{providerIdent}/generate/ (AJAX)
  • Export CSV: /reports-constructor/{providerIdent}/export/

The URL rules accept only lowercase letters, digits and the hyphen in {providerIdent} ([a-z0-9-]+). Use only these characters in the identifier that you give to registerProvider(). The URL rules are in the backend area.

Rendering API

Both ReportRenderer (standard table) and PivotRenderer (pivot table) implement a common interface:

use plugin\ReportConstructor\domain\render\IReportRenderer;

interface IReportRenderer
{
    public function render(ReportData $reportData, ITemplate $template, array $filters = []): string;
}
  • For non-pivot reports, filters can be omitted.
  • For pivot reports, pass the validated filters so the renderer can generate the full date range.

Frontend / UX Notes

  • The pivot table view supports horizontal scrolling and sticky Y-axes and the Total column.
  • The last sticky column (Row Total) uses an orange inset shadow and remains visible on scroll.
  • Validation messages are shown inline near inputs; buttons are right-aligned.
  • On large screens the configuration (X, Y, Formulas) is shown horizontally.
  • The date range filter has no built-in default. Set the default in the last parameter of FilterDef, as an array with the keys from and to.

Example Usage

See /examples/GenericReportProvider.php for a complete generic example.

The other files in /examples/ are specific to an education platform project:

  • StudentAssignmentsReportProvider.php
  • AttendanceReportProvider.php
  • TotalEnrolmentsReportProvider.php
  • DatePivotAttendanceProvider.php (pivot report)

Database Relations

The plugin uses the festi_tables_relations table to automatically resolve JOIN paths. Each relation defines:

  • table: Source table
  • target_table: Destination table
  • on_clause: JOIN condition
  • type: JOIN type (inner, left, right, full)

The combination of table, target_table and type is unique. The plugin uses each relation in the two directions. The query builder writes the type in uppercase before the word JOIN, for example LEFT JOIN. The table also accepts the type join. Do not use it: the result is JOIN JOIN, which is not correct SQL.

The system uses BFS (Breadth-First Search) to find the shortest path between tables.

Events

The plugin dispatches events that allow you to hook into the report generation process:

AfterLoadValuesEvent

Dispatched after report data is loaded from the database but before it's returned. This allows you to modify the report data (e.g., transform values, add computed columns, filter rows).

Event Type: reports_constructor_after_load_values_event

When: Dispatched in ReportService::generateReport() after executing the SQL query and creating the ReportData object.

Event Object: 

use plugin\ReportConstructor\domain\event\AfterLoadValuesEvent;
use plugin\ReportConstructor\domain\model\ReportData;

class AfterLoadValuesEvent extends FestiEvent
{
    public const TYPE = 'reports_constructor_after_load_values_event';

    public function &getReportData(): ReportData; // Returns reference for modification
}

Example Usage: 

use Core;
use plugin\ReportConstructor\domain\event\AfterLoadValuesEvent;

// In your plugin's onInit() method
public function onInit(): void
{
    parent::onInit();

    Core::getInstance()->addEventListener(
        AfterLoadValuesEvent::TYPE,
        [$this, 'onAfterLoadValues']
    );
}

public function onAfterLoadValues(AfterLoadValuesEvent $event): void
{
    $reportData = &$event->getReportData();
    $rows = &$reportData->getRows(); // a reference to the rows of the report

    // Modify rows - e.g., add computed values, transform data
    foreach ($rows as &$row) {
        // Example: Add a computed percentage column
        if (isset($row['total']) && isset($row['completed'])) {
            $row['completion_rate'] = $row['total'] > 0 
                ? ($row['completed'] / $row['total']) * 100 
                : 0;
        }
    }
    unset($row);

    // Or filter out certain rows. The assignment writes through the reference.
    $rows = array_values(array_filter($rows, function($row) {
        return ($row['status'] ?? '') !== 'archived';
    }));
}

This PHP example was not run for this page.

Note: Change the rows through the reference that getRows() returns. A new ReportData instance has no effect: ReportService returns its own object and does not read the event again.

BeforeDisplayReportEvent

The plugin dispatches this event in onDisplayDefault() before it adds the report block to the page. Use it to add your own blocks to the page of a report.

Event Type: before_display_report_constructor (BeforeDisplayReportEvent::EVENT_TYPE)

Method Returns
getStageBuilder() The Festi\Theme\Stage\StageBuilder of the page
getProvider() The provider of the report
getProviderIdent() The identifier of the provider 
use plugin\ReportConstructor\domain\event\BeforeDisplayReportEvent;

Core::getInstance()->addEventListener(
    BeforeDisplayReportEvent::EVENT_TYPE,
    function (BeforeDisplayReportEvent $event) {
        if ($event->getProviderIdent() !== 'orders-report') {
            return;
        }

        $event->getStageBuilder()->addBlock('<p>Orders for each customer.</p>');
    }
);

This example was not run for this page.

PrepareReportConstructorMenuItemEvent

The class ReportConstructorMenuItemsFetcher dispatches this event before it adds the menu item report-constructor. getMenuItem() returns the menu item (Festi\Theme\UI\IMenuItem) as a reference.

Event Type: prepare_report_constructor_menu_item (PrepareReportConstructorMenuItemEvent::EVENT_TYPE)

The plugin does not register ReportConstructorMenuItemsFetcher. The project registers it.

Internals / Implementation Notes

  • Query building uses QueryBuilderFactory with database-specific implementations to produce totals (ROLLUP/GROUPING SETS), and a common assembly flow.
  • JOIN path resolution is performed by JoinResolver using relations stored in festi_tables_relations.
  • CSV export mirrors Web totals layout (column totals + grand total) and, for pivot reports, includes all dates in the filter range even when no data exists.
  • Dynamic Base Table Selection: The ReportService calls setSelectedFormulas() on the provider before calling getBaseTable(). This allows providers to dynamically select the base table based on which formula was selected. The default implementation in ReportConstructorProvider uses formula->getBaseTable() which falls back to formula->getTable() if no explicit base table is set.
  • Event System: The AfterLoadValuesEvent is dispatched after data is loaded from the database, allowing plugins to modify report data before rendering.

Error Handling

The plugin throws a SystemException with one of these messages:

  • Provider not found: "Report provider orders-report not found"
  • Missing relation: "Missing relation: Cannot find JOIN path from 'users' to 'orders'. Please add the required relation to festi_tables_relations table."
  • Invalid axis: "Axis 'invalid_axis' not found"
  • Invalid formula: "Formula 'invalid_formula' not found"
  • Missing filters: "Filter 'Study Group' is required" (the message contains the label of the filter)
  • Invalid filter value: "Invalid value for filter 'Status'"
  • Pivot report: "Reports require exactly one formula to be selected"
  • Pivot report: "Reports require at least one Y-axis to be selected"
  • Database type: "Unsupported database driver: sqlite"

Policy constraints do not cause an error. They add conditions to the WHERE clause.

Database-Specific Features

PostgreSQL

Uses GROUPING SETS for optimal totals calculation

MySQL

Uses WITH ROLLUP for totals

MSSQL

Uses ROLLUP with GROUPING() for totals

Performance Considerations

  • JOIN Path Caching: A JoinResolver object keeps the paths that it found. The plugin makes a new object for each report.
  • CSV Export: The exporter writes the CSV rows directly to the output. The query result is fully in memory before the export starts.
  • Index Recommendations: Ensure proper indexes on:
  • JOIN columns referenced in on_clause
  • Filter columns
  • GROUP BY columns

Security

  • Policy Constraints: Providers define access control via getPolicyConstraints()
  • Return empty array [] for no restrictions
  • Return field/value pairs to filter data: ['orders.id_user' => $userId]
  • Use session data: ['orders.id_org' => $this->getSessionValue('id_org')]
  • SQL Injection Prevention: The user selects axes and formulas by identifier. The SQL expressions come from the provider class. The database layer of the framework makes the WHERE conditions from the filter values (getSqlCondition()).
  • Filter Validation: Type-specific validation for all filter values
  • Permission System: Uses report_constructor_view section for access control

The provider writes the SQL expressions of axes and formulas. The plugin puts them into the query unchanged. Do not put user input into these expressions.

Troubleshooting

"Missing relation" Error

Add the required relation to festi_tables_relations in a new migration of your project.

Create the migration file:

./vendor/bin/festi-make-migration --dump ./dumps/

Put the relation into the new file:

INSERT INTO festi_tables_relations ("table", target_table, on_clause, type)
VALUES ('source_table', 'target_table', 'source_table.id = target_table.id_source', 'inner');

Apply the migration:

./vendor/bin/festi-migrate --path ./src/dashboard/ --dump ./dumps/

Empty Results

Check: 1. Policy constraints in getPolicyConstraints() - they might be too restrictive 2. Filter values - ensure they match data in database 3. Data exists in base table 4. JOIN conditions in festi_tables_relations are correct

Performance Issues

  1. Add indexes on JOIN columns
  2. Reduce number of rows with filters
  3. Limit selected formulas

License

Part of the Lummioo education platform.