将 GROUP BY 支持添加到复杂函数中的 ssp 类

Question

我正在使用数据表 v 1.10.19 当我使用以下时，该组破坏了分页，只显示一页。

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

对于第二个建议：

从 ssp：：complex json 编码语句中删除了 GROUP BY 子句，并且

// 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 ( 似乎您在分组后按限制和偏移量排序(并且按 id DESC 分组是错误的(

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

Answer 2

在上述更改之后，可以轻松地通过 ssp：：complex(( 语句传递group by子句或任何其他子句，如下所示：

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

SSP复杂功能的变化。.CLASS。.PHP：

static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null)

自

static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null, $extra=null )

和

// 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 $extra
$order
$limit "
);