PHP Entity\Query类代码示例

function getList($blockId, $fields, $order, $filers)
{
    $entity_requests_data_class = connectToBlock($blockId);
    $main_query_requests = new Entity\Query($entity_requests_data_class);
    $main_query_requests->setSelect($fields);
    //$main_query_requests->setSelect(array('ID','UF_TITLE'));
    if (!empty($order)) {
        $main_query_requests->setOrder($order);
    }
    if (!empty($filers)) {
        $main_query_requests->setFilter($filers);
        /*$main_query_requests->setFilter(
              array(
                  'UF_NAME'=>'Александр',
              )
          );*/
    }
    $result_requests = $main_query_requests->exec();
    $result_requests = new CDBResult($result_requests);
    $requests = array();
    while ($row_requests = $result_requests->Fetch()) {
        $requests[] = $row_requests;
        //массив выбранных элементов
    }
    return $requests;
}

 /**
  * Executes the query
  *
  * @param string $filterField
  * @param mixed $filterFieldValue
  * @param string $method
  *
  * @return \Bitrix\Main\DB\MysqlResult
  */
 private function execute($filterField, $filterFieldValue, $method)
 {
     $queryBuilder = new Entity\Query(Model\VarsGroupTable::getEntity());
     $queryBuilder->setSelect(array('ID', 'NAME', 'CODE'))->setOrder(array('ID' => 'ASC'))->setFilter(array($filterField => $filterFieldValue));
     if ($method == 'findOneBy') {
         $queryBuilder->setLimit(1);
     }
     return $queryBuilder->exec();
 }

 public function validate($value, $primary, array $row, Entity\Field $field)
 {
     $query = new Entity\Query($this->reference->getEntity());
     $query->setFilter(array('=' . $this->reference->getName() => $value) + $this->filter);
     $query->setLimit(1);
     $result = $query->exec();
     if ($result->fetch()) {
         return true;
     }
     return new Entity\FieldError($field, $this->getErrorMessage($value, $field), self::NOT_EXISTS);
 }

 public function calculateEntityCount(DuplicateCriterion $criterion, array $options = null)
 {
     $entityTypeID = $this->getEntityTypeID();
     $enablePermissionCheck = $this->isPermissionCheckEnabled();
     $userID = $this->getUserID();
     $query = new Main\Entity\Query(DuplicateCommunicationMatchCodeTable::getEntity());
     $query->addSelect('QTY');
     $query->registerRuntimeField('', new Main\Entity\ExpressionField('QTY', 'COUNT(*)'));
     $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
     if ($enablePermissionCheck) {
         $permissionSql = $this->preparePermissionSql();
         if ($permissionSql === false) {
             //Access denied;
             return 0;
         }
         if (is_string($permissionSql) && $permissionSql !== '') {
             $query->addFilter('@ENTITY_ID', new Main\DB\SqlExpression($permissionSql));
         }
     }
     $matches = $criterion->getMatches();
     $type = isset($matches['TYPE']) ? $matches['TYPE'] : '';
     if ($type === '') {
         throw new Main\ArgumentException("Parameter 'TYPE' is required.", 'matches');
     }
     $value = isset($matches['VALUE']) ? $matches['VALUE'] : '';
     if ($type === '') {
         throw new Main\ArgumentException("Parameter 'VALUE' is required.", 'matches');
     }
     $query->addFilter('=TYPE', $type);
     $query->addFilter('=VALUE', $value);
     $rootEntityID = 0;
     if (is_array($options) && isset($options['ROOT_ENTITY_ID'])) {
         $rootEntityID = (int) $options['ROOT_ENTITY_ID'];
     }
     if ($rootEntityID > 0) {
         $query->addFilter('!ENTITY_ID', $rootEntityID);
         $query->addFilter('!@ENTITY_ID', DuplicateIndexMismatch::prepareQueryField($criterion, $entityTypeID, $rootEntityID, $userID));
     }
     $limit = 0;
     if (is_array($options) && isset($options['LIMIT'])) {
         $limit = (int) $options['LIMIT'];
     }
     if ($limit > 0) {
         $query->setLimit($limit);
     }
     $dbResult = $query->exec();
     $fields = $dbResult->fetch();
     return is_array($fields) && isset($fields['QTY']) ? intval($fields['QTY']) : 0;
 }

 protected function processActionShowObjectInGrid()
 {
     if (!$this->checkRequiredGetParams(array('objectId'))) {
         $this->sendJsonErrorResponse();
     }
     /** @var Folder|File $object */
     $object = BaseObject::loadById((int) $this->request->getQuery('objectId'), array('STORAGE'));
     if (!$object) {
         $this->errorCollection->addOne(new Error('Could not find file or folder', self::ERROR_COULD_NOT_FIND_FILE));
         $this->sendJsonErrorResponse();
     }
     $storage = $object->getStorage();
     $securityContext = $storage->getCurrentUserSecurityContext();
     if (!$object->canRead($securityContext)) {
         $this->errorCollection->addOne(new Error('Could not find file or folder', self::ERROR_COULD_NOT_READ_FILE));
         $this->sendJsonErrorResponse();
     }
     $gridOptions = new Internals\Grid\FolderListOptions($storage);
     $pageSize = $gridOptions->getPageSize();
     $parameters = array('select' => array('ID'), 'filter' => array('PARENT_ID' => $object->getParentId(), 'DELETED_TYPE' => ObjectTable::DELETED_TYPE_NONE), 'order' => $gridOptions->getOrderForOrm(), 'limit' => $pageSize);
     $countQuery = new Query(ObjectTable::getEntity());
     $countQuery->addSelect(new ExpressionField('CNT', 'COUNT(1)'));
     $countQuery->setFilter($parameters['filter']);
     $totalCount = $countQuery->setLimit(null)->setOffset(null)->exec()->fetch();
     $totalCount = $totalCount['CNT'];
     $pageCount = ceil($totalCount / $pageSize);
     $driver = Driver::getInstance();
     $finalPage = null;
     for ($pageNumber = 1; $pageNumber <= $pageCount; $pageNumber++) {
         $fullParameters = $driver->getRightsManager()->addRightsCheck($securityContext, $parameters, array('ID', 'CREATED_BY'));
         $fullParameters['offset'] = $pageSize * ($pageNumber - 1);
         $query = ObjectTable::getList($fullParameters);
         while ($row = $query->fetch()) {
             if ($row['ID'] == $object->getId()) {
                 $finalPage = $pageNumber;
                 break;
             }
         }
         if ($finalPage !== null) {
             break;
         }
     }
     $finalPage = $finalPage ?: 1;
     $command = $this->request->getQuery('cmd') ?: '';
     if ($command) {
         $command = '!' . $command;
     }
     LocalRedirect($driver->getUrlManager()->getPathInListing($object) . "?&pageNumber={$finalPage}#hl-" . $object->getId() . $command);
 }

 /**
  * Deletes unnecessary files, which don't relate to version or object.
  *
  * @param int $portion Count of files which we want to delete. Default value is 10.
  * @return string
  */
 public static function deleteUnnecessaryFiles($portion = 10)
 {
     $query = new Query(FileTable::getEntity());
     $query->addSelect('ID')->addFilter('=EXTERNAL_ID', 'unnecessary')->addFilter('=MODULE_ID', Driver::INTERNAL_MODULE_ID)->setLimit($portion);
     $workLoad = false;
     $dbResult = $query->exec();
     while ($row = $dbResult->fetch()) {
         $workLoad = true;
         \CFile::delete($row['ID']);
     }
     if (!$workLoad) {
         return '';
     }
     return static::className() . '::deleteUnnecessaryFiles();';
 }

 /**
  * @return boolean
  */
 public static function isRegistered($ownerID)
 {
     if (!is_int($ownerID)) {
         $ownerID = (int) $ownerID;
     }
     if ($ownerID <= 0) {
         throw new Main\ArgumentException('Owner ID must be greater than zero.', 'ownerID');
     }
     $query = new Query(DealInvoiceStatisticsTable::getEntity());
     $query->addSelect('CREATED_DATE');
     $query->addFilter('=OWNER_ID', $ownerID);
     $query->setLimit(1);
     $dbResult = $query->exec();
     $result = $dbResult->fetch();
     return is_array($result);
 }

 function elemGetEx($elemId = false)
 {
     $hlHandler = $this->hlHandler;
     $getList = new Entity\Query($hlHandler);
     $getList->setSelect(array('*'));
     $getList->setOrder(array("ID" => "ASC"));
     if (!empty($elemId)) {
         $getList->setFilter(array("ID" => $elemId));
     }
     $result = $getList->exec();
     $result = new CDBResult($result);
     $arRes = array();
     while ($row = $result->Fetch()) {
         $arRes[] = $row;
     }
     return $arRes;
 }

 public static function checkFields(Result $result, $primary, array $data)
 {
     parent::checkFields($result, $primary, $data);
     if (!$result->isSuccess()) {
         return;
     }
     $query = new Entity\Query(static::getEntity());
     $res = $query->setSelect(array('ID', 'STATUS'))->setFilter(array('=TO_ENTITY' => $data['TO_ENTITY'], '=REAL_OBJECT_ID' => $data['REAL_OBJECT_ID']))->setLimit(2)->exec();
     while ($existing = $res->fetch()) {
         if (!isset($primary) || $primary != $existing['ID']) {
             if ($existing['STATUS'] == self::STATUS_IS_DECLINED) {
                 static::delete($existing['ID']);
             } else {
                 $result->addError(new Entity\EntityError(Loc::getMessage("DISK_SHARING_ENTITY_ERROR_NON_UNIQUE")));
             }
         }
     }
 }

 protected function buildQuery()
 {
     if ($this->useIblockSearch()) {
         $this->appendIblockRelatedData();
     } else {
         $entityClass = $this->getEntity()->getDataClass();
         $this->filter['IBLOCK_ID'] = $entityClass::getIblockId();
     }
     return parent::buildQuery();
 }

 public function validate($value, $primary, array $row, Entity\Field $field)
 {
     $entity = $field->getEntity();
     $primaryNames = $entity->getPrimaryArray();
     $query = new Entity\Query($entity);
     $query->setSelect($primaryNames);
     $query->setFilter(array('=' . $field->getName() => $value));
     $query->setLimit(2);
     $result = $query->exec();
     while ($existing = $result->fetch()) {
         // check primary
         foreach ($existing as $k => $v) {
             if (!isset($primary[$k]) || $primary[$k] != $existing[$k]) {
                 return $this->getErrorMessage($value, $field);
             }
         }
     }
     return true;
 }

 public static function getMismatches($entityTypeID, $entityID, $typeID, $matchHash, $userID, $limit = 0)
 {
     if (!is_int($limit)) {
         $limit = (int) $limit;
     }
     $results = array();
     $query = new Main\Entity\Query(Entity\DuplicateIndexMismatchTable::getEntity());
     $query->addSelect('R_ENTITY_ID', 'ENTITY_ID');
     $query->addFilter('=USER_ID', $userID);
     $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
     $query->addFilter('=TYPE_ID', $typeID);
     $query->addFilter('=MATCH_HASH', $matchHash);
     $query->addFilter('=L_ENTITY_ID', $entityID);
     if ($limit > 0) {
         $query->addOrder('R_ENTITY_ID', 'ASC');
         $query->setLimit($limit);
     }
     $dbResult = $query->exec();
     while ($fields = $dbResult->fetch()) {
         $results[] = (int) $fields['ENTITY_ID'];
     }
     $query = new Main\Entity\Query(Entity\DuplicateIndexMismatchTable::getEntity());
     $query->addSelect('L_ENTITY_ID', 'ENTITY_ID');
     $query->addFilter('=USER_ID', $userID);
     $query->addFilter('=ENTITY_TYPE_ID', $entityTypeID);
     $query->addFilter('=TYPE_ID', $typeID);
     $query->addFilter('=MATCH_HASH', $matchHash);
     $query->addFilter('=R_ENTITY_ID', $entityID);
     if ($limit > 0) {
         $query->addOrder('L_ENTITY_ID', 'ASC');
         $query->setLimit($limit);
     }
     $dbResult = $query->exec();
     while ($fields = $dbResult->fetch()) {
         $results[] = (int) $fields['ENTITY_ID'];
     }
     return $results;
 }

 /**
  * Method searches for a custom filter that, needs updating (notifications of new elements).
  * Custom filters are added to the stack of tasks.
  *
  * @param int $limitFilter
  * @return string
  */
 public static function findUserFilterNotify($limitFilter = 1, $limitFilterUser = 100)
 {
     $iblockElement = new \CIBlockElement();
     $connection = \Bitrix\Main\Application::getConnection();
     $sqlHelper = $connection->getSqlHelper();
     $limitFilter = (int) $limitFilter;
     $limitFilterUser = (int) $limitFilterUser;
     if ($limitFilterUser <= 0 || $limitFilterUser <= 0) {
         return;
     }
     $queryBuilder = new Entity\Query(Model\SubscribeTable::getEntity());
     $filterResult = $queryBuilder->setSelect(array('*'))->setFilter(array('ACTIVE' => 'Y'))->setLimit($limitFilter)->exec();
     while ($filterRow = $filterResult->fetch()) {
         $filter['IBLOCK_ID'] = $filterRow['IBLOCK_ID'];
         if ($filterRow['SECTION_ID'] > 0) {
             $filter['SECTION_ID'] = $filterRow['SECTION_ID'];
         }
         $filter = array_merge($filter, unserialize($filterRow['FILTER']));
         $elementResult = $iblockElement->GetList(false, $filter, array('IBLOCK_ID'))->fetch();
         if ($elementResult['CNT'] <= 0) {
             continue;
         }
         // Choose filters which notification less,
         // than the number of elements with filter
         $sql = "\n                SELECT DISTINCT t1.ID\n                FROM b_citfact_filter_subscribe_user as t1\n                LEFT OUTER JOIN b_citfact_filter_subscribe_notify as t2 ON (\n                    t1.ID = t2.FILTER_USER_ID AND\n                    t1.FILTER_ID = '" . $sqlHelper->forSql($filterRow['ID']) . "'\n                )\n                WHERE (SELECT count(ID) FROM b_citfact_filter_subscribe_notify as t3 WHERE t3.FILTER_USER_ID = t1.ID) < '" . $sqlHelper->forSql($elementResult['CNT']) . "'\n                GROUP BY t1.ID\n                LIMIT 0," . $limitFilterUser . "\n            ";
         // Adding custom filters to stack table
         $notifyList = (array) $connection->query($sql)->fetchAll();
         foreach ($notifyList as $key => $notify) {
             Model\SubscribeStackTable::add(array('FILTER_USER_ID' => $notify['ID'], 'ACTION' => 'UPDATE'));
         }
         // If list for stack table is not empty, then deactivate filter
         if (sizeof($notifyList) > 0) {
             Model\SubscribeTable::update($filterRow['ID'], array('ACTIVE' => 'N'));
         }
     }
     return "Citfact\\FilterSubscribe\\Agent::findUserFilterNotify({$limitFilter})";
 }

 /** @return array */
 public function prepareEntityListFilter(array $filterParams)
 {
     $filter = self::internalizeFilter($filterParams);
     $query = new Query(DealStageHistoryTable::getEntity());
     $query->addSelect('OWNER_ID');
     $query->addGroup('OWNER_ID');
     $period = $filter->getPeriod();
     $periodStartDate = $period['START'];
     $periodEndDate = $period['END'];
     $query->addFilter('=TYPE_ID', HistoryEntryType::CREATION);
     $query->addFilter('>=START_DATE', $periodStartDate);
     $query->addFilter('<=START_DATE', $periodEndDate);
     $query->registerRuntimeField('', new ExpressionField('E1', '(CASE WHEN NOT EXISTS(' . self::prepareHistoryQuery($periodStartDate, $periodEndDate, HistoryEntryType::MODIFICATION, '%s', '_i')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
     $query->addFilter('=E1', 1);
     $query->registerRuntimeField('', new ExpressionField('E2', '(CASE WHEN NOT EXISTS(' . self::prepareHistoryQuery($periodStartDate, $periodEndDate, HistoryEntryType::FINALIZATION, '%s', '_i')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
     $query->addFilter('=E2', 1);
     $query->registerRuntimeField('', new ExpressionField('E3', '(CASE WHEN NOT EXISTS(' . self::prepareActivityQuery($periodStartDate, $periodEndDate, '%s')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
     $query->addFilter('=E3', 1);
     $query->registerRuntimeField('', new ExpressionField('E4', '(CASE WHEN NOT EXISTS(' . self::prepareInvoiceQuery($periodStartDate, $periodEndDate, '%s')->getQuery() . ') THEN 1 ELSE 0 END)', 'OWNER_ID'));
     $query->addFilter('=E4', 1);
     $responsibleIDs = $filter->getResponsibleIDs();
     if (!empty($responsibleIDs)) {
         $query->addFilter('@RESPONSIBLE_ID', $responsibleIDs);
     }
     return array('__JOINS' => array(array('TYPE' => 'INNER', 'SQL' => 'INNER JOIN(' . $query->getQuery() . ') DS ON DS.OWNER_ID = L.ID')));
 }

 public static function getCount()
 {
     $query = new Query(static::getEntity());
     $query->setSelect(array('CNT' => array('expression' => array('COUNT(*)'), 'data_type' => 'integer')));
     $result = $query->exec()->fetch();
     return $result['CNT'];
 }