Array in Query bind method

Need Help
1

Hello,
using cake 3.6.14 I need to bind an array to an existing Query object.

As mentioned in bind code documentation (Query.php), i’m using bind method :

$classTable = TableRegistry::getTableLocator()->get($className);
$query = $classTable->find()->where("state :st");
....
$query->bind(":st", [1, 2], 'int[]');

the problem is that i get Unknown type "int[]“InvalidArgumentException”

cakephp/cakephp/src/Database/TypeConverterTrait.php(34): Cake\Database\Type::build(‘int[]’)

Thank you for your help.

2

Hi dlr, maybe it doesn’t understand “int” ? check this:
https://book.cakephp.org/3.0/en/orm/database-basics.html#data-types

anyway if you don’t put there user data do your really need that bind() ?

3

Hi
in fact it does not recognize ‘string[]’ or anything that it is supposed to.

By looking at Query.php bind documentation i can see :
/**
* Associates a query placeholder to a value and a type.
*
* If type is expressed as “atype[]” (note braces) then it will cause the
* placeholder to be re-written dynamically so if the value is an array, it
* will create as many placeholders as values are in it. For example:
*
* ```
* $query->bind(’:id’, [1, 2, 3], ‘int[]’);

but in fact it seems not to be the case.

I definitely need to do a bind.

Any suggestion will be welcome.

Thanks

4

I see there are no tests for this functionality either, so, it’s entirely possible that it is something that used to work, but has since been broken. I’m investigating further.

Why do you need to use bind? Could you give a more complete example or explanation of what you are trying to do?

5

Hi
thank you for investigating.

My need is to provide my app users with a criteria-based alert system stored in a config file from which they will just have to set thresholds. These thresholds will be passed to requests via the “bind” method. Some criteria are arrays of int or string.

// Config file alerts.php sample
"interventions" => [   // ClassName
            "older_than" => [       // Alert name
                "conditions" => [
                    "created > :supval"
                ],
                "vars" => [
                    ":supval" => ["description" => "greeter than ", "type" => 'datetime']
                ]
            ], 
            "state" => [       
                "conditions" => [
                    "state IN (':state')"
                ],
                "vars" => [
                    ":state" => ["description" => "Billing state", "type" => "string", "select" => ["due" => "due", "closed" => "closed"]]
                ]
            ]
        ]


then i could do something like :
 $alerts = Configure::read("alerts");


        foreach ($alerts as $className => $alertList) {
            $alertTable = TableRegistry::getTableLocator()->get($className);

// this code is only for demonstrating bindings
// i need to provide users input fields in which they can enter values for each 'vars' in config file, then i can do bindings and execute request
            foreach ($alertList as $alertName =>  $alert) {

                $query = $alertTable->find()->where($alert["conditions"]);
                foreach ($alert["vars"] as $name => $val) {
                   $query->bind($name, Date::now()->addMonth(-1), $val['type']);
//or
                    $query->bind($name, "due, closed", $val['type']);
                }

Regards

6

Hi!
1st. I can confirm that in 3.1 this doesn’t works too.
2nd. It doesn’t understand single ‘int’ only single ‘integer’
3rd. I try to find the code which check for [] after ‘integer’, but I can’t it’s too abstract for me :wink: but it’s seems to me that something should make from integer[] many single integers and than call Type::Build() and it is not doing so.

ps. dlr, check spelling, you’ve got ‘greEter than’ in condition. If your config is hardcoded (free of user input) do you really need this bind?

8

From some testing I did the query Builder seems to handle bindings for you.

Have you seen what SQL is returned.

9

Hello everyone, I re-open this topic to find a solution with Cake 4.

According to the Cake 3 documentation, we could indeed do that :

If type is expressed as "atype[]" (note braces) then it will cause the placeholder to be re-written dynamically so if the value is an array, it will create as many placeholders as values are in it. For example:

$query->bind(':id', [1, 2, 3], 'int[]');

but on the Cake 4 documentation, this is no longer possible. How to do ?

Exemple :

$conditions[] = ['Posts.category_id IN'=>$catID];

If $CatID is an array how comes from the user, it’s not very safe?