public function augmentSQL(SQLQuery &$query)
{
if (empty($query->getSelect()) || $query->getDelete() || in_array("COUNT(*)", $query->getSelect()) || in_array("count(*)", $query->getSelect())) {
return;
}
if (!$query->getWhere() || !preg_match('/\\.(\'|"|`|)ID(\'|"|`|)( ?)=/', $query->getWhere()[0])) {
$c = Controller::curr();
if (!startsWith($c->class, "CMS") && !startsWith($c->class, "Model") && $c->getAction() != "update") {
$className = $this->owner->ClassName;
$query->addWhere("{$className}.Published=1");
// $query->addWhere( "DATE({$className}.PublicationDate)<=DATE(NOW())" );
$query->addWhere("({$className}.ExpirationDate IS NULL) OR ({$className}.ExpirationDate IS NOT NULL AND DATE({$className}.ExpirationDate)>=DATE(NOW()))");
}
}
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Actives locales defined on a SiteConfig are there as a global setting
if ($this->owner instanceof SiteConfig) {
return;
}
// In admin, show everthing anyway
if ($this->isAdminBackend()) {
return;
}
// Find in set is only compatible with MySql
$c = DB::getConn();
if (!$c instanceof MySQLDatabase) {
return;
}
$locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
$from = $query->getFrom();
$where = $query->getWhere();
$column = 'ActiveLocales';
$table = null;
// Check on which table is the ActiveLocales field
foreach ($from as $fromTable => $conditions) {
if ($table === null) {
$table = $fromTable;
}
$db = DataObject::custom_database_fields($fromTable);
if ($db && isset($db[$column])) {
$table = $fromTable;
break;
}
}
$identifier = "\"{$table}\".\"{$column}\"";
$where[] = "{$identifier} IS NULL OR FIND_IN_SET ('{$locale}', {$identifier}) > 0";
$query->setWhere($where);
}
/**
* Update any requests to limit the results to the current site
*/
public function augmentSQL(SQLQuery &$query)
{
if (Subsite::$disable_subsite_filter) {
return;
}
// If you're querying by ID, ignore the sub-site - this is a bit ugly... (but it was WAYYYYYYYYY worse)
//@TODO I don't think excluding if SiteTree_ImageTracking is a good idea however because of the SS 3.0 api and ManyManyList::removeAll() changing the from table after this function is called there isn't much of a choice
$from = $query->getFrom();
$where = $query->getWhere();
if (!isset($from['SiteTree_ImageTracking']) && !($where && preg_match('/\\.(\'|"|`|)ID(\'|"|`|)/', $where[0]))) {
$subsiteID = (int) Subsite::currentSubsiteID();
// The foreach is an ugly way of getting the first key :-)
foreach ($query->getFrom() as $tableName => $info) {
$where = "\"{$tableName}\".\"SubsiteID\" IN (0, {$subsiteID})";
$query->addWhere($where);
break;
}
$sect = array_values($query->getSelect());
$isCounting = strpos($sect[0], 'COUNT') !== false;
// Ordering when deleting or counting doesn't apply
if (!$query->getDelete() && !$isCounting) {
$query->addOrderBy("\"SubsiteID\"");
}
}
}
/**
* Remove a filter from the query
*/
public function removeFilterOn($fieldExpression)
{
$matched = false;
$where = $this->query->getWhere();
foreach ($where as $i => $clause) {
if (strpos($clause, $fieldExpression) !== false) {
unset($where[$i]);
$matched = true;
}
}
// set the entire where clause back, but clear the original one first
if ($matched) {
$this->query->setWhere($where);
} else {
throw new InvalidArgumentException("Couldn't find {$fieldExpression} in the query filter.");
}
return $this;
}
/**
* Determines if the current query is supposed
* to be exempt from the automatic filtering out
* of temporary records.
*
* @param SQLQuery $query
* @return boolean
*/
protected function wantsTemporary($query)
{
foreach ($query->getWhere() as $whereClause) {
$from = array_values($query->getFrom());
// SQLQuery will automatically add double quotes and single quotes to values, so check against that.
if ($whereClause == "{$from[0]}.\"MultiFormIsTemporary\" = '1'") {
return true;
}
}
return false;
}
public function augmentSQL(SQLQuery &$query, DataQuery &$dataQuery = null)
{
// Get locale and translation zone to use
$locale = $dataQuery->getQueryParam('Fluent.Locale') ?: Fluent::current_locale();
// Get all tables to translate fields for, and their respective field names
$includedTables = $this->getTranslatedTables();
// Iterate through each select clause, replacing each with the translated version
foreach ($query->getSelect() as $alias => $select) {
// Skip fields without table context
if (!preg_match('/^"(?<class>\\w+)"\\."(?<field>\\w+)"$/i', $select, $matches)) {
continue;
}
$class = $matches['class'];
$field = $matches['field'];
// If this table doesn't have translated fields then skip
if (empty($includedTables[$class])) {
continue;
}
// If this field shouldn't be translated, skip
if (!in_array($field, $includedTables[$class])) {
continue;
}
// Select visible field from translated fields (Title_fr_FR || Title => Title)
$translatedField = Fluent::db_field_for_locale($field, $locale);
$expression = $this->localiseSelect($class, $translatedField, $field);
$query->selectField($expression, $alias);
}
// Rewrite where conditions
$where = $query->getWhere();
foreach ($where as $index => $condition) {
// determine the table/column this condition is against
$filterColumn = $this->detectFilterColumn($condition, $includedTables, $locale);
if (empty($filterColumn)) {
continue;
}
// Duplicate the condition with all localisable fields replaced
$localisedCondition = $this->localiseFilterCondition($condition, $includedTables, $locale);
if ($localisedCondition === $condition) {
continue;
}
// Generate new condition that conditionally executes one of the two conditions
// depending on field nullability.
// If the filterColumn is null or empty, then it's considered untranslated, and
// thus the query should continue running on the default column unimpeded.
$where[$index] = "\n\t\t\t\t({$filterColumn} IS NOT NULL AND {$filterColumn} != '' AND ({$localisedCondition}))\n\t\t\t\tOR (\n\t\t\t\t\t({$filterColumn} IS NULL OR {$filterColumn} = '') AND ({$condition})\n\t\t\t\t)";
}
$query->setWhere($where);
// Augment search if applicable
if ($adapter = Fluent::search_adapter()) {
$adapter->augmentSearch($query, $dataQuery);
}
}
/**
* Rewrites the WHERE fragment of a query
*
* @param string $class
* @param array $translatedColumns Translated columns
* @param SQLQUery $query
* @param string $keywords SQL escaped keywords
* @param string $keywordsHTML HTML escaped keywords
*/
public function augmentFilter($class, $translatedColumns, SQLQuery $query, $keywords, $keywordsHTML, $booleanMode)
{
// Augment the search section
$locale = Fluent::current_locale();
$wherePattern = self::$where_replacements[$class];
$translatedPattern = $wherePattern;
$searchColumns = self::$search_columns[$class];
foreach (array_intersect($searchColumns, $translatedColumns) as $column) {
$replacement = Fluent::db_field_for_locale($column, $locale);
$translatedPattern = preg_replace('/\\b' . preg_quote($column) . '\\b/', $replacement, $translatedPattern);
}
// If no fields were translated, then don't modify
if ($translatedPattern === $wherePattern) {
return;
}
// Inject keywords into patterns
$search = array('/\\$keywords/i', '/\\$htmlEntityKeywords/i', '/\\$boolean/i');
$replace = array($keywords, $keywordsHTML, $booleanMode ? 'IN BOOLEAN MODE' : '');
$whereOriginal = preg_replace($search, $replace, $wherePattern);
$whereTranslated = preg_replace($search, $replace, $translatedPattern);
$where = $query->getWhere();
$newWhere = array();
foreach ($query->getWhere() as $where) {
// Remove excessive whitespace which breaks string replacement
$where = preg_replace('/\\s+/im', ' ', $where);
$whereOriginal = preg_replace('/\\s+/im', ' ', $whereOriginal);
$newWhere[] = str_replace($whereOriginal, "{$whereOriginal} + {$whereTranslated}", $where);
}
$query->setWhere($newWhere);
}
/**
* Test deprecation of SQLQuery::getWhere working appropriately
*/
public function testDeprecatedGetWhere()
{
// Temporarily disable deprecation
Deprecation::notification_version(null);
$query = new SQLQuery();
$query->setSelect(array('"SQLQueryTest_DO"."Name"'));
$query->setFrom('"SQLQueryTest_DO"');
$query->addWhere(array('"SQLQueryTest_DO"."Date" > ?' => '2012-08-08 12:00'));
$query->addWhere('"SQLQueryTest_DO"."Name" = \'Richard\'');
$query->addWhere(array('"SQLQueryTest_DO"."Meta" IN (?, \'Who?\', ?)' => array('Left', 'Right')));
$expectedSQL = <<<EOS
SELECT "SQLQueryTest_DO"."Name"
FROM "SQLQueryTest_DO"
WHERE ("SQLQueryTest_DO"."Date" > ?)
AND ("SQLQueryTest_DO"."Name" = 'Richard')
AND ("SQLQueryTest_DO"."Meta" IN (?, 'Who?', ?))
EOS;
$expectedParameters = array('2012-08-08 12:00', 'Left', 'Right');
// Check sql evaluation of this query maintains the parameters
$sql = $query->sql($parameters);
$this->assertSQLEquals($expectedSQL, $sql);
$this->assertEquals($expectedParameters, $parameters);
// Check that ->toAppropriateExpression()->setWhere doesn't modify the query
$query->setWhere($query->toAppropriateExpression()->getWhere());
$sql = $query->sql($parameters);
$this->assertSQLEquals($expectedSQL, $sql);
$this->assertEquals($expectedParameters, $parameters);
// Check that getWhere are all flattened queries
$expectedFlattened = array('"SQLQueryTest_DO"."Date" > \'2012-08-08 12:00\'', '"SQLQueryTest_DO"."Name" = \'Richard\'', '"SQLQueryTest_DO"."Meta" IN (\'Left\', \'Who?\', \'Right\')');
$this->assertEquals($expectedFlattened, $query->getWhere());
}
/**
* Convert a SQLQuery object into a SQL statement
* @param $query SQLQuery
*/
public function sqlQueryToString(SQLQuery $query)
{
if ($query->getDelete()) {
$text = 'DELETE ';
} else {
$text = $this->sqlSelectToString($query->getSelect(), $query->getDistinct());
}
if ($query->getFrom()) {
$text .= $this->sqlFromToString($query->getFrom());
}
if ($query->getWhere()) {
$text .= $this->sqlWhereToString($query->getWhere(), $query->getConnective());
}
// these clauses only make sense in SELECT queries, not DELETE
if (!$query->getDelete()) {
if ($query->getGroupBy()) {
$text .= $this->sqlGroupByToString($query->getGroupBy());
}
if ($query->getHaving()) {
$text .= $this->sqlHavingToString($query->getHaving());
}
if ($query->getOrderBy()) {
$text .= $this->sqlOrderByToString($query->getOrderBy());
}
if ($query->getLimit()) {
$text .= $this->sqlLimitToString($query->getLimit());
}
}
return $text;
}
/**
* Changes any SELECT query thats not filtering on an ID
* to limit by the current language defined in {@link get_current_locale()}.
* It falls back to "Locale='' OR Lang IS NULL" and assumes that
* this implies querying for the default language.
*
* Use {@link disable_locale_filter()} to temporarily disable this "auto-filtering".
*/
function augmentSQL(SQLQuery &$query)
{
// If the record is saved (and not a singleton), and has a locale,
// limit the current call to its locale. This fixes a lot of problems
// with other extensions like Versioned
$locale = $this->owner->ID && $this->owner->Locale ? $this->owner->Locale : Translatable::get_current_locale();
$baseTable = ClassInfo::baseDataClass($this->owner->class);
if ($locale && self::locale_filter_enabled() && !$query->filtersOnID() && array_search($baseTable, array_keys($query->getFrom())) !== false && !preg_match('/("|\'|`)Locale("|\'|`)/', implode(' ', $query->getWhere()))) {
$qry = sprintf('"%s"."Locale" = \'%s\'', $baseTable, Convert::raw2sql($locale));
$query->addWhere($qry);
}
}
/**
* Check if a given SQLQuery filters on the Locale field
*
* @param SQLQuery $query
* @return boolean
*/
protected function filtersOnLocale($query)
{
foreach ($query->getWhere() as $condition) {
// Compat for 3.1/3.2 where syntax
if (is_array($condition)) {
// In >=3.2 each $condition is a single length array('condition' => array('params'))
reset($condition);
$condition = key($condition);
}
// >=3.2 allows conditions to be expressed as evaluatable objects
if (interface_exists('SQLConditionGroup') && $condition instanceof SQLConditionGroup) {
$condition = $condition->conditionSQL($params);
}
if (preg_match('/("|\'|`)Locale("|\'|`)/', $condition)) {
return true;
}
}
}
/**
* Convert a SQLQuery object into a SQL statement
* Caution: Expects correctly quoted and escaped SQL fragments.
*
* @param $query SQLQuery
*/
public function sqlQueryToString(SQLQuery $query)
{
if ($query->getDelete()) {
//Appended space at the end of string causing an issue but this might not be the best solution
//@see sqlSelectToString() sqlFromToString
$text = 'DELETE';
} else {
if ($traverse = $query->getTraverse()) {
//Build the traverse string here
$text = $this->sqlTraverseToString($traverse);
} else {
$text = $this->sqlSelectToString($query->getSelect(), $query->getDistinct());
}
}
if ($query->getFrom()) {
$text .= $this->sqlFromToString($query->getFrom());
}
if ($query->getWhere()) {
$text .= $this->sqlWhereToString($query->getWhere(), $query->getConnective());
}
// these clauses only make sense in SELECT queries, not DELETE
if (!$query->getDelete()) {
if ($query->getGroupBy()) {
$text .= $this->sqlGroupByToString($query->getGroupBy());
}
if ($query->getHaving()) {
$text .= $this->sqlHavingToString($query->getHaving());
}
if ($query->getOrderBy()) {
$text .= $this->sqlOrderByToString($query->getOrderBy());
}
if ($query->getLimit()) {
$text .= $this->sqlLimitToString($query->getLimit());
}
}
// SS_Log::log(new Exception(print_r($text, true)), SS_Log::NOTICE);
return $text;
}
