PHP QueryBuilder::andWhere方法代码示例

 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);
 }