We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Query builder to raw sql no longer working after 2.0.0 upgrade

Hi,

In my project I use the following code to get the raw SQL out of a query builder. This worked great in Phalcon 1.3.4 but throws an error in 2.0.0.

<?php
    /**
     * @param \Phalcon\Mvc\Model\Query\Builder $builder
     */
    public function dumpSqlBeforeExecute ( $builder )
    {

        $db = $this->getDI()->get('db');

        $intermediate = $builder->getQuery()->parse();
        $sqlVariables = $builder->getQuery()->getBindParams();

        $dialect      = $db->getDialect();
        $sql          = $dialect->select($intermediate);

        if( is_array( $sqlVariables ) ) {

            foreach ( $sqlVariables as $key => $value ) {
                if ( !is_numeric( $value ) ) $value = '"' . $value . '"';
                $sqlVariables[ ':' . $key ] = $value;
                unset( $sqlVariables[ $key ] );
            }

            $keys = array_keys( $sqlVariables );
            $query = str_replace( $keys , $sqlVariables , $sql );
        } else {
            $query = $sql;
        }

        return $query

    }

The error thrown:

Notice: Undefined index: 0 in phalcon/db/dialect.zep on line 364 in /www/app/myModule/src/MyModule/Service/QueryService.php on line 28

Should I follow another approach to get this result in 2.0.0. or is this a bug (that I should report?)



1.3k
edited Apr '15

It looks like I might have been a bit to hasty with my report. Turns out that none of my query builder based queries, that all worked fine using 1.3.4, work anymore. No mather if I feed them to pagination or do $builder->getQuery()->execute( ). They all end up with a trace like this one:

0 Wrong number of parameters

TRACE

0 [internal function]: Phalcon\Mvc\Model\Resultset\Complex->__construct(Array, Object(Phalcon\Mvc\Model\Row), Object(Phalcon\Db\Result\Pdo), NULL, false)

1 [internal function]: Phalcon\Mvc\Model\Query->_executeSelect(Array, Array, NULL)

2/www/app/myModule/src/MyModule/Model/CatGridDynamicEntity.php(95): Phalcon\Mvc\Model\Query->execute()

3 /www/app/myModule/src/MyModule/Model/CatGridDynamicEntity.php(183): MyModule\Model\CatGridDynamicEntity->buildGridQuery()

4 /www/app/myModule/src/MyModule/Controller/IndexController.php(112): MyModule\Model\CatGridDynamicEntity->getCatGrid()

5 [internal function]: MyModule\Controller\IndexController->listAction()

6 [internal function]: Phalcon\Dispatcher->dispatch()

7 /www/public/index.php(103): Phalcon\Mvc\Application->handle()

8 {main}

So, the earlier mentioned error might just be rooted deeper (or not). Anyway, I really need to get my query builders working again.