Using SQL Functions in cakephp returns error

Question

I am following Using SQL Functions to build my query. I tend to get the first due instalment. My query was working (on cakephp3.1) before updating the cakephp to version 3.3 (by composer).

In my Controller

$this->loadModel('Orders');
$order = $this->Orders->find()
        ->where(['order_id' => $orderId])
        ->contain([
            'PaymentInstalments' => function($q) {
                return $q->find('firstDue');
            },
            'Users' => function($q) {
                return $q->select(['email']);
            }
])
->first();

On my paymentInstalments Table

public function findFirstDue(Query $query, array $options)
{
    $alias = $this->alias();

    $query
        ->select([
//          "id" => $query->func()->min("$alias.id"),  ## This use to work before but now is not working
            'id' => $query->func()->min(function($row){
                return $row->id;
            }),
            'order_id', 'amount', 'date_due', 'transaction_id'
        ])
        ->contain([
            'Transactions' => function($q) {
                return $q
                    ->select([
                        'id', 'transaction_date'
                    ])
                    ->where(function ($exp, $q) {
                        return $exp->isNull('transaction_date');
                    });
            }
        ]);

    debug($query->__debugInfo()['sql']);
    die;    
    return $query;
}

Here is print of my query.

'SELECT PaymentInstalments.id AS `PaymentInstalments__id`, PaymentInstalments.order_id AS `PaymentInstalments__order_id`, PaymentInstalments.instalment_num AS `PaymentInstalments__instalment_num`, PaymentInstalments.amount AS `PaymentInstalments__amount`, PaymentInstalments.date_due AS `PaymentInstalments__date_due`, PaymentInstalments.payment_email_sent AS `PaymentInstalments__payment_email_sent`, PaymentInstalments.transaction_id AS `PaymentInstalments__transaction_id`, {
    "id": 41408,
    "order_id": "10000",
    "instalment_num": 1,
    "amount": 100,
    "date_due": "2016-08-25T12:15:00+01:00",
    "payment_email_sent": false,
    "transaction_id": null
} AS `PaymentInstalments`.`id`, Transactions.id AS `Transactions__id`, Transactions.transaction_date AS `Transactions__transaction_date` FROM payment_instalments PaymentInstalments LEFT JOIN transactions Transactions ON ((Transactions.transaction_date) IS NULL AND Transactions.id = (PaymentInstalments.transaction_id)) WHERE PaymentInstalments.order_id in (:c0)'

The problem is if I use "id" => $query->func()->min("$alias.id"), I get this error:

You are required to select the "PaymentInstalments.order_id" field(s)

And if I use this

'id' => $query->func()->min(function($row){
    return $row->id;
}),`

I get this error:

Error: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"id": 41408, "order_id": "10000", "instalment_num": 1, "amount": ' at line 2

Any help please

Answer 1

You cannot use collection methods

$query->min() is a collection method, respectively will execute the query and call the method on the resulting result set, ie it has nothing to do with SQL functions. Just look at your debug output, there's result set data in your SQL query.

See Cookbook > Database Access & ORM > Query Builder > Queries Are Collection Objects

$query->func()->min() is the way to go, that's what generates an SQL function expression object.

There's a bug in the core regarding function expressions

That being said, what you are experiencing is a bug that is present in the foreign key presence existence check, and is being triggered by having expressions in the select list. You should see even more errors, respectively warnings like

Object of class Cake\Database\Expression\FunctionExpression could not be converted to string

Please make sure to always include stuff like that in your questions! If you don't see it, try cranking up your error reporting level.

The cause of that warning, combined with a PHP bug, is the source of the whole problem, the select list ist being passed to array_diff(), which uses string comparison, ie (string)$elementA === (string)$elementB, which will fail since the expression object cannot be converted to a string.

PHP is weird

And now comes an interesting quirk, without that you'd only see a warning, but the query would run fine.

Before PHP 7, if you put the key that is being searched for, ie order_id, directly after the function expression in the select list, then array_diff() won't find it, and say that it is missing. However if you have at least one additional element between it and the function expression, ie something like

'id' => $query->func()->min("$alias.id"),
// instead of here
'amount',
'order_id', // put it here
'date_due',
'transaction_id'

then array_diff() will find it, and the query will execute fine despite the string conversion error being thrown. But that's not all, no no, it wouldn't be PHP if there weren't really weird things going on.

The whole "place the key differently and behavior changes" only happens when array functions like asort() are being invoked inside of the error handler callback. It doesn't matter on what data they are being invoked, it doesn't need to be connected to the error in any way, it could just be something like $foo = []; asort($foo);, that would already cause that weird behavior.

https://3v4l.org/2nT5M

You gotta love PHP :)

Use a hardcoded SQL fragment as a workaround

As a workaround until the bug is fixed, you could pass an SQL fragment as a string instead, like

'id' => 'MIN(PaymentInstalments.id)'

Last but not least

Please report the string conversion problem as a bug over at GitHub.

Answer 2

You could temporarily get it to work by hard-coding it:

$alias = $this->alias();
$query->select(['id' => "MIN($alias.id)"]);