We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Using Raw SQL

Hi everyone.

I want to use Raw SQL, I did it:

public static function findByRawSql($conditions, $params = null)
        // Выражение на чистом SQL
        $sql = "SELECT u.id, u.avatar, u.city, u.f_name, u.s_name, u.notes, u.username, SUM(b.summ) AS summ
FROM users AS u LEFT JOIN balance AS b ON b.user_id = u.id
WHERE $conditions GROUP BY u.id ORDER BY summ DESC, u.city DESC, u.avatar DESC";

        // Модель
        $user = new Users();

        // Выполнение запроса
        return new Resultset(null, $user, $user->getReadConnection()->query($sql, $params));

And Controller:

if($this->request->isPost() == true)
            $users = Users::findByRawSql("COALESCE(u.f_name, '') LIKE ? AND COALESCE(u.s_name, '') LIKE ? AND COALESCE(u.sex, '') LIKE ? AND COALESCE(u.city, '') LIKE ?", array(
            $users = Users::findByRawSql('u.id > ?', array(0));


But COALESCE doesn't work, probably shielding query somewhere along the path function converts it to a string. Sorry for my English, I hope you understand.

I use COALESCE, because these fields is NULL, and I want to get all fields where there is NULL.

you already wrote and tested this SQL directly in the database?


You are using Transact-SQL or Oracle ?