pivot tables with dynamic field

Roger Mangraviti commented on Oct 16

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 on Oct 16

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 on Oct 17

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 on Oct 18

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 on Oct 18

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 on Oct 18

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 on Oct 18

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

Sebastian Morales commented on Oct 21

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 on Oct 22

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 on Oct 22

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

Roger Mangraviti commented on Oct 24

thats great, happy to use PivotTable, can I get a sample including the PivotTable configuration ?

I have not implemented PivotTable before have no reference in how to implement the above

Sebastian Morales commented on Oct 24

The above code only converts your vertical data into traditional data rows. You can then use the converted data to your suit. The simplest way is a data table.

You can get an idea of what a PivotTable does from this demo:

https://www.koolreport.com/examples/reports/pivot/years_months_customers_categories/

Roger Mangraviti commented on Oct 24

thanks for your feedback. Your tips helped me scope and understand the solution.

I am including the solution that worked well for us incase other members are interested; `

        ->pipe(new Map([
            '{value}' => function ($row, $meta, $index, $mapState) {
                $fieldName = ucwords(strtolower(str_replace('_', ' ', $row['field_name'])));
                $mapState[$row['directory_id']][$fieldName] = $row['user_data'];
                $mappedRows = [];
                return ['{rows}' => $mappedRows, '{state}' => $mapState];
            },
            '{end}' => function ($count, $mapState) {
                $rowsToSend = [];
                foreach ($mapState as $dataK => $dataV) {
                    $dataV['directory_id'] = $dataK;
                    asort($dataV);
                    $rowsToSend[] = $dataV;
                }
                return $rowsToSend;
            }
        ]))

KoolReport's Forum

Pivot tables with dynamic field #3359

Build Your Excellent Data Report