How to execute a SQL string in simple way?

Thanks!

Is there any way to execute the raw sql query in static method by directly execute $something->query($sql)?

If you want to execute raw sql query in non static method in model

class Robots Extends Model
{

   public function findSomething()
   {
       $sql = "Select count(`id`) from `robots`";
      return $this->getReadConnection()->fetchAll($sql);   // for select
   }

   public function updateSomething()
   {
      $sql = "update `robots` set ...";
      return $this->getWriteConnection()->execute($sql);   // for insert, update, delete
   }
}

but this is not good practice to do it this way, better utilize standard model functions

No, but you can use the default DI to access the db service:

public static function something() {
    $db = \Phalcon\Di::getDefault()->get('db');
    $n = $db->execute("SELECT COUNT(id) FROM robots");
}

It is a bad practice though, you might want to have multiple DIs in the future...

I see Thanks a lot!

First of all - your query is really so complex that you can write it in modelsManager / find / findFirst / using update/save/delete methods ?

It's something like iMessage between two users, I've no idea that if the find method of a Model can do:

$sql = "SELECT
                    `mails`.*,
                    `users`.`id` as display_id,
                    `users`.`name`,
                    `users`.`avatar`
                FROM `mails`
                INNER JOIN `users`ON
                    IF(`mails`.`to_user` = $user_id, `mails`.`from_user`, `mails`.`to_user`) = `users`.`id`
                WHERE `mails`.`is_delete` = 0 AND
                      (`mails`.`from_user` = $user_id OR `mails`.`to_user` = $user_id)
                ORDER BY `mails`.`in_date` ASC
        ";

First of all - your query is really so complex that you can write it in modelsManager / find / findFirst / using update/save/delete methods ?

how to write in modelsManager?

First of all - your query is really so complex that you can write it in modelsManager / find / findFirst / using update/save/delete methods ?

You can use Query Builder instead of simple find method

$robots = Robots::query()
    ->where("type = :type:")
    ->andWhere("year < 2000")
    ->bind(array("type" => "mechanical"))
    ->order("name")
    ->execute();

More info here https://docs.phalcon.io/en/latest/api/Phalcon_Mvc_Model_Criteria.html

I would wrote it like this:

$result = $modelsManager->createBuilder()
    ->columns(['mails.*','users.id as display_id','users.name','users.avatar'])
    ->from(['mails'=>'mails class'])
    ->innerJoin('users class','IF(mails.to_user = :userId:,mails.from_user, mails.to_user) = users.id)','users');
    ->where('mails.is_delete = 0 AND (mails.from_user = :userId: OR mails.to_user = :userId:)')
    ->orderBy('mails.in_date ASC')
    ->getQuery()
    ->execute(['userId'=>$userId]);

Not sure if this IF gonna work though. Maybe you need to rewrite it into case.

@David Robots::query() It's good way! Thanks!

@Jurigag That's amazing！

I would reccomend to create Repositories factory and repository for each class, like in symfony.