Pivot table plugin - Grouping on database joined fields

[PaulV888]

Hello,

Issue: I want to group on database joined fields. I try to map several categories into 1 reporting category. When setting up this pivot then the individual counts on the row do not add up to the row totals. And the grand total does not add up to the row totals.

Cause: Multiple rows are returned for one group. The sql produced for this, groups by _raw values instead of the joined values

SELECT SQL_CALC_FOUND_ROWS DISTINCT `mc_contracts`.`id` AS `mc_contracts___id`, `mc_contracts`.`id` AS `mc_contracts___id_raw`, `mc_contracts`.`productID` AS `mc_contracts___productID_raw`, `mc_products`.`status` AS `mc_contracts___productID`, `mc_contracts`.`statusID` AS `mc_contracts___statusID_raw`, `mc_status`.`deployment_status_group` AS `mc_contracts___statusID`, `mc_contracts`.`id` AS slug , `mc_contracts`.`id` AS `__pk_val` ,COUNT(`mc_contracts`.`id`) AS `mc_contracts___id`, COUNT(`mc_contracts`.`id`) AS `mc_contracts___id_raw` FROM `mc_contracts` LEFT JOIN `mc_products` AS `mc_products` ON `mc_products`.`id` = `mc_contracts`.`productID` LEFT JOIN `mc_status` AS `mc_status` ON `mc_status`.`id` = `mc_contracts`.`statusID` WHERE ( mc_contracts.statusID NOT IN ('99','100','101') )
--> GROUP BY `mc_contracts`.`statusID`, `mc_contracts`.`productID`
ORDER BY `mc_status`.`deployment_status_group` asc

Suggested fix: Remove line 96 in pivot.php. This will result in a group by fabrik_element names and seems to work fine.

    private function group()
    {
        $params = $this->getParams();
        $groups = explode(',', $params->get('pivot_group'));
 
        foreach ($groups as &$group)
        {
            $group = trim($group);
-->      //$group = FabrikString::safeColName($group);
        }

The resulting SQL for this is:

SELECT SQL_CALC_FOUND_ROWS DISTINCT `mc_contracts`.`id` AS `mc_contracts___id`, `mc_contracts`.`id` AS `mc_contracts___id_raw`, `mc_contracts`.`productID` AS `mc_contracts___productID_raw`, `mc_products`.`status` AS `mc_contracts___productID`, `mc_contracts`.`statusID` AS `mc_contracts___statusID_raw`, `mc_status`.`deployment_status_group` AS `mc_contracts___statusID`, `mc_contracts`.`id` AS slug , `mc_contracts`.`id` AS `__pk_val` ,COUNT(`mc_contracts`.`id`) AS `mc_contracts___id`, COUNT(`mc_contracts`.`id`) AS `mc_contracts___id_raw` FROM `mc_contracts` LEFT JOIN `mc_products` AS `mc_products` ON `mc_products`.`id` = `mc_contracts`.`productID` LEFT JOIN `mc_status` AS `mc_status` ON `mc_status`.`id` = `mc_contracts`.`statusID` WHERE ( mc_contracts.statusID NOT IN ('99','100','101') )
-->GROUP BY mc_contracts___statusID, mc_contracts___productID
ORDER BY `mc_status`.`deployment_status_group` asc

After doing this, all rows add up as well as grand total, and all is good and happy.

Regards,
Paul

 

[cheesegrits]

Did you test it on non joins?

-- hugh

 

[PaulV888]

I had not, I just did a quick test and it looks good. No errors and numbers add up.

Paul

 

[cheesegrits]

OK, I've merged it.

I don't use the pivot plugin so y'all will have to test and make sure this doesn't break anything else.

-- hugh