Doctrine 2 DBAL and QueryBuilder - It really works?

Question

I take the example on the Doctrine DBAL online doc:

$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ?')
    ->setParameter(0, $userInputEmail);

First question:

When I call the:

$sql = $queryBuilder->getSQL();

I expect to have back a complete and valid SQL query with the indicated parameter; unfortunately this is not the case and I have back the query still with question mark; is this normal?

Second question:

It's not clear for me (reading the doc) how to exactly proceed with the query builder to retrieve the result; a practical sample may point me on the right direction.

Similar issue I get with the following:

$DBQB
        ->insert('core_users')
        ->values(
                array(
                    'username' => $SETUP['admin']['username'],
                    'secret' => hash($SETUP['util']['hashalgo'], $SETUP['admin']['passwd'])
                )
                );

Where the ->GetSQL() don't give me back a valid SQL query:

INSERT INTO core_users (username, secret) VALUES(qsecofr, b7...);

And obviously this lead me to an error.

Thank you for answers.

Note: this is a very similar question of doctrine dbal querybuilder as prepared statement but also the former didn't get an answer...

https://stackoverflow.com/questions/2095394/doctrine-how-to-print-out-the-real-sql-not-just-the-prepared-statement — felipsmartins, Jul 12 '17 at 14:17

score 2 · Accepted Answer · edited May 27 '20 at 11:32

Perfectly normal. The essence of prepared statements is that the sql is sent to the database server independent of the actual values. The two are always kept apart, and merged at database level; hence getSQLfrom Doctrine is unable to return the fully compiled SQL query.

It is a bit odd that the documentation does not have an actual example of executing the query.

Personally I'm not a big fan of QueryBuilder::setParameter. Instead I do:

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
    ->select('id', 'name')
    ->from('users')
    ->where('email = ?');

$stmt = $conn->executeQuery($queryBuilder->getSQL(),[$userInputEmail]);
$user = $stmt->fetch();

Just need to send an array of values whose order matches your parameter order. As a bonus, you can send an array for WHERE xxx IN(?). Something which prepared statements don't normally handle.

As far as the insert goes, I think you may be overthinking things. The connection object supports insert,update and delete without the need for a query builder.

$conn->insert('core_users',['username'=>'DTrump','secret='Putin']);

=================================

Here is a working example using setParameter

    $qb
        ->select('projectId','gameNumber','fieldName')
        ->from('games')
        ->where('gameNumber = ?')
        ->setParameter(0,11457);

    $stmt = $qb->execute();

    dump($stmt->fetchAll());

Thank you for your answer; it solved my problem despite I still miss why the setParameter method doesn't work... — Francesco Piraneo G., Jul 13 '17 at 07:20
It does work, just not the way you think it should. $qb->execute should run the query just fine. — Cerad, Jul 13 '17 at 10:53
Well, can you point to a complete example? Just to grab the full picture. — Francesco Piraneo G., Jul 13 '17 at 13:58

Doctrine 2 DBAL and QueryBuilder - It really works?

1 Answers1