php - Cakephp 数据表 SSP 分组依据

Question

我有以下代码。我想在服务器端的数据表 SSP 类上执行 group by 子句。

$loggedInUser = $this->Auth->user();
        $loggedInUser = CommonUtil::DataTableToArr($loggedInUser);
        $this->autoRender = false;

        $table = <<<EOT
        (select UserId , sum(GrossRate) as UserRate, sum(UserRate) , sum(OfferwallRate) , 
        sum(NumberOfTasks) from offerwalldb.offerwall_transaction_by_users                
        group by UserId
        ) tmp 
        EOT;

        $primaryKey = 'Id';
        Log::debug($this->request->data);
        $columns = array(
            array( 'db' => 'UserId', 'dt' => 0, 'field' => 'UserId' ),
            array( 'db' => 'UserRate',  'dt' => 1, 'field' => 'Rate' )

        );

        $result = SSPGroup::simple($_GET, CommonUtil::$dbDetails, $table, $primaryKey, $columns );        
        Log::debug($result);
        echo json_encode(
            $result
        );

我收到以下错误

syntax error, unexpected end of file in 

我也厌倦了 github 上的扩展版本，但它也不起作用。另外我怎样才能添加一个where子句？

我使用的是cakephp3。

Answer 1

我找到了这个很棒的库

https://datatables.ozdemir.be/group-by

任何卡住的人都应该看看这个。

use Ozdemir\Datatables\Datatables;
use Ozdemir\Datatables\DB\MySQL;

public function GetDataByUsers()
    {
        $loggedInUser = $this->Auth->user();
        $loggedInUser = CommonUtil::DataTableToArr($loggedInUser);
        $this->autoRender = false;

        $config = [
            'host'     => 'localhost',
            'port'     => '3306',
            'username' => 'root',
            'password' => '',
            'database' => 'offerwalldb'
        ];

        $dt = new Datatables(new MySQL($config));

        $dt->query('select UserId ,SUM(GrossRate) as GrossRate
                    from offerwalldb.offerwall_transaction_by_users                
                    group by UserId');

        echo $dt->generate();
    }