/**
* This function iterates over `dsParamFILTERS` and builds the relevant
* `$where` and `$joins` parameters with SQL. This SQL is generated from
* `Field->buildDSRetrievalSQL`. A third parameter, `$group` is populated
* with boolean from `Field->requiresSQLGrouping()`
*
* @param string $where
* @param string $joins
* @param boolean $group
*/
public function processFilters(&$where, &$joins, &$group)
{
if (!is_array($this->dsParamFILTERS) || empty($this->dsParamFILTERS)) {
return;
}
$pool = FieldManager::fetch(array_keys($this->dsParamFILTERS));
self::$_fieldPool += $pool;
foreach ($this->dsParamFILTERS as $field_id => $filter) {
if (is_array($filter) && empty($filter) || trim($filter) == '') {
continue;
}
if (!is_array($filter)) {
$filter_type = $this->__determineFilterType($filter);
$value = preg_split('/' . ($filter_type == DS_FILTER_AND ? '\\+' : '(?<!\\\\),') . '\\s*/', $filter, -1, PREG_SPLIT_NO_EMPTY);
$value = array_map('trim', $value);
$value = array_map(array('Datasource', 'removeEscapedCommas'), $value);
} else {
$value = $filter;
}
if ($field_id != 'id' && $field_id != 'system:date' && !self::$_fieldPool[$field_id] instanceof Field) {
throw new Exception(__('Error creating field object with id %1$d, for filtering in data source %2$s. Check this field exists.', array($field_id, '<code>' . $this->dsParamROOTELEMENT . '</code>')));
}
if ($field_id == 'id') {
$c = 'IN';
if (stripos($value[0], 'not:') === 0) {
$value[0] = preg_replace('/^not:\\s*/', null, $value[0]);
$c = 'NOT IN';
}
$where = " AND `e`.id " . $c . " ('" . implode("', '", $value) . "') ";
} else {
if ($field_id == 'system:date') {
require_once TOOLKIT . '/fields/field.date.php';
$date = new fieldDate();
$date->buildDSRetrievalSQL($value, $joins, $where, $filter_type == DS_FILTER_AND ? true : false);
$where = preg_replace('/`t\\d+`.value/', '`e`.creation_date', $where);
} else {
// For deprecated reasons, call the old, typo'd function name until the switch to the
// properly named buildDSRetrievalSQL function.
if (!self::$_fieldPool[$field_id]->buildDSRetrivalSQL($value, $joins, $where, $filter_type == DS_FILTER_AND ? true : false)) {
$this->_force_empty_result = true;
return;
}
if (!$group) {
$group = self::$_fieldPool[$field_id]->requiresSQLGrouping();
}
}
}
}
}
/**
* This function iterates over `dsParamFILTERS` and builds the relevant
* `$where` and `$joins` parameters with SQL. This SQL is generated from
* `Field->buildDSRetrievalSQL`. A third parameter, `$group` is populated
* with boolean from `Field->requiresSQLGrouping()`
*
* @param string $where
* @param string $joins
* @param boolean $group
*/
public function processFilters(&$where, &$joins, &$group)
{
if (!is_array($this->dsParamFILTERS) || empty($this->dsParamFILTERS)) {
return;
}
$pool = FieldManager::fetch(array_filter(array_keys($this->dsParamFILTERS), 'is_int'));
self::$_fieldPool += $pool;
if (!is_string($where)) {
$where = '';
}
foreach ($this->dsParamFILTERS as $field_id => $filter) {
if (is_array($filter) && empty($filter) || trim($filter) == '') {
continue;
}
if (!is_array($filter)) {
$filter_type = $this->__determineFilterType($filter);
$value = preg_split('/' . ($filter_type == DataSource::FILTER_AND ? '\\+' : '(?<!\\\\),') . '\\s*/', $filter, -1, PREG_SPLIT_NO_EMPTY);
$value = array_map('trim', $value);
$value = array_map(array('Datasource', 'removeEscapedCommas'), $value);
} else {
$value = $filter;
}
if (!in_array($field_id, self::$_system_parameters) && $field_id != 'id' && !self::$_fieldPool[$field_id] instanceof Field) {
throw new Exception(__('Error creating field object with id %1$d, for filtering in data source %2$s. Check this field exists.', array($field_id, '<code>' . $this->dsParamROOTELEMENT . '</code>')));
}
// Support system:id as well as the old 'id'. #1691
if ($field_id === 'system:id' || $field_id === 'id') {
$c = 'IN';
if (stripos($value[0], 'not:') === 0) {
$value[0] = preg_replace('/^not:\\s*/', null, $value[0]);
$c = 'NOT IN';
}
// Cast all ID's to integers.
$value = array_map(create_function('$x', 'return (int)$x;'), $value);
$count = array_sum($value);
$value = array_filter($value);
// If the ID was cast to 0, then we need to filter on 'id' = 0,
// which will of course return no results, but without it the
// Datasource will return ALL results, which is not the
// desired behaviour. RE: #1619
if ($count === 0) {
$value[] = '0';
}
// If there are no ID's, no need to filter. RE: #1567
if (!empty($value)) {
$where .= " AND `e`.id " . $c . " (" . implode(", ", $value) . ") ";
}
} else {
if ($field_id === 'system:creation-date' || $field_id === 'system:modification-date' || $field_id === 'system:date') {
require_once TOOLKIT . '/fields/field.date.php';
$date_joins = '';
$date_where = '';
$date = new fieldDate();
$date->buildDSRetrievalSQL($value, $date_joins, $date_where, $filter_type == DataSource::FILTER_AND ? true : false);
// Replace the date field where with the `creation_date` or `modification_date`.
$date_where = preg_replace('/`t\\d+`.date/', $field_id !== 'system:modification-date' ? '`e`.creation_date_gmt' : '`e`.modification_date_gmt', $date_where);
$where .= $date_where;
} else {
// For deprecated reasons, call the old, typo'd function name until the switch to the
// properly named buildDSRetrievalSQL function.
if (!self::$_fieldPool[$field_id]->buildDSRetrivalSQL($value, $joins, $where, $filter_type == DataSource::FILTER_AND ? true : false)) {
$this->_force_empty_result = true;
return;
}
if (!$group) {
$group = self::$_fieldPool[$field_id]->requiresSQLGrouping();
}
}
}
}
}
}
if (!isset($fieldPool[$field_id]) || !is_object($fieldPool[$field_id])) {
$fieldPool[$field_id] =& $entryManager->fieldManager->fetch($field_id);
}
if ($field_id != 'id' && $field_id != 'system:date' && !$fieldPool[$field_id] instanceof Field) {
throw new Exception(__('Error creating field object with id %1$d, for filtering in data source "%2$s". Check this field exists.', array($field_id, $this->dsParamROOTELEMENT)));
}
if ($field_id == 'id') {
$where = " AND `e`.id IN ('" . implode("', '", $value) . "') ";
} else {
if ($field_id == 'system:date') {
require_once TOOLKIT . '/fields/field.date.php';
$date = new fieldDate(Frontend::instance());
// Create an empty string, we don't care about the Joins, we just want the WHERE clause.
$empty = "";
$date->buildDSRetrievalSQL($value, $empty, $where, $filter_type == DS_FILTER_AND ? true : false);
$where = preg_replace('/`t\\d+`.value/', '`e`.creation_date', $where);
} else {
// For deprecated reasons, call the old, typo'd function name until the switch to the
// properly named buildDSRetrievalSQL function.
if (!$fieldPool[$field_id]->buildDSRetrivalSQL($value, $joins, $where, $filter_type == DS_FILTER_AND ? true : false)) {
$this->_force_empty_result = true;
return;
}
if (!$group) {
$group = $fieldPool[$field_id]->requiresSQLGrouping();
}
}
}
}
}
/**
* Get where and join information to build a query.
*
* The information returned by this function can be used in the
* fetch() methods of the EntryManager class. If you only need
* to fetch data the getSlice function is recommended.
*
* @return array
*/
public function getWhereJoinsAndGroup($count_only = false)
{
$where = NULL;
$joins = NULL;
if (is_array($this->dsParamFILTERS) && !empty($this->dsParamFILTERS)) {
foreach ($this->dsParamFILTERS as $field_id => $filter) {
if (is_array($filter) && empty($filter) || trim($filter) == '') {
continue;
}
if (!is_array($filter)) {
$filter_type = $this->__determineFilterType($filter);
$value = preg_split('/' . ($filter_type == DS_FILTER_AND ? '\\+' : '(?<!\\\\),') . '\\s*/', $filter, -1, PREG_SPLIT_NO_EMPTY);
$value = array_map('trim', $value);
$value = array_map(array('Datasource', 'removeEscapedCommas'), $value);
} else {
$value = $filter;
}
if (!isset($fieldPool[$field_id]) || !is_object($fieldPool[$field_id])) {
$fieldPool[$field_id] =& FieldManager::fetch($field_id);
}
if ($field_id != 'id' && $field_id != 'system:date' && !$fieldPool[$field_id] instanceof Field) {
throw new Exception(__('Error creating field object with id %1$d, for filtering in data source "%2$s". Check this field exists.', array($field_id, $this->dsParamROOTELEMENT)));
}
if ($field_id == 'id') {
$where = " AND `e`.id IN ('" . implode("', '", $value) . "') ";
} elseif ($field_id == 'system:date') {
$date = new fieldDate(Frontend::instance());
// Create an empty string, we don't care about the Joins, we just want the WHERE clause.
$empty = "";
$date->buildDSRetrievalSQL($value, $empty, $where, $filter_type == DS_FILTER_AND ? true : false);
$where = preg_replace('/`t\\d+`.value/', '`e`.creation_date', $where);
} else {
if (!$fieldPool[$field_id]->buildDSRetrievalSQL($value, $joins, $where, $filter_type == DS_FILTER_AND ? true : false)) {
$this->_force_empty_result = true;
return;
}
if (!$group) {
$group = $fieldPool[$field_id]->requiresSQLGrouping();
}
}
}
}
$where .= ' AND `d`.`value` IS NOT NULL';
$joins .= ' LEFT JOIN tbl_entries_data_' . FieldManager::fetchFieldIDFromElementName($this->emailField, $this->getSource()) . ' AS `d` ON `e`.`id` = `d`.`entry_id`';
if ($this->newsletter_id !== NULL) {
$joins .= ' LEFT OUTER JOIN tbl_tmp_email_newsletters_sent_' . $this->newsletter_id . ' AS `n` ON `d`.`value` = `n`.`email`';
$where .= ' AND `n`.`email` IS NULL GROUP BY `d`.`value`';
} elseif ($count_only != true) {
$where .= ' GROUP BY `d`.`value`';
}
return array('where' => $where, 'joins' => $joins);
}
