PHP QueryBuilder::expr方法代码示例

 /**
  * Check if given country code is valid
  * 
  * @param string $countryCode
  * @param int $cacheMinutes
  */
 public function isValidCountryCode($countryCode = null, $cacheMinutes = null)
 {
     $qb = new QueryBuilder();
     $qb->select($qb->expr()->count("*", "count"))->from(Tbl::get('TBL_LOCATIONS'))->where($qb->expr(new Field('country'), $countryCode));
     $this->query->exec($qb->getSQL(), $cacheMinutes);
     $count = $this->query->fetchField('count');
     if ($count > 0) {
         return true;
     }
     return false;
 }

 /**
  * @param QueryBuilder $q
  * @param              $filter
  * @return array
  */
 protected function addCatchAllWhereClause(&$q, $filter)
 {
     $unique = $this->generateRandomParameterName();
     //ensure that the string has a unique parameter identifier
     $string = $filter->strict ? $filter->string : "%{$filter->string}%";
     $expr = $q->expr()->orX($q->expr()->like('f.label', ':' . $unique), $q->expr()->like('f.alias', ':' . $unique));
     if ($filter->not) {
         $expr = $q->expr()->not($expr);
     }
     return array($expr, array("{$unique}" => $string));
 }

 public static function getHostByName($hostName, $tryToAutoCreateHost = true, $cacheMinutes = null)
 {
     $sql = MySqlDbManager::getQueryObject();
     $qb = new QueryBuilder();
     $qb->select(new Field('*'))->from(Tbl::get('TBL_HOSTS', 'Host'))->where($qb->expr()->equal(new Field('host'), $hostName));
     $sql->exec($qb->getSQL(), $cacheMinutes);
     if ($sql->countRecords()) {
         $data = $sql->fetchRecord();
         $host = new Host();
         Host::setData($data, $host);
         return $host;
     } else {
         $originalHostName = $hostName;
         $explodedArray = explode(".", $hostName, 2);
         $parentHostName = array_pop($explodedArray);
         $wildcardHostName = "*." . $parentHostName;
         $qb = new QueryBuilder();
         $qb->select(new Field('*'))->from(Tbl::get('TBL_HOSTS', 'Host'))->where($qb->expr()->equal(new Field('host'), $wildcardHostName));
         $sql->exec($qb->getSQL(), $cacheMinutes);
         if ($sql->countRecords()) {
             $data = $sql->fetchRecord();
             $data['host'] = $originalHostName;
             $data['wildcardOf'] = $parentHostName;
             $host = new Host();
             Host::setData($data, $host);
             return $host;
         } elseif ($tryToAutoCreateHost) {
             $host = new Host();
             $host->host = $hostName;
             self::addHost($host);
             return self::getHostByName($hostName, false, $cacheMinutes);
         }
         throw new RuntimeException("There is no such host (" . $originalHostName . ")");
     }
 }

 /**
  * Check validity of username, password and other auth factors
  * 
  * @param string $username
  * @param string $password
  * @param array $additionalCredentials
  * @param boolean $writeCookie
  * @throws UserAuthFailedException
  * @return User
  */
 public function checkCredentials($username, $password, $additionalCredentials = array(), $writeCookie = false)
 {
     $qb = new QueryBuilder();
     $qb->select(new Field('id'), new Field('password'), new Field('salt'))->from(Tbl::get('TBL_USERS', 'UserManager'))->where($qb->expr()->equal(new Field('login'), $username));
     $this->query->exec($qb->getSQL());
     if ($this->query->countRecords() == 1) {
         $userData = $this->query->fetchRecord();
         $hashToCheck = static::getUserPasswordHash($password, $userData['salt']);
         if ($userData['password'] === $hashToCheck) {
             $usr = $this->doLogin($userData['id'], $additionalCredentials, $writeCookie);
             try {
                 $hookParams = array("user" => $usr, "additionalCredentials" => $additionalCredentials);
                 HookManager::callHook("UserAuthSuccess", $hookParams);
             } catch (UserAuthFailedException $e) {
                 $this->doLogout();
                 throw $e;
             }
             return $usr;
         }
     }
     // Failed login nothing returned from above code
     $hookParams = array("username" => $username, "password" => $password, "additionalCredentials" => $additionalCredentials);
     HookManager::callHook("UserAuthFail", $hookParams);
     throw new UserAuthFailedException("Incorrect login/password combination");
 }

 public function getLastId()
 {
     $qb = new QueryBuilder();
     $qb->select($qb->expr()->max(new Field('id'), 'lastId'))->from(Tbl::get('TBL_CHAT_MESSAGES'));
     $lastId = $this->query->exec($qb->getSQL())->fetchField('lastId');
     return empty($lastId) ? 0 : $lastId;
 }

 public function getEventsLastId()
 {
     $qb = new QueryBuilder();
     $qb->select($qb->expr()->max(new Field('id'), 'maxId'))->from(Tbl::get('TBL_COMET_EVENTS'));
     $maxId = $this->query->exec($qb->getSQL())->fetchField('maxId');
     if (empty($maxId)) {
         $maxId = 0;
     }
     return $maxId;
 }

 /**
  * Is remote IP blocked by country
  * 
  * @return boolean
  */
 private function isBlockedByCountry($cacheMinutes = null)
 {
     $myLocation = Reg::get(ConfigManager::getConfig('GeoIP', 'GeoIP')->Objects->GeoIP)->getLocation();
     if (empty($myLocation)) {
         return false;
     }
     $countryCode = $myLocation->country;
     if (empty($countryCode)) {
         return false;
     }
     $qb = new QueryBuilder();
     $qb->select($qb->expr()->count('*', 'count'))->from(Tbl::get('TBL_SECURITY_BLACKLISTED_COUNTRIES'))->where($qb->expr()->equal(new Field('country'), $countryCode));
     $this->query->exec($qb->getSQL(), $cacheMinutes);
     $count = $this->query->fetchField('count');
     if ($count > 0) {
         return true;
     }
     return false;
 }

 /**
  * @param QueryBuilder $q
  * @param              $filter
  *
  * @return array
  */
 protected function addSearchCommandWhereClause(&$q, $filter)
 {
     $command = $field = $filter->command;
     $unique = $this->generateRandomParameterName();
     $expr = false;
     switch ($command) {
         case $this->translator->trans('mautic.core.searchcommand.ispublished'):
             $expr = $q->expr()->eq('c.isPublished', ":{$unique}");
             $string = true;
             break;
         case $this->translator->trans('mautic.core.searchcommand.isunpublished'):
             $expr = $q->expr()->eq('c.isPublished', ":{$unique}");
             $string = false;
             break;
     }
     if ($expr && $filter->not) {
         $expr = $q->expr()->not($expr);
     }
     return [$expr, ["{$unique}" => $string]];
 }

 public function fillUsersGps($userId, $leafId)
 {
     $qb = new QueryBuilder();
     $qb->delete(Tbl::get('TBL_USERS_GPS'))->where($qb->expr()->equal(new Field('user_id'), $userId));
     $this->query->exec($qb->getSQL());
     $gpsTree = $this->getNodeTree($leafId);
     foreach ($gpsTree as $treeNode) {
         $qb = new QueryBuilder();
         $qb->insert(Tbl::get('TBL_USERS_GPS'))->values(array('user_id' => $userId, 'node_id' => $treeNode["node_id"]));
         $this->query->exec($qb->getSQL());
     }
 }

 public static function setControllerTemplateByHost(Host $host, $controller, $template)
 {
     $sql = MySqlDbManager::getQueryObject();
     $qb = new QueryBuilder();
     if (!empty($controller) or !empty($template)) {
         $qb->insert(Tbl::get('TBL_HOST_CONTROLLER_TEMPLATE'))->values(array('host_id' => $host->id, 'controller' => $controller, 'template' => $template))->onDuplicateKeyUpdate()->set(new Field('controller'), $controller)->set(new Field('template'), $template);
     } else {
         $qb->delete(Tbl::get('TBL_HOST_CONTROLLER_TEMPLATE'))->where($qb->expr()->equal(new Field('host_id'), $host->id));
     }
     $sql->exec($qb->getSQL());
     return $sql->affected();
 }

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

 public function deleteGroup(TextsGroup $group)
 {
     if (empty($group->id)) {
         throw new InvalidArgumentException("Group ID have to be specified");
     }
     if (!is_numeric($group->id)) {
         throw new InvalidArgumentException("Group ID have to be integer");
     }
     $qb = new QueryBuilder();
     $qb->delete(Tbl::get('TBL_TEXTS_GROUPS'))->where($qb->expr()->equal(new Field("id"), $group->id));
     $this->query->exec($qb->getSQL());
     return $this->query->affected();
 }

 public function deleteAllAliasesForTextValue(TextValue $textValue)
 {
     if (empty($textValue->id)) {
         throw new InvalidArgumentException("Text Value ID have to be specified");
     }
     if (!is_numeric($textValue->id)) {
         throw new InvalidArgumentException("Text Value ID have to be integer");
     }
     $qb = new QueryBuilder();
     $qb->delete(Tbl::get('TBL_TEXTS_ALIASES'))->where($qb->expr()->equal(new Field("value_id"), $textValue->id));
     $this->query->exec($qb->getSQL());
     return $this->query->affected();
 }

 /**
  * @param QueryBuilder $q
  * @param              $filter
  * @return array
  */
 protected function addSearchCommandWhereClause(&$q, $filter)
 {
     $command = $filter->command;
     $unique = $this->generateRandomParameterName();
     $returnParameter = true;
     //returning a parameter that is not used will lead to a Doctrine error
     $expr = false;
     switch ($command) {
         case $this->translator->trans('mautic.core.searchcommand.ispublished'):
             $expr = $q->expr()->eq("e.isPublished", ":{$unique}");
             $forceParameters = array($unique => true);
             break;
         case $this->translator->trans('mautic.core.searchcommand.isunpublished'):
             $expr = $q->expr()->eq("e.isPublished", ":{$unique}");
             $forceParameters = array($unique => true);
             break;
         case $this->translator->trans('mautic.core.searchcommand.isuncategorized'):
             $expr = $q->expr()->orX($q->expr()->isNull('e.category'), $q->expr()->eq('e.category', $q->expr()->literal('')));
             $returnParameter = false;
             break;
         case $this->translator->trans('mautic.core.searchcommand.ismine'):
             $expr = $q->expr()->eq("IDENTITY(e.createdBy)", $this->currentUser->getId());
             $returnParameter = false;
             break;
         case $this->translator->trans('mautic.core.searchcommand.category'):
             $expr = $q->expr()->like('e.alias', ":{$unique}");
             $filter->strict = true;
             break;
         case $this->translator->trans('mautic.core.searchcommand.lang'):
             $langUnique = $this->generateRandomParameterName();
             $langValue = $filter->string . "_%";
             $forceParameters = array($langUnique => $langValue, $unique => $filter->string);
             $expr = $q->expr()->orX($q->expr()->eq('e.language', ":{$unique}"), $q->expr()->like('e.language', ":{$langUnique}"));
             break;
     }
     if ($expr && $filter->not) {
         $expr = $q->expr()->not($expr);
     }
     if (!empty($forceParameters)) {
         $parameters = $forceParameters;
     } elseif (!$returnParameter) {
         $parameters = array();
     } else {
         $string = $filter->strict ? $filter->string : "%{$filter->string}%";
         $parameters = array("{$unique}" => $string);
     }
     return array($expr, $parameters);
 }

 /**
  * Get language by short name
  *
  * @param string $shortName
  * @throws EmptyArgumentException, InvalidArgumentException
  * @return Language object
  */
 public static function getLanguage($shortName, $cacheMinutes = null)
 {
     if (empty($shortName)) {
         throw new EmptyArgumentException("Empty shortName argument.");
     }
     $sql = MySqlDbManager::getQueryObject();
     $qb = new QueryBuilder();
     $qb->select(new Field('*'))->from(Tbl::get('TBL_LANGUAGES'))->where($qb->expr()->equal(new Field('name'), $shortName));
     $sql->exec($qb->getSQL(), $cacheMinutes);
     if ($sql->countRecords()) {
         $l = new Language();
         $data = $sql->fetchRecord();
         static::setData($data, $l);
         return $l;
     }
     throw new InvalidArgumentException("There is no language with such short name (" . $shortName . ")");
 }