Get raw sql from Phalcon query builder

Question

Is it possible to extract raw sql query from the query builder instance in Phalcon? Something like this?

$queryBuilder = new Phalcon\Mvc\Model\Query\Builder();
$queryBuilder
    ->from(…)
    ->where(…);

$rawSql = $queryBuilder->hypotheticalGetRawQueryMethod();

score 20 · Accepted Answer · edited Jun 07 '15 at 10:10

By error and trial the below seems to working. Would be great if someone could confirm if there's a better way.

$queryBuilder = new Builder();
$queryBuilder->from(…)->where(…);

$intermediate = $queryBuilder->getQuery()->parse();
$dialect      = DI::getDefault()->get('db')->getDialect();
$sql          = $dialect->select($intermediate);

Edit: As of 2.0.3 you can do it super simple, see comment for full details:

$modelsManager->createBuilder()
    ->from('Some\Robots')
    ->getQuery()
    ->getSql()

dompie · Answer 2 · 2014-01-24T22:44:19.217

2

you can use getRealSqlStatement() (or similar function name) on the DbAdapter. See http://docs.phalconphp.com/en/latest/api/Phalcon_Db_Adapter.html

According to documentation you can get this way the resulting sql query.

Or wait, this might not work on querybuilder. Otherwise you can setup low level query logging: http://docs.phalconphp.com/en/latest/reference/models.html#logging-low-level-sql-statements

edited Jan 24 '14 at 22:44

answered Jan 24 '14 at 22:15

dompie

711
1
7
12

score 1 · Answer 3 · answered Nov 27 '14 at 13:42

$db = Phalcon\DI::getDefault()->getDb();
$sql = $db->getSQLStatement();
$vars = $db->getSQLVariables();
if ($vars) {
    $keys = array();
    $values = array();
    foreach ($vars as $placeHolder=>$var) {
        // fill array of placeholders
        if (is_string($placeHolder)) {
            $keys[] = '/:'.ltrim($placeHolder, ':').'/';
        } else {
            $keys[] = '/[?]/';
        }
        // fill array of values
        // It makes sense to use RawValue only in INSERT and UPDATE queries and only as values
        // in all other cases it will be inserted as a quoted string
        if ((strpos($sql, 'INSERT') === 0 || strpos($sql, 'UPDATE') === 0) && $var instanceof \Phalcon\Db\RawValue) {
            $var = $var->getValue();
        } elseif (is_null($var)) {
            $var = 'NULL';
        } elseif (is_numeric($var)) {
            $var = $var;
        } else {
            $var = '"'.$var.'"';
        }
        $values[] = $var;
    }
    $sql = preg_replace($keys, $values, $sql, 1);
}

More you can read there

score 1 · Answer 4 · answered Jul 28 '20 at 18:54

The following is the common solution:

$result = $modelsManager->createBuilder()
            ->from(Foo::class)
            ->where('slug = :bar:', ['bar' => "some-slug"])
            ->getQuery()
            ->getSql();

But you might not expect to see the query without its values, like in:

die(print_r($result, true));

Array
(
    [sql] => SELECT `foo`.`id`, `foo`.`slug` FROM `foo` WHERE `foo`.`slug` = :bar
    [bind] => Array
        (
            [bar] => some-slug
        )

    [bindTypes] => 
)

So, this simple code might be useful:

public static function toSql(\Phalcon\Mvc\Model\Query\BuilderInterface $builder) : string
    {
        $data = $builder->getQuery()->getSql();

        ['sql' => $sql, 'bind' => $binds, 'bindTypes' => $bindTypes] = $data;

        $finalSql = $sql;
        foreach ($binds as $name => $value) {
            $formattedValue = $value;

            if (\is_object($value)) {
                $formattedValue = (string)$value;
            }

            if (\is_string($formattedValue)) {
                $formattedValue = sprintf("'%s'", $formattedValue);
            }
            $finalSql = str_replace(":$name", $formattedValue, $finalSql);
        }

        return $finalSql;
    }

score 0 · Answer 5 · answered Oct 15 '19 at 11:15

0

If you're using query builder then like given below then getPhql function can serve the purpose as per phalcon 3.4.4 version.


    $queryBuilder = new Builder();
    $queryBuilder->from(…)->where(…)->getQuery();
    $queryBuilder->getPhql();

answered Oct 15 '19 at 11:15

Vinod Chauhan

11
1

score 0 · Answer 6 · answered Jun 22 '20 at 18:45

if (!function_exists("getParsedBuilderQuery")) {
    /**
     * @param \Phalcon\Mvc\Model\Query\BuilderInterface $builder
     *
     * @return null|string|string[]
     */
    function getParsedBuilderQuery (\Phalcon\Mvc\Model\Query\BuilderInterface $builder) {
        $dialect = Phalcon\Di::getDefault()->get('db')->getDialect();
        $sql = $dialect->select($builder->getQuery()->parse());

        foreach ($builder->getQuery()->getBindParams() as $key => $value) {
            // For strings work fine. You can add other types below
            $sql = preg_replace("/:?\s?($key)\s?:?/","'$value'",$sql);
        }
        return $sql;
    }
}

Simple function that im using for debugging.

Get raw sql from Phalcon query builder

6 Answers6