Improve IN statement for 3.0 release

[syrm]

Hello,

I think we can improve the IN statement for 3.0 release.
Maybe allow this syntax :
$query->where('user_Id IN (?)', [1, 2, 3, 4, 5]);

And the builder will expand the ? into ?, ?, ?, ?, ?

If this solution is ok for you i can make a PR.

[harikt]

FYI : https://github.com/atlasphp/Atlas.Query ;) . May be you are interested ?

[syrm]

Thank you, i already know it, and it's SqlQuery i need ;-)

[designermonkey]

I already thought that this library did this... I was just about to log this as a bug!

Aura.SQL does array quoting, so I assumed this one did too.

[jimbo8098]

Noting the same in 3.0-dev (513747a) and 2.7.1. According to https://github.com/auraphp/Aura.SqlQuery/blob/3.x/docs/select.md#where it should work but I'm having issues which is the current latest on 3.x branch, specifically pulling in 513747a . To make sure I wasn't going mental I created a test table:

mysql> select * from test;
+------+------+
| num1 | num2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
+------+------+

Then I wrote a small test page:

<?php
include "./vendor/autoload.php";

use Aura\SqlQuery\QueryFactory;
$pdo = new PDO("mysql:dbname=test;host=127.0.0.1",'user','pass');

$queryFactory = new QueryFactory('mysql');
$select = $queryFactory->newSelect();
$select
  ->cols([
    'num1',
    'num2'
  ])
  ->from('test')
  ->where('num1 IN (:test)',[':test' => ["1","2","3"]]);
echo $select->getStatement() . "<br/><br/>";
var_dump($select->getBindValues());

$stm = $pdo->prepare($select->getStatement());
$stm->execute($select->getBindValues());
echo "<br/><br/>";
var_dump($stm->fetchAll());
?>

Pretty simple, it should output rows 1 and 2 of the test table, however I actually get:

SELECT num1, num2 FROM `test` WHERE num1 IN (:test)

array(1) { [":test"]=> array(3) { [0]=> string(1) "1" [1]=> string(1) "2" [2]=> string(1) "3" } }
Notice: Array to string conversion in /var/www/html/test.php on line 20


array(0) { }

Line 20 being $stm->execute($select->getBindValues());

So I believe there is still an issue here where getBindValues() doesn't output as their docs seem to suggest.

[harikt]

@jimbo8098 if you are starting to use this package, I would recommend you use https://github.com/atlasphp/Atlas.Query which is a descendant of Aura.SqlQuery .

[jimbo8098]

I did make a similar workaround last night for MySQL but it was a different project. My solution involved generating a list of variables and using bindValue to assign the intended value to the variable. Happy to PR my solution but I'm not 100% if it would be applicable in other drivers. If someone could advise on that, it would be excellent. Meantime I will PR for MySQL and I'll look to add a test to the phpunit declarations (which only use having())

[harikt]

There is already a PR : #162

[jimbo8098]

Thanks @harikt , that PR would be perfect. The use of Aura.SqlQuery is the descision of the project maintainer. Without wishing to rock the boat too much, I wouldn't be able to change it at the moment.