mysql - 将 MySQL 用户定义变量与 CakePHP3 查询选择结合使用

Question

这里是an example我需要什么:

SELECT 
    @earnings := (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings ,
    @deductions := (`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions,
    @earnings - @deductions AS net_salary 
FROM 
    salary

就我而言，我需要将 SUM(Table.total) 作为单独的 salesTotal 列返回，并在 @salesTotal/内重用@salesQuantity。

这是我尝试过的:

1.

$query->select([
    $query->newExpr([
        '@salesTotal := ',
        $query->func()->sum('Table.total')
    ]),
]);

这会生成@salesTotal := AND SUM(Table.total)

2.

$query->select([
    $query->newExpr([
        '@salesTotal := ' . $query->func()->sum('Table.total')
    ]),
]);

导致警告(4096):Cake\Database\Expression\FunctionExpression 类的对象无法转换为字符串

3.

$query->select([
    $query->newExpr([
        '@salesTotal := SUM(Table.total)'
    ]),
]);

收到警告 (2): array_combine() :两个参数应具有相同数量的元素 CORE/src/ORM/ResultSet.php, line 527 。这是not a good solution要么。

Answer 1

灵感来自this answer ，想法是声明变量，然后重用它来计算其他值。这种方法可能很有值(value)，尤其是对于复杂的计算。我用过\Cake\ORM\Query::newExpr()与 an edited conjunction .

此外，根据the MySQL doc on user-defined variables ，我尝试预先设置它们。

In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type.

To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it.

这是我收到的查询:

$connection = $this->{'Example/Table'}->getConnection();
$connection->execute('SET @varSalesTotal = 0.0;');
$connection->execute('SET @varSalesQuantity = 0;');

$query = $this->{'Example/Table'}->find('all', [
    // ...
]);

$query->select([

    // calculate the @varSalesTotal variable
    // SQL: @varSalesTotal := SUM(ExampleTable.total)
    'varSalesTotalCol' => $query->newExpr([
        '@varSalesTotal',
        $query->func()->sum('ExampleTable.total'),
    ])->setConjunction(':='),

    // calculate the @varSalesQuantity variable
    // SQL: @varSalesQuantity := SUM(ExampleTable.quantity)
    'varSalesQuantityCol' => $query->newExpr([
        '@varSalesQuantity',
        $query->func()->sum('ExampleTable.quantity'),
    ])->setConjunction(':='),

    // attempt to calculate another value reusing the variables
    // SQL: @varSalesTotal/@varSalesQuantity AS calcPriceVar
    'calcPriceVar' => $query->newExpr([
        '@varSalesTotal',
        '@varSalesQuantity',
    ])->setConjunction('/'),

]);

注意:varSalesTotalCol 和 varSalesQuantityCol 键是必需的，否则我会得到

Warning (2): array_combine(): Both parameters should have an equal number of elements [CORE/src/ORM/ResultSet.php, line 527]

Warning (4096): Argument 1 passed to Cake\ORM\Entity::__construct() must be of the type array, boolean given, called in vendor/cakephp/cakephp/src/ORM/ResultSet.php on line 601 and defined [CORE/src/ORM/Entity.php, line 48]

最终结果，calcPriceVar 为 null，所以显然这不起作用。可能是因为这个:

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

我仍然发布此内容，因为我找不到任何使用 CakePHP 构建类似查询或以这种方式使用 newExpr() 的示例。我花了很长时间才弄清楚，所以这个可以作为引用。

我最终选择的工作选项是:

$query->select([
    // regular calculated columns
    'salesTotal' => $query->func()->sum('ExampleTable.total'),
    'salesQuantity' => $query->func()->sum('ExampleTable.quantity'),
    // calculate again, without reusing any previously calculated results
    'calcPrice' => $query->newExpr([
        $query->func()->sum('ExampleTable.total'),
        $query->func()->sum('ExampleTable.quantity'),
    ])->setConjunction('/'),
]);

我不喜欢无法重用计算的事实，如果有更好的方法，我很乐意学习。