When the complex queries we can not use ORM or Phalcon Query Language(PHQL) so that in this case we need to use PDO Phalcon or Resultset to do this. This tips will show you how to call raw SQL query for insert,update,select, and delete in Phalcon PHP Framework.

<?php

use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;

class Robots extends Model
{
    public static function rawfindByCreateInterval()
    {
        // A raw SQL statement
        $sql = "SELECT * FROM robots WHERE id > 0";

        // Base model
        $robot = new Robots();

        // Execute the query
        return new Resultset(
            null,
            $robot,
            $robot->getReadConnection()->query($sql)
        );
    }

    public static function rawUpdate()
    {
        $sql = "UPDATE robots SET published = 'active'";
        $this->getReadConnection()->execute($sql);
    }
}

As you can see, the rawfindByCreateInterval() method returns an instance of \Phalcon\Mvc\ Model\Resultset\Simple.

The rawUpdate() method just executes the query in this example, I will mark all the articles as published. You might have noticed the getReadConnection() method. This method is very useful when you need to iterate over large data.

Or you can use a master-slave connection. For example, consider the following code snippet:

class Robots extends Model
{
    public static function rawfindByCreateInterval()
    {
        // A raw SQL statement
        $sql = "SELECT * FROM robots WHERE id > 0";

        // Base model
        $robot = new Robots();

        // Execute the query
        return new Resultset(
            null,
            $robot,
            $robot->getReadConnection()->query($sql)
        );
    }

    public static function rawUpdate()
    {
        $sql = "UPDATE robots SET published = 'active'";
        $this->getReadConnection()->execute($sql);
    }
    public function initialize()
    {
           $this->setReadConnectionService('slave_db_connection_service'); // By default is 'db'
           $this->setWriteConnectionService('db');
    }
}

Above I use a Resultset of Phalcon but we can use PDO, for example:

use Phalcon\DI\FactoryDefault;

class Robots extends Model
{
    public static function rawfindByCreateInterval()
    {
        // A raw SQL statement
        $sql = "SELECT * FROM robots WHERE id > 0";
        $db = self::getConnection();

        return $db->query($sql);

    }

    public static function rawUpdate()
    {
        $sql = "UPDATE robots SET published = 'active'";
        $db = self::getConnection();

        return $db->execute($sql);
    }

    public static function getConnection()
    {
        $di = FactoryDefault::getDefault();

        return $di->get('db');
    }
}

That it, very simple right?