Chartjs-How to chart missing data in chartjs

by

0👍

@nbk provided the clue to the resolution with a pivot table. Here’s my solution, a PHP function in the controller to dynamically build SQL for a pivot table.

Might move it to a general purpose utility lib in the future.

    private function pivotTable($table, $pivotColumn, $pivotLabel,  $whereClause, $aggregate="count"){
        $returnValue = [];

        // validate parms exist
        if ((isset($table) != true) ||
            (isset($pivotLabel) != true) ||
            (isset($pivotColumn) != true)) {
                $returnValue['error'] = true;
                $returnValue['message'] = 'Invalid parameter';
                return $returnValue ;
            }

        // build the SQL to get the column names and column count
        $qry =  "SELECT DISTINCT {$pivotColumn} FROM {$table} ";
        $where =    (isset($whereClause)) ? "WHERE {$whereClause} " : "";
        $order = "ORDER BY {$pivotColumn};";
        // execute the query
        $columns = $this->db->query($qry . $where . $order);

        // no columns  to pivot
        if( $columns->num_rows() < 1) {
            $returnValue['error'] = true;
            $returnValue['message'] = 'No columns to pivot';
            return $returnValue ;
        }

        // too many columns to pivot
        if($columns->num_rows() > 50) {
            $returnValue['error'] = true;
            $returnValue['message'] = 'Pivot would result in more than 50 columns';
            return $returnValue ;
        };

        // build pivot query
        $i = 0;
        $pivotQry = "SELECT {$pivotLabel} ";
        foreach($columns->result_array() as $column) {
            $pivotQry .= ",\r\n{$aggregate}( IF ( {$pivotColumn} = '{$column[$pivotColumn]}', {$pivotColumn}, NULL ) ) as  {$column[$pivotColumn]} ";
        }

        $pivotQry .= "\r\nFROM {$table} \r\n";
        $pivotQry .= $where;
        $pivotQry .= "GROUP BY {$pivotLabel} ";

        $returnValue['error'] = false;
        $returnValue['message'] = 'Ok';
        $returnValue['sql'] = $pivotQry;
        return $returnValue ;
    }

Leave a comment