protected static function queryString($lang_id = null, $host_id = null, $module = null, $page = null, $cacheMinutes = null) { $qb = new QueryBuilder(); $qb->select(new Field('title'), new Field('meta_keywords'), new Field('meta_description'))->from(Tbl::get('TBL_PAGE_INFO')); if ($lang_id === null) { $qb->andWhere($qb->expr()->isNull(new Field('lang_id'))); } else { $qb->andWhere($qb->expr()->equal(new Field('lang_id'), $lang_id)); } if ($host_id === null) { $qb->andWhere($qb->expr()->isNull(new Field('host_id'))); } else { $qb->andWhere($qb->expr()->equal(new Field('host_id'), $host_id)); } if ($module === null) { $qb->andWhere($qb->expr()->isNull(new Field('module'))); } else { $qb->andWhere($qb->expr()->equal(new Field('module'), $module)); } if ($page === null) { $qb->andWhere($qb->expr()->isNull(new Field('page'))); } else { $qb->andWhere($qb->expr()->equal(new Field('page'), $page)); } return $qb->getSQL(); }
/** * Function returns array of nodes by given node's latitude and longitude. * $distance is a max distance from center point to get nodes. * $bounding_distance is a measurement if the square in which we seek for the nodes. * This is done for performance for not to calculate distances for all nodes in DB. * * @param array $node array("lat" => integer, "lng" => integer, optional("id" => nodId)) * @param Integer $distance * @param Integer $bounding_distance * @throws InvalidIntegerArgumentException * @return Array|boolean */ public function getNearestNodes($node, $distance = 10, $bounding_distance = 3) { if (empty($node)) { throw new InvalidIntegerArgumentException("Node is mepty"); } $lat = $node["lat"]; $long = $node["lng"]; $qb = new QueryBuilder(); $qb->select('*', new Field($qb->expr()->prod($qb->expr()->prod($qb->expr()->quot($qb->expr()->prod(new Func('ACOS', $qb->expr()->sum($qb->expr()->prod(new Func('SIN', $qb->expr()->quot($qb->expr()->prod($lat, new Func('PI')), 180)), new Func('SIN', $qb->expr()->quot($qb->expr()->prod(new Field('lat'), new Func('PI')), 180))), $qb->expr()->prod($qb->expr()->prod(new Func('COS', $qb->expr()->quot($qb->expr()->prod($lat, new Func('PI')), 180)), new Func('COS', $qb->expr()->quot($qb->expr()->prod(new Field('lat'), new Func('PI')), 180))), new Func('COS', $qb->expr()->quot($qb->expr()->prod($qb->expr()->diff($long, new Field('lng')), new Func('PI')), 180))))), 180), new Func('PI')), 60), 1.1515), null, 'distance'))->from(Tbl::get('TBL_TREE'), 'tree')->leftJoin(Tbl::get('TBL_ZIP_CODES'), 'zips', $qb->expr()->equal(new Field('gps_id', 'zips'), new Field('id', 'tree')))->where($qb->expr()->between(new Field('lat'), $qb->expr()->diff($lat, $bounding_distance), $qb->expr()->sum($lat, $bounding_distance)))->andWhere($qb->expr()->between(new Field('lng'), $qb->expr()->diff($long, $bounding_distance), $qb->expr()->sum($long, $bounding_distance)))->andWhere($qb->expr()->in(new Field('type_id'), array(30, 35, 40)))->having($qb->expr()->less(new Field('distance'), $distance))->orderBy(new Field('distance'), "ASC"); if (!empty($node["id"])) { $qb->andWhere($qb->expr()->notEqual(new Field('id'), $node["id"])); } $this->query->exec($qb->getSQL()); if ($this->query->countRecords() > 0) { return $this->query->fetchRecords(); } return false; }
/** * Reimplementing findBy features… with extra things. * * * key => array('<=', $value) * * key => array('<', $value) * * key => array('>=', $value) * * key => array('>', $value) * * key => array('BETWEEN', $value, $value) * * key => array('LIKE', $value) * * key => array('NOT IN', $array) * * key => 'NOT NULL' * * You even can filter with node fields, examples: * * * `node.published => true` * * `node.nodeName => 'page1'` * * @param array $criteria * @param QueryBuilder $qb * @param boolean $joinedNode * @param boolean $joinedNodeType */ protected function filterByCriteria(&$criteria, &$qb, &$joinedNode = false, &$joinedNodeType = false) { /* * Reimplementing findBy features… */ foreach ($criteria as $key => $value) { if ($key == "tags" || $key == "tagExclusive") { continue; } /* * compute prefix for * filtering node relation fields */ $prefix = 'ns.'; // Dots are forbidden in field definitions $baseKey = str_replace('.', '_', $key); if (false !== strpos($key, 'node.nodeType.')) { if (!$joinedNode) { $qb->innerJoin('ns.node', 'n'); $joinedNode = true; } if (!$joinedNodeType) { $qb->innerJoin('n.nodeType', 'nt'); $joinedNodeType = true; } $prefix = 'nt.'; $key = str_replace('node.nodeType.', '', $key); } if (false !== strpos($key, 'node.')) { if (!$joinedNode) { $qb->innerJoin('ns.node', 'n'); $joinedNode = true; } $prefix = 'n.'; $key = str_replace('node.', '', $key); } $qb->andWhere($this->buildComparison($value, $prefix, $key, $baseKey, $qb)); } }
public function whereCurrentYear(QueryBuilder $qb) { $qb->andWhere('a.date BETWEEN :start AND :end')->setParameter('start', new \Datetime(date('Y') . '-01-01'))->setParameter('end', new \Datetime(date('Y') . '-12-31')); }
/** * @param integer $inviterUserId * @param integer $invitedUserId * @deprecated Sessions log insertd by mysql TRIGGER chat_sessions_log */ protected function insertSessionLog($inviterUserId, $invitedUserId) { if ($inviterUserId > $invitedUserId) { $userId1 = $inviterUserId; $userId2 = $invitedUserId; } else { $userId1 = $invitedUserId; $userId2 = $inviterUserId; } $qb = new QueryBuilder(); $qb->select(new Field('id'))->from(Tbl::get('TBL_CHAT_SESSIONS_LOG')); $andClause1 = new Andx(); $andClause1->add($qb->expr()->equal(new Field('user1_id', Tbl::get('TBL_CHAT_SESSIONS_LOG')), $userId1)); $andClause1->add($qb->expr()->equal(new Field('user2_id', Tbl::get('TBL_CHAT_SESSIONS_LOG')), $userId2)); $andClause2 = new Andx(); $andClause2->add($qb->expr()->equal(new Field('user1_id', Tbl::get('TBL_CHAT_SESSIONS_LOG')), $userId2)); $andClause2->add($qb->expr()->equal(new Field('user2_id', Tbl::get('TBL_CHAT_SESSIONS_LOG')), $userId1)); $orClause = new Orx(); $orClause->add($andClause1); $orClause->add($andClause2); $qb->andWhere($orClause); $this->query->exec($qb->getSQL()); $qb = new QueryBuilder(); if ($this->query->countRecords()) { $sesionId = $this->query->fetchField("id"); $qb->update(Tbl::get('TBL_CHAT_SESSIONS_LOG'))->set(new Field('datetime'), date(DEFAULT_DATETIME_FORMAT))->where($qb->expr()->equal(new Field('id'), $sesionId)); } else { $qb->insert(Tbl::get('TBL_CHAT_SESSIONS_LOG'))->values(array('user1_id' => $userId1, 'user2_id' => $userId2, 'datetime' => date(DEFAULT_DATETIME_FORMAT))); } $this->query->exec($qb->getSQL()); return $this->query->affected(); }
/** * Update DB Config * @param ConfigDB $oldDBCOnfig * @param ConfigDB $newDBConfig * @throws InvalidArgumentException */ public static function updateConfigValue(ConfigDB $oldDBCOnfig, ConfigDB $newDBConfig) { if (empty($oldDBCOnfig) || empty($newDBConfig)) { throw new InvalidArgumentException("new or old DB config object is empty"); } if (!isset($oldDBCOnfig->location) or empty($oldDBCOnfig->location)) { throw new InvalidArgumentException("odl Location of config should be non empty array"); } if (!isset($newDBConfig->location) or empty($newDBConfig->location)) { throw new InvalidArgumentException("New Location of config should be non empty array"); } if (!isset($oldDBCOnfig->name) or empty($oldDBCOnfig->name)) { throw new InvalidArgumentException("Old Name of config should be specified"); } if (!isset($newDBConfig->name) or empty($newDBConfig->name)) { throw new InvalidArgumentException("New Name of config should be specified"); } if (!isset($newDBConfig->value)) { throw new InvalidArgumentException("Value of new config should be specified"); } $odlHostLangid = null; if (!empty($oldDBCOnfig->host) && !empty($oldDBCOnfig->language)) { $odlHostLangid = HostLanguageManager::getHostLanguageId($oldDBCOnfig->host, $oldDBCOnfig->language); } $newHostLangId = null; if (!empty($newDBConfig->host) && !empty($newDBConfig->language)) { $newHostLangId = HostLanguageManager::getHostLanguageId($newDBConfig->host, $newDBConfig->language); } $sql = MySqlDbManager::getQueryObject(); $qb = new QueryBuilder(); $qb->update(Tbl::get("TBL_CONFIGS"))->set(new Field("location"), implode(":", $newDBConfig->location))->set(new Field("name"), $newDBConfig->name)->set(new Field("value"), $newDBConfig->value)->where($qb->expr()->equal(new Field("location"), implode(":", $oldDBCOnfig->location)))->andWhere($qb->expr()->equal(new Field("name"), $oldDBCOnfig->name)); if ($newHostLangId !== null) { $qb->set(new Field("host_lang_id"), $newHostLangId); } else { $qb->set(new Field("host_lang_id"), new Literal("null")); } if ($odlHostLangid !== null) { $qb->andWhere($qb->expr()->equal(new Field("host_lang_id"), $odlHostLangid)); } else { $qb->andWhere($qb->expr()->isNull(new Field("host_lang_id"))); } $sql->exec($qb->getSQL()); }
/** * Reimplementing findBy features… with extra things * * * key => array('<=', $value) * * key => array('<', $value) * * key => array('>=', $value) * * key => array('>', $value) * * key => array('BETWEEN', $value, $value) * * key => array('LIKE', $value) * * key => array('NOT IN', $array) * * key => 'NOT NULL' * * You can filter with translations relation, examples: * * * `translation => $object` * * `translation.locale => 'fr_FR'` * * @param array $criteria * @param QueryBuilder $qb */ protected function filterByCriteria(&$criteria, &$qb) { /* * Reimplementing findBy features… */ foreach ($criteria as $key => $value) { if ($key == "folders" || $key == "folderExclusive") { continue; } /* * compute prefix for * filtering node, and sources relation fields */ $prefix = 'd.'; // Dots are forbidden in field definitions $baseKey = str_replace('.', '_', $key); /* * Search in translation fields */ if (false !== strpos($key, 'translation.')) { $prefix = 't.'; $key = str_replace('translation.', '', $key); } /* * Search in translation fields */ if (false !== strpos($key, 'documentTranslations.')) { $prefix = 'dt.'; $key = str_replace('documentTranslations.', '', $key); } if ($key == 'translation') { $prefix = 'dt.'; } $qb->andWhere($this->buildComparison($value, $prefix, $key, $baseKey, $qb)); } }
/** * Direct bind one single parameter without preparation. * * @param string $key * @param mixed $value * @param QueryBuilder $qb * @param string $alias * * @return QueryBuilder */ protected function singleDirectComparison($key, &$value, &$qb, $alias) { if (is_object($value) && $value instanceof PersistableInterface) { $res = $qb->expr()->eq($alias . '.' . $key, $value->getId()); } elseif (is_array($value)) { /* * array * * ['<=', $value] * ['<', $value] * ['>=', $value] * ['>', $value] * ['BETWEEN', $value, $value] * ['LIKE', $value] * in [$value, $value] */ if (count($value) > 1) { switch ($value[0]) { case '!=': # neq $res = $qb->expr()->neq($alias . '.' . $key, $value[1]); break; case '<=': # lte $res = $qb->expr()->lte($alias . '.' . $key, $value[1]); break; case '<': # lt $res = $qb->expr()->lt($alias . '.' . $key, $value[1]); break; case '>=': # gte $res = $qb->expr()->gte($alias . '.' . $key, $value[1]); break; case '>': # gt $res = $qb->expr()->gt($alias . '.' . $key, $value[1]); break; case 'BETWEEN': $res = $qb->expr()->between($alias . '.' . $key, $value[1], $value[2]); break; case 'LIKE': $res = $qb->expr()->like($alias . '.' . $key, $qb->expr()->literal($value[1])); break; default: $res = $qb->expr()->in($alias . '.' . $key, $value); break; } } else { $res = $qb->expr()->in($alias . '.' . $key, $value); } } elseif (is_array($value)) { $res = $qb->expr()->in($alias . '.' . $key, $value); } elseif (is_bool($value)) { $res = $qb->expr()->eq($alias . '.' . $key, (bool) $value); } else { $res = $qb->expr()->eq($alias . '.' . $key, $value); } $qb->andWhere($res); return $qb; }
public function deleteAnswers($profileId) { if (empty($profileId)) { throw new InvalidArgumentException("Given Profile Id is empty"); } $qb = new QueryBuilder(); $qb->delete(Tbl::get('TBL_PROFILE_SAVE'))->where($qb->expr()->equal(new Field('user_id'), $this->userId)); if (is_array($profileId)) { $qb->andWhere($qb->expr()->in(new Field('profile_id'), $profileId)); } else { $qb->andWhere($qb->expr()->equal(new Field('profile_id'), $profileId)); } $this->query->exec($qb->getSQL()); }
/** * Check if record exists * * @param array $pageInfo * @param Language $lang * @param Host $host * @param string $module * @param string $page * @return bool */ private static function exists($lang_id = null, $host_id = null, $module = null, $page = null, $cacheMinutes = null) { $sql = MySqlDbManager::getQueryObject(); $qb = new QueryBuilder(); $qb->select(new Field('id'))->from(Tbl::get('TBL_PAGE_INFO', 'PageInfo')); if ($lang_id === null) { $qb->andWhere($qb->expr()->isNull(new Field('lang_id'))); } else { $qb->andWhere($qb->expr()->equal(new Field('lang_id'), $lang_id)); } if ($host_id === null) { $qb->andWhere($qb->expr()->isNull(new Field('host_id'))); } else { $qb->andWhere($qb->expr()->equal(new Field('host_id'), $host_id)); } if ($module === null) { $qb->andWhere($qb->expr()->isNull(new Field('module'))); } else { $qb->andWhere($qb->expr()->equal(new Field('module'), $module)); } if ($page === null) { $qb->andWhere($qb->expr()->isNull(new Field('page'))); } else { $qb->andWhere($qb->expr()->equal(new Field('page'), $page)); } $sql->exec($qb->getSQL(), $cacheMinutes); if ($sql->countRecords()) { return $sql->fetchField("id"); } return false; }
public function changeConversationHasAttachmentStatus($uuid, $status, $userId = null) { if (empty($uuid) or !is_numeric($uuid)) { throw new InvalidIntegerArgumentException("\$uuid have to be non zero integer."); } if (!is_numeric($status) or !in_array($status, $this->getConstsArray("STATUS_HAS_ATTACHMENT"))) { throw new InvalidIntegerArgumentException("Invalid \$status specified"); } if ($userId !== null and (empty($userId) or !is_numeric($userId))) { throw new InvalidIntegerArgumentException("\$userId have to be non zero integer."); } $qb = new QueryBuilder(); $qb->update(Tbl::get('TBL_CONVERSATIONS'))->set(new Field('has_attachment'), $status)->where($qb->expr()->equal(new Field('uuid'), $uuid)); if ($userId !== null and !empty($userId) and is_numeric($userId)) { $qb->andWhere($qb->expr()->equal(new Field('user_id'), $userId)); } $this->query->exec($qb->getSQL()); $hookParams = array('type' => 'hasAttach', 'uuid' => $uuid, 'hasAttach' => $status); HookManager::callHook("ConversationUpdate", $hookParams); }