Using MySQL user-defined variables with CakePHP3 query select

Question

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.

Answer 1

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.

Answer 2

Using your suggestion, i did this as a resume for using SET + variable:

Controller:

$weeks = $this->Facturas->find('Weeks', $options)->fetchAll('assoc');

Model:

public function findWeeks(Query $query, array $options = []) {
     $conn = ConnectionManager::get('default');
     $conn->execute('SET @row_number = 0');
     $query = 'SELECT @row_number:=@row_number + 1) AS week_number FROM weeks';
     $stmt = $conn->execute($query);
     return $stmt;
}

I am using CakePHP 3.8

Answer 3

Since you're not binding any values, you can simply write the clauses literally without any complexity. Like so:

$query
    ->select(array(
        '@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')
;

I don't have a filled table to test this with and to determine that it does return what you want, but it does return an empty array for this structure (rather than throwing exceptions) using CakePHP:

CREATE TABLE salary (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    house_rent INT DEFAULT 0,
    conveyance INT DEFAULT 0,
    medical INT DEFAULT 0,
    dearness INT DEFAULT 0,
    others_allowances INT DEFAULT 0,
    income_tax INT DEFAULT 0,
    pro_tax INT DEFAULT 0,
    emp_state_insu INT DEFAULT 0,
    absence_fine INT DEFAULT 0,
    others_deductions INT DEFAULT 0
);