Use raw queries in phalconphp

You can use the db service (provided that is what you called it when you registered it) from the Di container.

Then it is as simple as:

$sql         = "SELECT * FROM users";
$connection = $this->db;
$data       = $connection->query($sql);
$data->setFetchMode(\Phalcon\Db::FETCH_ASSOC);
$results    = $data->fetchAll();

Who tell that you can't? :) In previous topic I send you the fastest solution which I know.

I never need that but I think that you can deal it by getting PDO instance. In services.php you should have something like this:

//Setup the database service
$di->set('db', function(){
    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => "localhost",
        "username" => "root",
        "password" => "123456",
        "dbname" => "",
        'options' => [PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_PERSISTENT => TRUE,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC],
    ));
});

Next check the API of Mysql adapter: https://docs.phalcon.io/pl/latest/api/Phalcon_Db_Adapter_Pdo_Mysql.html There are examples of use raw queries.

thanks, what if my Class doesn't have access to db ? I am going to run this query inside a custom validator. and a custom validator is implemented ValidatorInterface and I can't write $this->db inside custom validator methods. in order to get DI , I can call \Phalcon\DI::getDefault() is there any similar way to get $connection ? for example: \Phalcon\DB::getDefault() !!

\Phalcon\DI::getDefault()->getDB() should works.

Or this:

$di = \Phalcon\DI::getDefault();
$db = $di['db']

Hi

is it true that raw sql is 10 times faster than Model::find()? This is what I get :(

Thanks

Yes, I find PHQL 10x slower with even simple queries.

select * from user where concat(ext_prefix,ext_code) = 0015555;

0.031 sec from raw SQL; 0.3xx sec by PHQL

The table has only 1 row.

Hi

is it true that raw sql is 10 times faster than Model::find()? This is what I get :(

Thanks

these answers on this article are very helpful. Tried to do those 3 query methods ( ::find() etc , query builder , phql ) , none of them satisfy my need to do queries. so to summarize what i get from these comments, what i implement on my script is :

$sql            = "SELECT * FROM someTable"
$di             = \Phalcon\DI::getDefault();
$db             = $di['db'];
$data           = $db->query( $sql );
$data->setFetchMode(\Phalcon\Db::FETCH_OBJ);
$results        = $data->fetchAll();

it works well, thank guys

Hello!

What about binding parameters? To prevent SQL Injections. Thanks

You can use the db service (provided that is what you called it when you registered it) from the Di container.

Then it is as simple as:

$sql         = "SELECT * FROM users";
$connection = $this->db;
$data       = $connection->query($sql);
$data->setFetchMode(\Phalcon\Db::FETCH_ASSOC);
$results    = $data->fetchAll();

Same as in PDO

$connection = $this->db;
$statement  = $connection->prepare('SELECT * FROM users WHERE user_id = :uid');
$statement->bindParam(':uid', $userId);
$statement->execute();

Hello!

What about binding parameters? To prevent SQL Injections. Thanks

You can use the db service (provided that is what you called it when you registered it) from the Di container.

Then it is as simple as:

$sql         = "SELECT * FROM users";
$connection = $this->db;
$data       = $connection->query($sql);
$data->setFetchMode(\Phalcon\Db::FETCH_ASSOC);
$results    = $data->fetchAll();