GROUP BY using SSP class

aadityakulkarni

Modified the ssp class to use join and group by. The queries themselves seem to run fine.

But the pagination attributes 'recordTotal' and 'recordsFiltered' are now messed up.

Any advice?

Modified complex function in the ssp class:

static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null, $whereCondition, $joinCondition=null, $selectQuery=null)
    {
        //echo $whereCondition;
        $bindings = array();
        $db = self::db( $conn );
        $localWhereResult = array();
        $localWhereAll = array();
        $whereAllSql = '';
        // Build the SQL query string from the request
        $limit = self::limit( $request, $columns );
        $order = self::order( $request, $columns );
        $where = self::filter( $request, $columns, $bindings );
        $whereResult = self::_flatten( $whereResult );
        $whereAll = self::_flatten( $whereAll );
        if ( $whereResult ) {
            $where = $where ?
                $where .' AND '.$whereResult :
                'WHERE '.$whereResult;
        }
        if ( $whereAll ) {
            $where = $where ?
                $where .' AND '.$whereAll :
                'WHERE '.$whereAll;
            $whereAllSql = 'WHERE '.$whereAll;
        }
        $whereCondition = preg_replace('/(\v|\s)+/', ' ', $whereCondition);
        //echo $whereCondition . "<br/><br/>";
        // Main query to actually get the data
        //echo $where;
        //$query = "SELECT ".implode(", ", self::pluck($columns, 'db'))." FROM `$table` $joinCondition $whereCondition $order $limit";
        if(isset($selectQuery)) {
            $query = "$selectQuery FROM `$table` $joinCondition $whereCondition $order $limit";
        }
        else {
            $query = "SELECT ".implode(", ", self::pluck($columns, 'db'))." FROM `$table` $joinCondition $whereCondition $order $limit";
        }
        //echo $query;
        //echo $whereCondition;
        //echo $query;
        $data = self::sql_exec( $db, $bindings,$query);
        // Data set length after filtering
        //print_r($data);
        $query2 = "SELECT COUNT({$primaryKey}) FROM $table $joinCondition $whereCondition";
        //echo $query2;
        $resFilterLength = self::sql_exec( $db, $bindings,$query2);
        $recordsFiltered = $resFilterLength[0][0];
        // Total data set length
        $query3 = "SELECT COUNT({$primaryKey}) FROM `$table` $joinCondition" . $whereAllSql;
        //echo $query3;
        $resTotalLength = self::sql_exec( $db, $bindings,$query3);
        $recordsTotal = $resTotalLength[0][0];
        /*
         * Output
         */
        return array(
            "draw"            => isset ( $request['draw'] ) ?
                intval( $request['draw'] ) :
                0,
            "recordsTotal"    => intval( $recordsTotal ),
            "recordsFiltered" => intval( $recordsFiltered ),
            "data"            => self::data_output( $columns, $data )
        );
    }

allan

I don't actually see the group by command in the above code for any of the queries - am I just being daft? Either way, you'd need to add it to all three queries.

Allan