GROUP BY using SSP class

GROUP BY using SSP class

aadityakulkarniaadityakulkarni Posts: 1Questions: 1Answers: 0

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 )
        );
    }

Answers

  • allanallan Posts: 51,272Questions: 1Answers: 7,725 Site admin

    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

Sign In or Register to comment.