We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 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(
                $this->_addLike($this->request->getPost('f_name')),
                $this->_addLike($this->request->getPost('s_name')),
                $this->_addLike($this->request->getPost('sex')),
                $this->_addLike($this->request->getPost('city'))
            ));
        }
        else
        {
            $users = Users::findByRawSql('u.id > ?', array(0));
        }

        $this->view->setVar('users',$users);

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?



51.2k

You are using Transact-SQL or Oracle ?