[FIXED] Using MySQL user-defined variables with CakePHP3 query select

Issue

Here's an example of what I need:

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

In my case, I need a SUM(Table.total) to be returned as a separate salesTotal column, as well as reused inside @salesTotal/@salesQuantity.

Here's what I tried:

1.

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

This generates @salesTotal := AND SUM(Table.total)

2.

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

Results in Warning (4096): Object of class Cake\Database\Expression\FunctionExpression could not be converted to string

3.

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

Getting Warning (2): array_combine(): Both parameters should have an equal number of elements CORE/src/ORM/ResultSet.php, line 527. It's not a good solution either.

Solution

Inspired by this answer, the idea was to have the variables declared, and then reused for calculating some other value. This approach could be valuable especially with complex calculations. I used \Cake\ORM\Query::newExpr() with an edited conjunction.

Moreover, according to the MySQL doc on user-defined variables, I tried to SET them up upfront.

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.

Here's the query I got:

$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('/'),

]);

Note: the varSalesTotalCol and varSalesQuantityCol keys are necessary, otherwise I was getting

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]

In the end result, calcPriceVar was null, so apparently this didn't work. Could be because of this:

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.

I'm still posting this as I couldn't find any example of building similar queries with CakePHP or using newExpr() in such a way. It took me quite a while to figure it out, so this could serve as a reference.

The working option I finally went with is this:

$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('/'),
]);

I don't like the fact that I can't reuse the calculations, and if there's a better way, I'd be happy to learn.

Answered By - aexl