php - 为复杂函数中的 ssp 类添加 GROUP BY 支持

Question

我正在使用 datatables v 1.10.19 因为我正在使用以下内容，group by 破坏了分页并且只显示一页。

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

这是一个复杂的函数:

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

问题是应该添加/更改什么以添加对 GROUP BY 子句的支持但是，我可以使用属性后面的数据表使其显示 GROUP BY DESC 但如果它是服务器会更好边:

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

更新:

根据@scaisEdge 的建议:

对于第一个建议，我更改了以下内容:

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

对于第二个建议:

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

完美运行:)

Answer 1

两个建议

1 ) 在此代码中，您应该为 $where 使用字符串连接

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

2 ) 似乎你有 order by limit 和 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";