public function getAction($version, $uuid)
{
try {
$query = new Query('
SELECT
uuid,
created_at,
updated_at,
name,
mime_type,
is_deleted,
is_image,
is_ready,
size
FROM Depot\\Models\\File
WHERE uuid = :uuid:
LIMIT 1
', $this->getDI());
$query->setUniqueRow(true);
$file = $query->execute(array('uuid' => $uuid));
if (!$file) {
throw new Exception('File not found', 404);
}
$this->response->setJsonContent(array('status' => 'OK', 'result' => $file));
$this->response->send();
} catch (\Exception $exception) {
$this->throwException($exception);
}
}
public function getServerTime()
{
// A raw SQL statement
$query = new Query('SELECT now() FROM NiuUsrInfo limit 1', $this->getDI());
// Execute the query
$result = $query->execute();
return $result[0]->toArray()[0];
}
/**
* @param array $bindParams
* @param array $bindTypes
* @return mixed
*/
public function execute($bindParams = null, $bindTypes = null)
{
$params = array_merge($this->_bindParams ?: [], $bindParams ?: []);
$params = array_filter($params, 'is_scalar');
ksort($params);
if (false !== $this->_cacheOptions) {
$this->cache(array_replace_recursive(['key' => sha1($this->_phql . print_r($params, true)), 'lifetime' => 7200], (array) $this->_cacheOptions));
} else {
$this->cache(null);
}
return parent::execute($bindParams, $bindTypes);
}
public function register(&$user_id, $params, $type = 1)
{
$flag = false;
try {
$this->di['db']->begin();
$ubm = new DtbUserBasic();
$invite_code = $this->getOneinvitecode();
$ubm->account_type = 0;
$ubm->nickname = $params['nickname'];
$ubm->mobile = $params['mobile'];
$ubm->email = $params['email'];
$ubm->password = $params['password'];
$ubm->avatar_url = "default_avatar.png";
$ubm->create_ts = time();
$ubm->invite_code = $invite_code;
$ubm->reg_form = $params['reg_form'];
$sql = "update DtbInviteCode set is_use=1 where invite_code={$invite_code} and is_use=0";
$query = new Phalcon\Mvc\Model\Query($sql, $this->getDI());
$res = $query->execute();
//update invite_code status
if ($ubm->save() == false) {
foreach ($ubm->getMessages() as $message) {
echo $message;
}
$this->di['db']->rollback();
return $flag;
} elseif (!$res) {
$this->di['db']->rollback();
return $flag;
} else {
$action_type = $this->di['config']->log_user->register;
$log_ts = time();
$sql = "insert into DtbLogUser (user_id,action_type,log_ts) values('{$ubm->user_id}','{$action_type}','{$log_ts}' )";
$query = new Phalcon\Mvc\Model\Query($sql, $this->getDI());
$res1 = $query->execute();
if (!$res1) {
$this->di['db']->rollback();
} else {
$flag = true;
$this->di['db']->commit();
}
return $flag;
}
} catch (Exception $ex) {
$this->di['db']->rollback();
return $flag;
}
}
/**
* Query the records on which the UPDATE/DELETE operation well be done
*
* @param \Phalcon\Mvc\Model $model
* @param array $intermediate
* @param array $bindParams
* @param array $bindTypes
* @return \Phalcon\Mvc\Model\ResultsetInterface
*/
protected function _getRelatedRecords(Model $model, array $intermediate, array $bindParams, array $bindTypes)
{
$selectColumns = array(array(array('type' => 'object', 'model' => get_class($model), 'column' => $model->getSource())));
//Instead of creating a PHQL string statement, we manually create the IR representation
$selectIr = array('columns' => $selectColumns, 'models' => $intermediate['models'], 'tables' => $intermediate['tables']);
//Check if a WHERE clause was especified
if (isset($intermediate['where']) === true) {
$selectIr['where'] = $intermediate['where'];
}
if (isset($intermediate['limit']) === true) {
$selectIr['limit'] = $intermediate['limit'];
}
//We create another Phalcon\Mvc\Model\Query to get the related records
$query = new Query();
$query->setDi($this->_dependencyInjector);
$query->setType(309);
$query->setIntermediate($selectIr);
return $query->execute($bindParams, $bindTypes);
}
/**
* Returns the query built
*
* @return \Phalcon\Mvc\Model\Query
*/
public function getQuery()
{
//Process the PHQL
$phql = $this->getPhql();
$query = new Query($phql, $this->_dependencyInjector);
//Set default bind params
$bindParams = $this->_bindParams;
if (is_array($bindParams) === true) {
$query->setBindParams($bindParams);
}
//Set default bind types
$bindTypes = $this->_bindTypes;
if (is_array($bindTypes) === true) {
$query->setBindTypes($bindTypes);
}
return $query;
}
public function testDeleteParsing()
{
require 'unit-tests/config.db.php';
if (empty($configMysql)) {
$this->markTestSkipped('Test skipped');
return;
}
$di = $this->_getDI();
$expected = array('tables' => array('robots'), 'models' => array('Robots'));
$query = new Query('DELETE FROM Robots');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('DELETE FROM Robots AS r WHERE r.id > 100');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('DELETE FROM Robots r WHERE r.id > 100');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('delete from Robots as r where r.id > 100');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'limit' => array('number' => array('type' => 'literal', 'value' => '10')));
$query = new Query('DELETE FROM Robots r LIMIT 10');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')), 'limit' => array('number' => array('type' => 'literal', 'value' => '10')));
$query = new Query('DELETE FROM Robots r WHERE r.id > 100 LIMIT 10');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
// Issue 1011
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')), 'limit' => array('number' => array('type' => 'placeholder', 'value' => ':limit')));
$query = new Query('DELETE FROM Robots r WHERE r.id > 100 LIMIT :limit:');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$query = new Query('DELETE FROM Robots r WHERE r.id > 100 LIMIT :limit:');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
}
public function testDeleteParsing()
{
$di = $this->_getDI();
$expected = array('tables' => array('robots'), 'models' => array('Robots'));
$query = new Query('DELETE FROM Robots');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('DELETE FROM Robots AS r WHERE r.id > 100');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('DELETE FROM Robots r WHERE r.id > 100');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('delete from Robots as r where r.id > 100');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'limit' => array('number' => '10'));
$query = new Query('DELETE FROM Robots r LIMIT 10');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
$expected = array('tables' => array(array('robots', NULL, 'r')), 'models' => array('Robots'), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')), 'limit' => array('number' => '10'));
$query = new Query('DELETE FROM Robots r WHERE r.id > 100 LIMIT 10');
$query->setDI($di);
$this->assertEquals($query->parse(), $expected);
}
public function getQuery()
{
if (!$this->_Phql) {
throw new \Exception('please format sql after call this method');
}
$where = $this->getWhere();
if (!empty($where)) {
$this->_Phql .= ' WHERE ' . $where;
}
$limit = $this->getLimit();
if (!empty($limit)) {
$this->_Phql .= ' LIMIT ' . $limit;
}
// or new ModelQuery($this->_Phql) call all
$query = new \Phalcon\Mvc\Model\Query($this->_Phql, \Phalcon\Di::getDefault());
if (!empty($this->_bindParams)) {
$query->setBindParams($this->_bindParams);
}
if (!empty($this->_bindTypes)) {
$query->setBindTypes($this->_bindTypes);
}
$query->setType($this->_type);
$query->setDI(\Phalcon\Di::getDefault());
return $query;
}
public function testDeleteParsing()
{
$this->specify("DELETE PHQL queries don't work as expected", function () {
$expected = array('tables' => array('robots'), 'models' => array(Robots::class));
$query = new Query('DELETE FROM ' . Robots::class);
$query->setDI($this->di);
expect($query->parse())->equals($expected);
$expected = array('tables' => array(array('robots', null, 'r')), 'models' => array(Robots::class), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('DELETE FROM ' . Robots::class . ' AS r WHERE r.id > 100');
$query->setDI($this->di);
expect($query->parse())->equals($expected);
$expected = array('tables' => array(array('robots', null, 'r')), 'models' => array(Robots::class), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('DELETE FROM ' . Robots::class . ' r WHERE r.id > 100');
$query->setDI($this->di);
expect($query->parse())->equals($expected);
$expected = array('tables' => array(array('robots', null, 'r')), 'models' => array(Robots::class), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')));
$query = new Query('DELETE FROM ' . Robots::class . ' as r WHERE r.id > 100');
$query->setDI($this->di);
expect($query->parse())->equals($expected);
$expected = array('tables' => array(array('robots', null, 'r')), 'models' => array(Robots::class), 'limit' => array('number' => array('type' => 'literal', 'value' => '10')));
$query = new Query('DELETE FROM ' . Robots::class . ' r LIMIT 10');
$query->setDI($this->di);
expect($query->parse())->equals($expected);
$expected = array('tables' => array(array('robots', null, 'r')), 'models' => array(Robots::class), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')), 'limit' => array('number' => array('type' => 'literal', 'value' => '10')));
$query = new Query('DELETE FROM ' . Robots::class . ' r WHERE r.id > 100 LIMIT 10');
$query->setDI($this->di);
expect($query->parse())->equals($expected);
// Issue 1011
$expected = array('tables' => array(array('robots', null, 'r')), 'models' => array(Robots::class), 'where' => array('type' => 'binary-op', 'op' => '>', 'left' => array('type' => 'qualified', 'domain' => 'r', 'name' => 'id', 'balias' => 'id'), 'right' => array('type' => 'literal', 'value' => '100')), 'limit' => array('number' => array('type' => 'placeholder', 'value' => ':limit')));
$query = new Query('DELETE FROM ' . Robots::class . ' r WHERE r.id > 100 LIMIT :limit:');
$query->setDI($this->di);
expect($query->parse())->equals($expected);
});
}
/**
* Executes a parsed PHQL statement
*
* @param array $bindParams
* @param array $bindTypes
* @return mixed
*/
public function execute($bindParams = null, $bindTypes = null)
{
$key = $this->_createKey($bindParams);
$this->cache(["key" => $key, "lifetime" => 300]);
return parent::execute($bindParams, $bindTypes);
}
/**
* Return a list of all posts within a given radius of the lat / long
*/
public function all($latitude, $longitude, $radius, $user)
{
$query = new Query('select Posts.id, Posts.users_id, Posts.latitude, Posts.longitude, Posts.content, Posts.created, ( 3959 * acos( cos( radians(:lat:) ) * cos( radians( Posts.latitude ) ) * cos( radians(Posts.longitude) - radians(:lng:)) + sin(radians(:lat:)) * sin( radians(Posts.latitude)))) AS miles from Posts having miles <= :radius:', $this->getDI());
$posts = $query->execute(array('lat' => $latitude, 'lng' => $longitude, 'radius' => $radius));
return array(true, $posts->toArray());
}
/**
* Creates a \Phalcon\Mvc\Model\Query and execute it
*
* @param string $phql
* @param array|null $placeholders
* @return \Phalcon\Mvc\Model\QueryInterface
* @throws Exception
*/
public function executeQuery($phql, $placeholders = null)
{
if (is_string($phql) === false || is_array($placeholders) === false && is_null($placeholders) === false) {
throw new Exception('Invalid parameter type.');
}
if (is_object($this->_dependencyInjector) === false) {
throw new Exception('A dependency injection object is required to access ORM services');
}
//Create a query
$query = new Query($phql);
$query->setDi($this->_dependencyInjector);
$this->_lastQuery = $query;
//Execute the query
return $query->execute($placeholders);
}
