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