Simple WHERE doesn't seem to work correctly in 3.0.0

[donatj]

I have the following code that worked fine in 2.x but does not seem to work at in 3.x because it is missing a bind value.

While I could be doing something wrong, I suspect this is a problem with the library as this seems like a simple query that isn't far from the documented examples.

$select = $this->mysqlQueryFactory->newSelect()
    ->cols([ '*' ])
    ->from('users')
    ->where("user_id = ?", [ 10 ])
    ->where("deleted = ?", [ 0 ]);

var_export($select->getStatement());
echo "\n\n";
var_export($select->getBindValues());

In 2.8.1 it generates

'SELECT
    *
FROM
    `users`
WHERE
    user_id = :_1_
    AND deleted = :_2_'

with the following bind values

array (
  '_1_' => 10,
  '_2_' => 0,
)

In 3.0.0 it generates

'SELECT
    *
FROM
    `users`
WHERE
    user_id = ?
    AND deleted = ?'

with this single bind value

array (
  0 => 0,
)

---

It seems like something is happening and my first bind value is getting lost.

[harikt]

Let us look into 3.x .

Does the below code work for you ?

1.

$select = $this->mysqlQueryFactory->newSelect()
    ->cols([ '*' ])
    ->from('users')
    ->where("user_id = ?", 10)
    ->where("deleted = ?", 0);

var_export($select->getStatement());
echo "\n\n";
var_export($select->getBindValues());

2.

$select = $this->mysqlQueryFactory->newSelect()
    ->cols([ '*' ])
    ->from('users')
    ->where("user_id = :user_id")
    ->where("deleted = :deleted")
    ->bindValues([
        'user_id' => 10,
        'deleted' => 0,
    ])
;

var_export($select->getStatement());
echo "\n\n";
var_export($select->getBindValues());

[donatj]

Yep, those generate respectively the following and work as expected.

Should I move all of my queries to named parameters to use 3.x?

I can do that, but I have a lot of stuff using positional ?'s. It'd be nice if the library continued t support ? parameters.

'SELECT
    *
FROM
    `users`
WHERE
    user_id = :user_id
    AND deleted = :deleted'

array (
  'user_id' => 10,
  'deleted' => 0,
)

'SELECT
    *
FROM
    `users`
WHERE
    user_id = :user_id
    AND deleted = :deleted'

array (
  'user_id' => 10,
  'deleted' => 0,
)

[harikt]

Hey @donatj ,

There was a discussion about this over https://github.com/auraphp/Aura.SqlQuery/pull/142 .

I don't force you to make changes and go with 3.x. You may want to consider alternatives like https://github.com/atlasphp or similar ones.

[harikt]

I am closing this issue for now or you can stick on to 2.x series.

[donatj]

That's fine I guess.

Still seems like something is broken in that v3 accepts parameters as the second argument of the where but they don't work.

[harikt]

@donatj can you add a failing test case and send a pull request ?

[rotexdegba]

This issue still exists in the latest version 3.0.0:

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

use Aura\SqlQuery\QueryFactory;

$queryFactory = new QueryFactory('sqlite');
$select = $queryFactory->newSelect();
$select->cols(['*'])
       ->from('some_table')
       ->where('some_col_a = ? ', ['some_val_a']);
$select->where('some_col_b = ? ', ['some_val_b']);

var_dump($select->getStatement());
var_dump($select->getBindValues());

Generates the out put below:

string(84) "SELECT
    *
FROM
    "some_table"
WHERE
    some_col_a = ? 
    AND some_col_b = ? "
array(1) {
  [0]=>
  string(10) "some_val_b"
}

instead of

string(84) "SELECT
    *
FROM
    "some_table"
WHERE
    some_col_a = ? 
    AND some_col_b = ? "
array(2) {
  [0]=>
  string(10) "some_val_a",
  [1]=>
  string(10) "some_val_b"
}

The only way to get the query to work is to change it like this:

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

use Aura\SqlQuery\QueryFactory;

$queryFactory = new QueryFactory('sqlite');
$select = $queryFactory->newSelect();
$select->cols(['*'])
       ->from('some_table')
       ->where('some_col_a = ? ', ['some_val_a']);

$select->where('some_col_b = ? ', [1 => 'some_val_b']); // I had to forcefully add the array key 1

var_dump($select->getStatement());
var_dump($select->getBindValues());

This needs to be fixed & I am willing to work with someone to help get this fixed.

My use case involves building a select query in an ORM for fetching relational data and allowing users of the package to pass a callback that can manipulate select object before the ORM executes the query to fetch the relational data internally, switching to named parameters will not work since the ORM is using question mark placeholder internally meaning that users of the package also have to use question mark placeholders to modify the query if they need to (PDO will not allow mixing named parameters with question mark placeholders).

Thanks

[harikt]

@rotexdegba to be clear, 3.x doesnot support ? , but instead you need to use named place holder like :name . Please refer the documentation https://github.com/auraphp/Aura.SqlQuery/blob/b58a7aab3c3291b31671ace4d64cc31e68f3f2a7/docs/select.md#where

[harikt]

Note : I would suggest looking into https://github.com/atlasphp/Atlas.Query in the future.

[rotexdegba]

@harikt Thanks for the feedback. I will modify my Package ( https://github.com/rotexsoft/leanorm/blob/master/docs/getting-started.md ) to use only named placeholders