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

How to execute a SQL string in simple way?

In the Model, how to execute a SQL string in a simple and best way (with the minimal system resources) ? For example:

class Robots Extends Model
{
    //...

    public static function findSomething()
    {
        $sql = "Select count(`id`) from `robots`";

        //Here, How to execute the $sql string, and return a total number of robots
    }
}


9.3k
Accepted
answer
edited Mar '16

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



31.3k

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

edited Mar '16

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...



31.3k

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 ?



31.3k
edited Mar '16

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 ?



31.3k

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 ?

edited Mar '16

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

edited Mar '16

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.



31.3k

@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.