pivot tables with dynamic field

Roger Mangraviti commented 7 days ago

Hi Team,

Long time KoolReports user, awesome product with great flexibility. New to the forums.

I have an edge case that I am trying to find an interesting solution too;

We have 3 columns in the mysql database;
directory_id, field_name, user_data

Sample Data;
1, address, 213 street name
1, city, sydney
1, telephone_number, 0288888888

Resulting Pivot Table requirement;
directory_id, address, city, telephone_number, edit_form_link
1, 213 street name, sydney, 0288888888, "link to edit form"

The field_name is a dynamic field from a set of about 62 field types (I would hope the number of field types should not matter) and the user_data is the value of the field_type.

Is it possible to get some tips or pointers for a solution for this ?

Sebastian Morales commented 6 days ago

We need to know whether there is any pattern to know when a directory_id is completed when reading data row by row, i.e no more data rows with that directory_id would appear later?

Roger Mangraviti commented 5 days ago

there is no pattern to know when a directory_id is completed, it would be the sql query that would determine the result set. Field_name would be dynamic and may have X number of a total of 62 options.

there maybe more or less field_names out of about 62 options, which some may not be set (ie no sample data exists for that field_name) so the column would be empty if other field_names have a user_data.

Sebastian Morales commented 4 days ago

For practical reasons, is it different if you sort your sql query by the directory_id field? If it's possible to sort by directory_id, we can manage to make Pivot process work with vertical data structure like yours.

Roger Mangraviti commented 4 days ago

there is no difference by sorting by directory_id, there would be no impact, that would be fine.

column ordering does not matter, just ability to pivot the table show how.

Sebastian Morales commented 4 days ago

Another question to clarify it further, could there be a case where a same directory_id has different values for a same field name, such as "directory_id" 1 has two "address" values, three "telephone" ones, etc?

Roger Mangraviti commented 4 days ago

absolutely not, there is a unique key on directory_id & field_name, only 1 field_name type per directory_id

Sebastian Morales commented 1 day ago

Great news, in that case you can try the following Map process to merge rows with the same directory_id into one merged row and use Pivot process on the merged rows like normal:

        ->pipe(new \koolreport\processes\Map([
            "{value}" => function ($row) use (&$state) {
                $directory_id = $row['directory_id'];
                $field_name = $row['field_name'];
                $user_data = $row['user_data'];
                
                $mergedRow = $state['margedRow'];
                $last_directory_id = $state['last_directory_id'];

                if ($directory_id !== $last_directory_id) {
                    $state['last_directory_id'] = $directory_id;
                    $state['mergedRow'] = [
                        'directory_id' => $directory_id
                    ];
                    if ($mergedRow['directory_id'] !== null) {
                        return $mergedRow; // return completed merge row
                    } 
                } else {
                    $mergedRow[$field_name] = $user_data;
                    $state['mergedRow'] = $mergedRow;
                }
            }
        ])) 
        ->pipe(new \koolreport\pivot\processes\Pivot([
            ...

The prerequisite is that your data rows are sorted by directory_id before the Map process. Pls try this and let us know whether it works or not for you.

Roger Mangraviti commented 13 hours ago

thanks for the tips, I am a bit lost on the configuration for the Pivot class. Could you please suggest the next part of the code as a basis to display the pivot table ? I also need an edit column that can include the directory_id in a href link.

Sebastian Morales commented 6 hours ago

PivotTable doesn't have an editable column, is it a data table you were looking for? In any case, you can save the list of field names into $state and add an edit column like this:

//MyReport.php

        $state = [
            'last_directory_id' => null,
            'margedRow' => [
                'directory_id' => null,
            ],
            'fieldNames' => [],
        ];
        
        ...
        ->pipe(new \koolreport\processes\Map([
            "{value}" => function ($row) use (&$state) {
                $directory_id = $row['directory_id'];
                $field_name = $row['field_name'];
                $user_data = $row['user_data'];

                $state['fieldNames'][$field_name] = true; //add $field_name as an array key
                
                $mergedRow = $state['margedRow'];
                $last_directory_id = $state['last_directory_id'];

                if ($directory_id !== $last_directory_id) {
                    $state['last_directory_id'] = $directory_id;
                    $state['editLink'] = getHrefLink($directory_id); // build a link with $directory_id
                    $state['fieldNames']['editLink'] = true; //add 'editLink' as an array key
                    $state['mergedRow'] = [
                        'directory_id' => $directory_id
                    ];
                    if ($mergedRow['directory_id'] !== null) {
                        return $mergedRow; // return completed merge row
                    } 
                } else {
                    $mergedRow[$field_name] = $user_data;
                    $state['mergedRow'] = $mergedRow;
                }
            }
        ])) 
        ...

        $this->state = $state; // save $state as report property

Finally, in your report view you can access list of field names:

//MyReport.view.php
    $fieldNames = array_keys($this->state['fieldNames']); // access report state property
    ... // render PivotTable or DataTables with $fieldNames here

KoolReport's Forum

Pivot tables with dynamic field #3359

Build Your Excellent Data Report