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.