PHP ModelCriteria::where方法代码示例

 /**
  * Add the search clause for an I18N column, taking care of the back/front context, as default_locale_i18n is
  * not defined in the backEnd I18N context.
  *
  * @param ModelCriteria $search
  * @param string $columnName the column to search into, such as TITLE
  * @param string $searchCriteria the search criteria, such as Criterial::LIKE, Criteria::EQUAL, etc.
  * @param string $searchTerm the searched term
  */
 public function addSearchInI18nColumn($search, $columnName, $searchCriteria, $searchTerm)
 {
     if (!$this->getBackendContext()) {
         $search->where("CASE WHEN NOT ISNULL(`requested_locale_i18n`.ID)\n                        THEN `requested_locale_i18n`.`{$columnName}`\n                        ELSE `default_locale_i18n`.`{$columnName}`\n                        END " . $searchCriteria . " ?", $searchTerm, \PDO::PARAM_STR);
     } else {
         $search->where("`requested_locale_i18n`.`{$columnName}` {$searchCriteria} ?", $searchTerm, \PDO::PARAM_STR);
     }
 }

 /**
  * @param ModelCriteria $search
  * @param               $requestedLocale
  * @param array $columns
  * @param null $foreignTable
  * @param string $foreignKey
  * @param bool $forceReturn
  * @param string $forceReturn
  */
 public static function getFrontEndI18n(ModelCriteria &$search, $requestedLocale, $columns, $foreignTable, $foreignKey, $forceReturn = false, $localeAlias = null)
 {
     if (!empty($columns)) {
         if ($foreignTable === null) {
             $foreignTable = $search->getTableMap()->getName();
             $aliasPrefix = '';
         } else {
             $aliasPrefix = $foreignTable . '_';
         }
         if ($localeAlias === null) {
             $localeAlias = $search->getTableMap()->getName();
         }
         $defaultLangWithoutTranslation = ConfigQuery::getDefaultLangWhenNoTranslationAvailable();
         $requestedLocaleI18nAlias = $aliasPrefix . 'requested_locale_i18n';
         $defaultLocaleI18nAlias = $aliasPrefix . 'default_locale_i18n';
         if ($defaultLangWithoutTranslation == Lang::STRICTLY_USE_REQUESTED_LANGUAGE) {
             $requestedLocaleJoin = new Join();
             $requestedLocaleJoin->addExplicitCondition($localeAlias, $foreignKey, null, $foreignTable . '_i18n', 'ID', $requestedLocaleI18nAlias);
             $requestedLocaleJoin->setJoinType($forceReturn === false ? Criteria::INNER_JOIN : Criteria::LEFT_JOIN);
             $defaultLocaleJoin = new Join();
             $defaultLocaleJoin->addExplicitCondition($localeAlias, $foreignKey, null, $foreignTable . '_i18n', 'ID', $defaultLocaleI18nAlias);
             $search->addJoinObject($requestedLocaleJoin, $requestedLocaleI18nAlias)->addJoinCondition($requestedLocaleI18nAlias, '`' . $requestedLocaleI18nAlias . '`.LOCALE = ?', $requestedLocale, null, \PDO::PARAM_STR);
             $search->addJoinObject($defaultLocaleJoin, $defaultLocaleI18nAlias)->addJoinCondition($defaultLocaleI18nAlias, '`' . $defaultLocaleI18nAlias . '`.LOCALE <> ?', $requestedLocale, null, \PDO::PARAM_STR);
             $search->withColumn('NOT ISNULL(`' . $requestedLocaleI18nAlias . '`.`ID`)', $aliasPrefix . 'IS_TRANSLATED');
             foreach ($columns as $column) {
                 $search->withColumn('`' . $requestedLocaleI18nAlias . '`.`' . $column . '`', $aliasPrefix . 'i18n_' . $column);
             }
         } else {
             $defaultLocale = Lang::getDefaultLanguage()->getLocale();
             $defaultLocaleJoin = new Join();
             $defaultLocaleJoin->addExplicitCondition($localeAlias, $foreignKey, null, $foreignTable . '_i18n', 'ID', $defaultLocaleI18nAlias);
             $defaultLocaleJoin->setJoinType(Criteria::LEFT_JOIN);
             $search->addJoinObject($defaultLocaleJoin, $defaultLocaleI18nAlias)->addJoinCondition($defaultLocaleI18nAlias, '`' . $defaultLocaleI18nAlias . '`.LOCALE = ?', $defaultLocale, null, \PDO::PARAM_STR);
             $requestedLocaleJoin = new Join();
             $requestedLocaleJoin->addExplicitCondition($localeAlias, $foreignKey, null, $foreignTable . '_i18n', 'ID', $requestedLocaleI18nAlias);
             $requestedLocaleJoin->setJoinType(Criteria::LEFT_JOIN);
             $search->addJoinObject($requestedLocaleJoin, $requestedLocaleI18nAlias)->addJoinCondition($requestedLocaleI18nAlias, '`' . $requestedLocaleI18nAlias . '`.LOCALE = ?', $requestedLocale, null, \PDO::PARAM_STR);
             $search->withColumn('NOT ISNULL(`' . $requestedLocaleI18nAlias . '`.`ID`)', $aliasPrefix . 'IS_TRANSLATED');
             if ($forceReturn === false) {
                 $search->where('NOT ISNULL(`' . $requestedLocaleI18nAlias . '`.ID)')->_or()->where('NOT ISNULL(`' . $defaultLocaleI18nAlias . '`.ID)');
             }
             foreach ($columns as $column) {
                 $search->withColumn('CASE WHEN NOT ISNULL(`' . $requestedLocaleI18nAlias . '`.ID) THEN `' . $requestedLocaleI18nAlias . '`.`' . $column . '` ELSE `' . $defaultLocaleI18nAlias . '`.`' . $column . '` END', $aliasPrefix . 'i18n_' . $column);
             }
         }
     }
 }

 public function testFindOneWithDistantClass()
 {
     BookstoreDataPopulator::populate();
     BookTableMap::clearInstancePool();
     AuthorTableMap::clearInstancePool();
     ReviewTableMap::clearInstancePool();
     $c = new ModelCriteria('bookstore', 'Propel\\Tests\\Bookstore\\Review');
     $c->setFormatter(ModelCriteria::FORMAT_ON_DEMAND);
     $c->where('Propel\\Tests\\Bookstore\\Review.Recommended = ?', true);
     $c->join('Propel\\Tests\\Bookstore\\Review.Book');
     $c->with('Book');
     $c->join('Book.Author');
     $c->with('Author');
     $c->limit(1);
     $con = Propel::getServiceContainer()->getConnection(BookTableMap::DATABASE_NAME);
     $reviews = $c->find($con);
     foreach ($reviews as $review) {
         break;
     }
     $count = $con->getQueryCount();
     $this->assertEquals($review->getReviewedBy(), 'Washington Post', 'Main object is correctly hydrated');
     $book = $review->getBook();
     $this->assertEquals($count, $con->getQueryCount(), 'with() hydrates the related objects to save a query');
     $this->assertEquals('Harry Potter and the Order of the Phoenix', $book->getTitle(), 'Related object is correctly hydrated');
     $author = $book->getAuthor();
     $this->assertEquals($count, $con->getQueryCount(), 'with() hydrates the related objects to save a query');
     $this->assertEquals('J.K.', $author->getFirstName(), 'Related object is correctly hydrated');
 }

 public function testUseQueryCustomClass()
 {
     $c = new ModelCriteria('bookstore', 'Propel\\Tests\\Bookstore\\Book', 'b');
     $c->thisIsMe = true;
     $c->where('b.Title = ?', 'foo');
     $c->setLimit(10);
     $c->leftJoin('b.Author a');
     $c2 = $c->useQuery('a', 'Propel\\Tests\\Runtime\\ActiveQuery\\ModelCriteriaForUseQuery');
     $this->assertTrue($c2 instanceof ModelCriteriaForUseQuery, 'useQuery() returns a secondary Criteria with the custom class');
     $c2->withNoName();
     $c = $c2->endUse();
     $con = Propel::getServiceContainer()->getConnection(BookTableMap::DATABASE_NAME);
     $c->find($con);
     $expectedSQL = $this->getSql("SELECT book.id, book.title, book.isbn, book.price, book.publisher_id, book.author_id FROM book LEFT JOIN author a ON (book.author_id=a.id) WHERE book.title = 'foo' AND a.first_name IS NOT NULL  AND a.last_name IS NOT NULL LIMIT 10");
     $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'useQuery() and endUse() allow to merge a custom secondary criteria');
 }

 public function testSelectArrayJoin()
 {
     BookstoreDataPopulator::depopulate($this->con);
     BookstoreDataPopulator::populate($this->con);
     $c = new ModelCriteria('bookstore', 'Propel\\Tests\\Bookstore\\Book');
     $c->join('Propel\\Tests\\Bookstore\\Book.Author');
     $c->where('Author.FirstName = ?', 'Neal');
     $c->select(array('Title', 'ISBN'));
     $titles = $c->find($this->con);
     $this->assertEquals($titles->count(), 1, 'find() called after select(array) allows for join() statements');
     $expectedSQL = $this->getSql("SELECT book.TITLE AS \"Title\", book.ISBN AS \"ISBN\" FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID) WHERE author.FIRST_NAME = 'Neal'");
     $this->assertEquals($expectedSQL, $this->con->getLastExecutedQuery(), 'find() called after select(array) allows for join() statements');
     $c = new ModelCriteria('bookstore', 'Propel\\Tests\\Bookstore\\Book');
     $c->join('Propel\\Tests\\Bookstore\\Book.Author');
     $c->where('Author.FirstName = ?', 'Neal');
     $c->select(array('Author.FirstName', 'Author.LastName'));
     $titles = $c->find($this->con);
     $this->assertEquals(array_values($titles->shift()), array('Neal', 'Stephenson'), 'find() called after select(array) will return values from the joined table using complete column names');
     $c = new ModelCriteria('bookstore', 'Propel\\Tests\\Bookstore\\Book');
     $c->join('Propel\\Tests\\Bookstore\\Book.Author');
     $c->where('Author.FirstName = ?', 'Neal');
     $c->select(array('Title', 'ISBN'));
     $title = $c->findOne($this->con);
     $this->assertEquals(count($title), 2, 'findOne() called after select(array) allows for join() statements');
     $expectedSQL = $this->getSql("SELECT book.TITLE AS \"Title\", book.ISBN AS \"ISBN\" FROM `book` INNER JOIN `author` ON (book.AUTHOR_ID=author.ID) WHERE author.FIRST_NAME = 'Neal' LIMIT 1");
     $this->assertEquals($expectedSQL, $this->con->getLastExecutedQuery(), 'findOne() called after select(array) allows for join() statements');
     $c = new ModelCriteria('bookstore', 'Propel\\Tests\\Bookstore\\Book');
     $c->join('Propel\\Tests\\Bookstore\\Book.Author');
     $c->where('Author.FirstName = ?', 'Neal');
     $c->select(array('Author.FirstName', 'Author.LastName'));
     $title = $c->findOne($this->con);
     $this->assertEquals(array_values($title), array('Neal', 'Stephenson'), 'findOne() called after select(array) will return values from the joined table using complete column names');
 }

 /**
  * @param ModelCriteria $query
  * @param \Jarves\Configuration\Condition $condition
  * @return \PDOStatement
  * @throws \PDOException
  */
 public function getStm(ModelCriteria $query, Condition $condition = null)
 {
     $params = [];
     $condition2Params = [];
     $id = hexdec(uniqid()) / mt_rand() + mt_rand();
     // check that the columns of the main class are already added (if this is the primary ModelCriteria)
     if (!$query->hasSelectClause() && !$query->getPrimaryCriteria()) {
         $query->addSelfSelectColumns();
     }
     $con = RuntimePropel::getServiceContainer()->getReadConnection($query->getDbName());
     $query->configureSelectColumns();
     $dbMap = RuntimePropel::getServiceContainer()->getDatabaseMap($query->getDbName());
     $db = RuntimePropel::getServiceContainer()->getAdapter($query->getDbName());
     $model = $query->getModelName();
     $tableMap = constant($model . '::TABLE_MAP');
     $query->setPrimaryTableName(constant($tableMap . '::TABLE_NAME'));
     //        $query->find($con);
     if ($condition) {
         $query->where($id . ' = ' . $id);
     }
     $sql = $query->createSelectSql($params);
     $conditionSql = '';
     if ($condition) {
         $condition2Params = $params;
         $conditionSql = $this->conditionOperator->standardConditionToSql($condition, $condition2Params, $this->getObjectKey());
     }
     if ($condition && $conditionSql) {
         $sql = str_replace($id . ' = ' . $id, '(' . $conditionSql . ')', $sql);
     }
     /** @var \PDOStatement $stmt */
     try {
         $stmt = $con->prepare($sql);
     } catch (\PDOException $e) {
         throw new PropelException('Could not execute query ' . $sql, 0, $e);
     }
     $db->bindValues($stmt, $params, $dbMap);
     if ($condition2Params) {
         foreach ($condition2Params as $idx => $v) {
             if (!is_array($v)) {
                 //propel uses arrays as bind values, we with Condition->toSql not.
                 $stmt->bindValue($idx, $v);
             }
         }
     }
     try {
         $stmt->execute();
     } catch (\PDOException $e) {
         throw new \PDOException($e->getMessage() . "\nSQL: {$sql}");
     }
     return $stmt;
 }

 public function testFindOneWithDistantClass()
 {
     BookstoreDataPopulator::populate();
     BookTableMap::clearInstancePool();
     AuthorTableMap::clearInstancePool();
     ReviewTableMap::clearInstancePool();
     $c = new ModelCriteria('bookstore', 'Propel\\Tests\\Bookstore\\Review');
     $c->setFormatter(ModelCriteria::FORMAT_ARRAY);
     $c->where('Propel\\Tests\\Bookstore\\Review.Recommended = ?', true);
     $c->join('Propel\\Tests\\Bookstore\\Review.Book');
     $c->with('Book');
     $c->join('Book.Author');
     $c->with('Author');
     $review = $c->findOne();
     $this->assertEquals($review['ReviewedBy'], 'Washington Post', 'Main object is correctly hydrated');
     $book = $review['Book'];
     $this->assertEquals('Harry Potter and the Order of the Phoenix', $book['Title'], 'Related object is correctly hydrated');
     $author = $book['Author'];
     $this->assertEquals('J.K.', $author['FirstName'], 'Related object is correctly hydrated');
 }

 public function testUseQueryCustomClass()
 {
     $c = new ModelCriteria('bookstore', 'Propel\\Tests\\Bookstore\\Book', 'b');
     $c->thisIsMe = true;
     $c->where('b.Title = ?', 'foo');
     $c->setLimit(10);
     $c->leftJoin('b.Author a');
     $c2 = $c->useQuery('a', 'Propel\\Tests\\Runtime\\ActiveQuery\\ModelCriteriaForUseQuery');
     $this->assertTrue($c2 instanceof ModelCriteriaForUseQuery, 'useQuery() returns a secondary Criteria with the custom class');
     $c2->withNoName();
     $c = $c2->endUse();
     $con = Propel::getServiceContainer()->getConnection(BookTableMap::DATABASE_NAME);
     $c->find($con);
     $expectedSQL = $this->getSql("SELECT book.ID, book.TITLE, book.ISBN, book.PRICE, book.PUBLISHER_ID, book.AUTHOR_ID FROM `book` LEFT JOIN `author` `a` ON (book.AUTHOR_ID=a.ID) WHERE book.TITLE = 'foo' AND a.FIRST_NAME IS NOT NULL  AND a.LAST_NAME IS NOT NULL LIMIT 10");
     $this->assertEquals($expectedSQL, $con->getLastExecutedQuery(), 'useQuery() and endUse() allow to merge a custom secondary criteria');
 }

 /**
  * @param \Propel\Runtime\ActiveQuery\ModelCriteria $query
  * @param array $searchColumns
  * @param \Generated\Shared\Transfer\DataTablesColumnTransfer $column
  *
  * @return void
  */
 protected function addQueryCondition(ModelCriteria $query, array $searchColumns, DataTablesColumnTransfer $column)
 {
     $search = $column->getSearch();
     if (preg_match('/created_at|updated_at/', $searchColumns[$column->getData()])) {
         $query->where(sprintf("(%s >= '%s' AND %s <= '%s')", $searchColumns[$column->getData()], $this->filterSearchValue($search[self::PARAMETER_VALUE]) . ' 00:00:00', $searchColumns[$column->getData()], $this->filterSearchValue($search[self::PARAMETER_VALUE]) . ' 23:59:59'));
         return;
     }
     $value = $this->filterSearchValue($search[self::PARAMETER_VALUE]);
     if ($value === 'null') {
         return;
     }
     $query->where(sprintf("%s = '%s'", $searchColumns[$column->getData()], $value));
 }