Adding GROUP BY support to the ssp class in complex function

I am using datatables v 1.10.19 As i was using the following ,the group by destroys the pagination and only shows one page.

        $where = "recipient='".$recipient."' AND grouped='' GROUP BY id DESC";
        echo json_encode(
            SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, $where )
        );

here is the complex function for this:

static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )
{
    $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;
    }

    // Main query to actually get the data
    $data = self::sql_exec( $db, $bindings,
        "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
         FROM `$table`
         $where
         $order
         $limit "
    );

    // Data set length after filtering
    $resFilterLength = self::sql_exec( $db, $bindings,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`
         $where"
    );
    if(empty($resFilterLength)){$recordsFiltered="['1','2']";}else{
    $recordsFiltered = $resFilterLength[0][0];
    }       
    //$recordsFiltered = $resFilterLength[0][0];

    // Total data set length
    $resTotalLength = self::sql_exec( $db, $bindings,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table` ".
        $whereAllSql
    );
    if(empty($resTotalLength)){$recordsTotal="['1','2']";}else{
    $recordsTotal = $resTotalLength[0][0];
    }           
    //$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 )
    );
}

The question is what should be added/changed to add support to GROUP BY clause However, i can use datatables following property to make it show GROUP BY DESC but it would be better if it is server side:

'order': [4, 'desc'],

UPDATE:

As Suggested by @scaisEdge :

For 1st suggestion i changed the following:

    // Data set length after filtering
    $resFilterLength = self::sql_exec( $db, $bindings,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`
         $where"
    );

    // Data set length after filtering
    $resFilterLength = self::sql_exec( $db, $bindings,
        "SELECT COUNT(`{$primaryKey}`)
         FROM   `$table`".
         $where
    );

For 2nd suggestion :

removed the GROUP BY clause from ssp::complex json encode statement and

    // Main query to actually get the data
    $data = self::sql_exec( $db, $bindings,
        "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
         FROM `$table`
         $where
         $order
         $limit "
    );

// Main query to actually get the data
$data = self::sql_exec( $db, $bindings,
    "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
     FROM `$table`
     $where GROUP BY id DESC
     $order
     $limit "
);

works perfectly 🙂

Answer

two suggestion

1 ) in this code you should use string concatenation for $where

$resFilterLength = self::sql_exec( $db, $bindings,
    "SELECT COUNT(`{$primaryKey}`)
     FROM   `$table` " .   $where 
);

2 ) seems you have order by limit and offset for pagination after group by (and group by id DESC is wrong)

   $where = "recipient='".$recipient."' AND grouped=''  
    GROUP BY id ORDER BY id DESC LIMIT 10 OFFSET 5";

Advertisement

Answer