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

get result from raw query execute [solved]

when i'm trying to execute this code

<?php
$nodeDepth = $this->getDi()->get('db')
                        ->query(
                            'SELECT node.id, (COUNT(parent.id) - 1) as depth FROM '
                            .$this->getSchema().'.'.$this->getSource().' AS node, '
                            .$this->getSchema().'.'.$this->getSource().' AS parent WHERE node.lft BETWEEN parent.lft AND parent.rght AND 
                            node.id = '.$id.' GROUP BY node.id ORDER BY node.lft'
                        );
echo '<pre>';
print_r($nodeDepth);
echo '</pre>';
?>

i get this result

Phalcon\Db\Result\Pdo Object
(
    [_connection:protected] => Phalcon\Db\Adapter\Pdo\Postgresql Object
        (
            [_eventsManager:protected] => 
            [_descriptor:protected] => Array
                (
                    [host] => localhost
                    [username] => xxxx
                    [password] => xxxx
                    [dbname] => xxxx
                )

            [_dialect:protected] => Phalcon\Db\Dialect\Postgresql Object
                (
                    [_escapeChar:protected] => "
                )

            [_connectionId:protected] => 1
            [_sqlStatement:protected] => 
            [_sqlVariables:protected] => 
            [_sqlBindTypes:protected] => 
            [_transactionsWithSavepoints:protected] => 0
            [_pdo:protected] => PDO Object
                (
                )

            [_affectedRows:protected] => 
            [_transactionLevel:protected] => 0
            [_type:protected] => pgsql
            [_dialectType:protected] => postgresql
        )

    [_result:protected] => 
    [_fetchMode:protected] => 4
    [_pdoStatement:protected] => PDOStatement Object
        (
            [queryString] => SELECT node.id, (COUNT(parent.id) - 1) as depth FROM app_system.app_menus AS node, app_system.app_menus AS parent WHERE node.lft BETWEEN parent.lft AND parent.rght AND node.id = 1 GROUP BY node.id ORDER BY node.lft
        )

    [_sqlStatement:protected] => SELECT node.id, (COUNT(parent.id) - 1) as depth FROM app_system.app_menus AS node, app_system.app_menus AS parent WHERE node.lft BETWEEN parent.lft AND parent.rght AND node.id = 1 GROUP BY node.id ORDER BY node.lft
    [_bindParams:protected] => 
    [_bindTypes:protected] => 
    [_rowCount:protected] => 
)

when i expected is like this


| id | depth | | bigint | bigint |

0 | 0

please help :)



2.0k
Accepted
answer
<?php
$nodeDepth = $this->getDi()->get('db')
                        ->query(
                            'SELECT node.id, (COUNT(parent.id) - 1) as depth FROM '
                            .$this->getSchema().'.'.$this->getSource().' AS node, '
                            .$this->getSchema().'.'.$this->getSource().' AS parent WHERE node.lft BETWEEN parent.lft AND parent.rght AND 
                            node.id = '.$id.' GROUP BY node.id ORDER BY node.lft'
                        )->fetch();
?>

result Array ( [id] => 1 [0] => 1 [depth] => 0 [1] => 0 )

https://docs.phalcon.io/en/latest/reference/db.html#connecting-to-databases