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