/**
* Given one or more selectors, create the SQL query for finding pages.
*
* @TODO split this method up into more parts, it's too long
*
* @param array $selectors Array of selectors.
* @return string SQL statement.
*
*/
protected function ___getQuery($selectors)
{
$where = '';
$cnt = 1;
$fieldtypes = $this->fieldtypes;
$fieldCnt = array();
// counts number of instances for each field to ensure unique table aliases for ANDs on the same field
$lastSelector = null;
$sortSelectors = array();
// selector containing 'sort=', which gets added last
$joins = array();
$nullPage = new NullPage();
$startLimit = false;
// true when the start/limit part of the query generation is done
$query = new DatabaseQuerySelect();
$query->select(array('pages.id', 'pages.parent_id', 'pages.templates_id'));
$query->from("pages");
$query->groupby("pages.id");
foreach ($selectors as $selectorCnt => $selector) {
if (is_null($lastSelector)) {
$lastSelector = $selector;
}
$fields = $selector->field;
$fields = is_array($fields) ? $fields : array($fields);
$field = reset($fields);
// first field
// TODO Make native fields and path/url multi-field and multi-value aware
if ($field == 'sort') {
$sortSelectors[] = $selector;
continue;
} else {
if ($field == 'limit' || $field == 'start') {
if (!$startLimit) {
$this->getQueryStartLimit($query, $selectors);
}
$startLimit = true;
continue;
} else {
if ($field == 'path' || $field == 'url') {
$this->getQueryJoinPath($query, $selector);
continue;
} else {
if ($field == 'has_parent' || $field == 'hasParent') {
$this->getQueryHasParent($query, $selector);
continue;
} else {
if ($field == 'num_children' || $field == 'numChildren' || $field == 'children.count') {
$this->getQueryNumChildren($query, $selector);
continue;
} else {
if ($this->getFuel('fields')->isNativeName($field)) {
$this->getQueryNativeField($query, $selector, $field);
continue;
}
}
}
}
}
}
foreach ($fields as $n => $field) {
// if a specific DB field from the table has been specified, then get it, otherwise assume 'data'
if (strpos($field, ".")) {
list($field, $subfield) = explode(".", $field);
} else {
$subfield = 'data';
}
if (!($field = $this->fuel('fields')->get($field))) {
throw new WireException("Field does not exist: {$fields[$n]}");
}
// keep track of number of times this table name has appeared in the query
if (!isset($fieldCnt[$field->table])) {
$fieldCnt[$field->table] = 0;
} else {
$fieldCnt[$field->table]++;
}
// use actual table name if first instance, if second instance of table then add a number at the end
$tableAlias = $field->table . ($fieldCnt[$field->table] ? $fieldCnt[$field->table] : '');
$valueArray = is_array($selector->value) ? $selector->value : array($selector->value);
$join = '';
$fieldtype = $field->type;
foreach ($valueArray as $value) {
if (isset($subqueries[$tableAlias])) {
$q = $subqueries[$tableAlias];
} else {
$q = new DatabaseQuerySelect();
}
//if($subfield == 'data' && in_array($selector->operator, array('=', '!=', '<>')) && $value === $fieldtype->getBlankValue($nullPage, $field)) {
if ($subfield == 'data' && empty($value) && in_array($selector->operator, array('=', '!='))) {
// handle blank values -- look in table that has no pages_id relation back to pages, using the LEFT JOIN / IS NULL trick
$query->leftjoin("{$tableAlias} ON {$tableAlias}.pages_id=pages.id");
$query->where("{$tableAlias}.pages_id " . ($selector->operator == '=' ? "IS" : "IS NOT") . " NULL");
continue;
} else {
$q->set('field', $field);
// original field if required by the fieldtype
$q = $fieldtype->getMatchQuery($q, $tableAlias, $subfield, $selector->operator, $value);
if (count($q->select)) {
$query->select($q->select);
}
if (count($q->join)) {
$query->join($q->join);
}
if (count($q->leftjoin)) {
$query->leftjoin($q->leftjoin);
}
if (count($q->orderby)) {
$query->orderby($q->orderby);
}
if (count($q->groupby)) {
$query->groupby($q->groupby);
}
}
if (count($q->where)) {
$and = $selector->not ? "AND NOT" : "AND";
$sql = '';
foreach ($q->where as $w) {
$sql .= $sql ? "{$and} {$w} " : "{$w} ";
}
$sql = "({$sql}) ";
if ($selector->operator == '!=') {
$join .= $join ? "\n\t\tAND {$sql} " : $sql;
} else {
if ($selector->not) {
$sql = "(NOT {$sql})";
$join .= $join ? "\n\t\tAND {$sql} " : $sql;
} else {
$join .= $join ? "\n\t\tOR {$sql} " : $sql;
}
}
}
$cnt++;
}
if ($join) {
$joinType = "join";
if (count($fields) > 1 || $subfield == 'count') {
$joinType = "leftjoin";
if ($where) {
$whereType = $lastSelector->str == $selector->str ? "OR" : ") AND (";
$where .= "\n\t{$whereType} ({$join}) ";
} else {
$where .= "({$join}) ";
}
}
// we compile the joins after going through all the selectors, so that we can
// match up conditions to the same tables
if (isset($joins[$tableAlias])) {
$joins[$tableAlias]['join'] .= " AND ({$join}) ";
} else {
$joins[$tableAlias] = array('joinType' => $joinType, 'table' => $field->table, 'tableAlias' => $tableAlias, 'join' => "({$join})");
}
}
$lastSelector = $selector;
}
// fields
}
// selectors
if ($where) {
$query->where("({$where})");
}
$this->getQueryAllowedTemplates($query);
// complete the joins, matching up any conditions for the same table
foreach ($joins as $j) {
$joinType = $j['joinType'];
$query->{$joinType}("{$j['table']} AS {$j['tableAlias']} ON {$j['tableAlias']}.pages_id=pages.id AND ({$j['join']})");
}
if (count($sortSelectors)) {
foreach (array_reverse($sortSelectors) as $s) {
$this->getQuerySortSelector($query, $s);
}
}
return $query;
}
/**
* Given one or more selectors, create the SQL query for finding pages.
*
* @TODO split this method up into more parts, it's too long
*
* @param array $selectors Array of selectors.
* @param array $options
* @return DatabaseQuerySelect
* @throws PageFinderSyntaxException
*
*/
protected function ___getQuery($selectors, array $options)
{
$where = '';
$cnt = 1;
$fieldCnt = array();
// counts number of instances for each field to ensure unique table aliases for ANDs on the same field
$lastSelector = null;
$sortSelectors = array();
// selector containing 'sort=', which gets added last
$joins = array();
$startLimit = false;
// true when the start/limit part of the query generation is done
$database = $this->wire('database');
$query = new DatabaseQuerySelect();
$query->select($options['returnVerbose'] ? array('pages.id', 'pages.parent_id', 'pages.templates_id') : array('pages.id'));
$query->from("pages");
$query->groupby("pages.id");
foreach ($selectors as $selector) {
if (is_null($lastSelector)) {
$lastSelector = $selector;
}
if (!$this->preProcessSelector($selector)) {
continue;
}
$fields = $selector->field;
$group = $selector->group;
// i.e. @field
$fields = is_array($fields) ? $fields : array($fields);
if (count($fields) > 1) {
$fields = $this->arrangeFields($fields);
}
$fieldsStr = ':' . implode(':', $fields) . ':';
// for strpos
$field = reset($fields);
// first field
if (strpos($field, '.')) {
list($field, $subfield) = explode('.', $field);
} else {
$subfield = '';
}
// TODO Make native fields and path/url multi-field and multi-value aware
if ($field == 'sort') {
$sortSelectors[] = $selector;
continue;
} else {
if ($field == 'limit' || $field == 'start') {
if (!$startLimit) {
$this->getQueryStartLimit($query, $selectors);
}
$startLimit = true;
continue;
} else {
if ($field == 'path' || $field == 'url') {
$this->getQueryJoinPath($query, $selector);
continue;
} else {
if ($field == 'has_parent' || $field == 'hasParent') {
$this->getQueryHasParent($query, $selector);
continue;
} else {
if ($field == 'num_children' || $field == 'numChildren' || $field == 'children' && $subfield == 'count') {
$this->getQueryNumChildren($query, $selector);
continue;
} else {
if ($this->wire('fields')->isNative($field) || strpos($fieldsStr, ':parent.') !== false) {
$this->getQueryNativeField($query, $selector, $fields);
continue;
}
}
}
}
}
}
// where SQL specific to the foreach() of fields below, if needed.
// in this case only used by internally generated shortcuts like the blank value condition
$whereFields = '';
$whereFieldsType = 'AND';
foreach ($fields as $n => $fieldName) {
// if a specific DB field from the table has been specified, then get it, otherwise assume 'data'
if (strpos($fieldName, ".")) {
list($fieldName, $subfield) = explode(".", $fieldName);
} else {
$subfield = 'data';
}
if (!($field = $this->wire('fields')->get($fieldName))) {
// field does not exist, try to match an API variable
$value = $this->wire($fieldName);
if (!$value) {
throw new PageFinderSyntaxException("Field does not exist: {$fieldName}");
}
if (count($fields) > 1) {
throw new PageFinderSyntaxException("You may only match 1 API variable at a time");
}
if (is_object($value)) {
if ($subfield == 'data') {
$subfield = 'id';
}
$selector->field = $subfield;
}
if (!$selector->matches($value)) {
$query->where("1>2");
// force non match
}
break;
}
// keep track of number of times this table name has appeared in the query
if (!isset($fieldCnt[$field->table])) {
$fieldCnt[$field->table] = 0;
} else {
$fieldCnt[$field->table]++;
}
// use actual table name if first instance, if second instance of table then add a number at the end
$tableAlias = $field->table . ($fieldCnt[$field->table] ? $fieldCnt[$field->table] : '');
$tableAlias = $database->escapeTable($tableAlias);
$valueArray = is_array($selector->value) ? $selector->value : array($selector->value);
$join = '';
$fieldtype = $field->type;
$operator = $selector->operator;
$numEmptyValues = 0;
foreach ($valueArray as $value) {
// shortcut for blank value condition: this ensures that NULL/non-existence is considered blank
// without this section the query would still work, but a blank value must actually be present in the field
$useEmpty = empty($value) || $value && $operator[0] == '<' || $value < 0 && $operator[0] == '>';
if ($subfield == 'data' && $useEmpty && $fieldtype) {
// && !$fieldtype instanceof FieldtypeMulti) {
if (empty($value)) {
$numEmptyValues++;
}
if (in_array($operator, array('=', '!=', '<>', '<', '<=', '>', '>='))) {
// we only accommodate this optimization for single-value selectors...
if ($this->whereEmptyValuePossible($field, $selector, $query, $value, $whereFields)) {
if (count($valueArray) > 1 && $operator == '=') {
$whereFieldsType = 'OR';
}
continue;
}
}
}
if (isset($subqueries[$tableAlias])) {
$q = $subqueries[$tableAlias];
} else {
$q = new DatabaseQuerySelect();
}
$q->set('field', $field);
// original field if required by the fieldtype
$q->set('group', $group);
// original group of the field, if required by the fieldtype
$q->set('selector', $selector);
// original selector if required by the fieldtype
$q->set('selectors', $selectors);
// original selectors (all) if required by the fieldtype
$q->set('parentQuery', $query);
$q = $fieldtype->getMatchQuery($q, $tableAlias, $subfield, $selector->operator, $value);
if (count($q->select)) {
$query->select($q->select);
}
if (count($q->join)) {
$query->join($q->join);
}
if (count($q->leftjoin)) {
$query->leftjoin($q->leftjoin);
}
if (count($q->orderby)) {
$query->orderby($q->orderby);
}
if (count($q->groupby)) {
$query->groupby($q->groupby);
}
if (count($q->where)) {
// $and = $selector->not ? "AND NOT" : "AND";
$and = "AND";
/// moved NOT condition to entire generated $sql
$sql = '';
foreach ($q->where as $w) {
$sql .= $sql ? "{$and} {$w} " : "{$w} ";
}
$sql = "({$sql}) ";
if ($selector->operator == '!=') {
$join .= $join ? "\n\t\tAND {$sql} " : $sql;
} else {
if ($selector->not) {
$sql = "((NOT {$sql}) OR ({$tableAlias}.pages_id IS NULL))";
$join .= $join ? "\n\t\tAND {$sql} " : $sql;
} else {
$join .= $join ? "\n\t\tOR {$sql} " : $sql;
}
}
}
$cnt++;
}
if ($join) {
$joinType = 'join';
if (count($fields) > 1 || count($valueArray) > 1 && $numEmptyValues > 0 || $subfield == 'count' || $selector->not && $selector->operator != '!=' || $selector->operator == '!=') {
// join should instead be a leftjoin
$joinType = "leftjoin";
if ($where) {
$whereType = $lastSelector->str == $selector->str ? "OR" : ") AND (";
$where .= "\n\t{$whereType} ({$join}) ";
} else {
$where .= "({$join}) ";
}
if ($selector->not) {
// removes condition from join, but ensures we still have a $join
$join = '1=1';
}
}
// we compile the joins after going through all the selectors, so that we can
// match up conditions to the same tables
if (isset($joins[$tableAlias])) {
$joins[$tableAlias]['join'] .= " AND ({$join}) ";
} else {
$joins[$tableAlias] = array('joinType' => $joinType, 'table' => $field->table, 'tableAlias' => $tableAlias, 'join' => "({$join})");
}
}
$lastSelector = $selector;
}
// fields
if (strlen($whereFields)) {
if (strlen($where)) {
$where = "({$where}) {$whereFieldsType} ({$whereFields})";
} else {
$where .= "({$whereFields})";
}
}
}
// selectors
if ($where) {
$query->where("({$where})");
}
$this->getQueryAllowedTemplates($query, $options);
// complete the joins, matching up any conditions for the same table
foreach ($joins as $j) {
$joinType = $j['joinType'];
$query->{$joinType}("{$j['table']} AS {$j['tableAlias']} ON {$j['tableAlias']}.pages_id=pages.id AND ({$j['join']})");
}
if (count($sortSelectors)) {
foreach (array_reverse($sortSelectors) as $s) {
$this->getQuerySortSelector($query, $s);
}
}
$this->postProcessQuery($query);
return $query;
}
/**
* Perform a partial match on title of options
*
* @param Field $field
* @param string $property Either 'title' or 'value'
* @param string $operator
* @param string $value Value to find
* @return SelectableOptionArray
*
*/
public function findOptionsByProperty(Field $field, $property, $operator, $value)
{
if ($operator == '=' || $operator == '!=') {
// no need to use fulltext matching if operator is not a partial match operator
return $this->getOptions($field, array($property => $value));
}
$query = new DatabaseQuerySelect();
$query->select('*');
$query->from(self::optionsTable);
$query->where("fields_id=:fields_id");
$query->bindValue(':fields_id', $field->id);
$ft = new DatabaseQuerySelectFulltext($query);
$ft->match(self::optionsTable, $property, $operator, $value);
$result = $query->execute();
$options = new SelectableOptionArray();
$options->setField($field);
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
$option = $this->arrayToOption($row);
$options->add($option);
}
$options->resetTrackChanges();
return $options;
}
/**
* Given one or more selectors, create the SQL query for finding pages.
*
* @TODO split this method up into more parts, it's too long
*
* @param array $selectors Array of selectors.
* @return string SQL statement.
* @throws PageFinderSyntaxException
*
*/
protected function ___getQuery($selectors, array $options)
{
$where = '';
$cnt = 1;
$fieldCnt = array();
// counts number of instances for each field to ensure unique table aliases for ANDs on the same field
$lastSelector = null;
$sortSelectors = array();
// selector containing 'sort=', which gets added last
$joins = array();
$startLimit = false;
// true when the start/limit part of the query generation is done
$database = $this->wire('database');
$query = new DatabaseQuerySelect();
$query->select(array('pages.id', 'pages.parent_id', 'pages.templates_id'));
$query->from("pages");
$query->groupby("pages.id");
foreach ($selectors as $selectorCnt => $selector) {
if (is_null($lastSelector)) {
$lastSelector = $selector;
}
$fields = $selector->field;
$fields = is_array($fields) ? $fields : array($fields);
$fieldsStr = ':' . implode(':', $fields) . ':';
// for strpos
$field = reset($fields);
// first field
if (strpos($field, '.')) {
list($field, $subfield) = explode('.', $field);
} else {
$subfield = '';
}
// TODO Make native fields and path/url multi-field and multi-value aware
if ($field == 'sort') {
$sortSelectors[] = $selector;
continue;
} else {
if ($field == 'limit' || $field == 'start') {
if (!$startLimit) {
$this->getQueryStartLimit($query, $selectors);
}
$startLimit = true;
continue;
} else {
if ($field == 'path' || $field == 'url') {
$this->getQueryJoinPath($query, $selector);
continue;
} else {
if ($field == 'has_parent' || $field == 'hasParent') {
$this->getQueryHasParent($query, $selector);
continue;
} else {
if ($field == 'num_children' || $field == 'numChildren' || $field == 'children' && $subfield == 'count') {
$this->getQueryNumChildren($query, $selector);
continue;
} else {
if ($this->getFuel('fields')->isNativeName($field) || strpos($fieldsStr, ':parent.') !== false) {
$this->getQueryNativeField($query, $selector, $fields);
continue;
}
}
}
}
}
}
// where SQL specific to the foreach() of fields below, if needed.
// in this case only used by internally generated shortcuts like the blank value condition
$whereFields = '';
foreach ($fields as $n => $field) {
// if a specific DB field from the table has been specified, then get it, otherwise assume 'data'
if (strpos($field, ".")) {
list($field, $subfield) = explode(".", $field);
} else {
$subfield = 'data';
}
if (!($field = $this->fuel('fields')->get($field))) {
throw new PageFinderSyntaxException("Field does not exist: {$fields[$n]}");
}
// keep track of number of times this table name has appeared in the query
if (!isset($fieldCnt[$field->table])) {
$fieldCnt[$field->table] = 0;
} else {
$fieldCnt[$field->table]++;
}
// use actual table name if first instance, if second instance of table then add a number at the end
$tableAlias = $field->table . ($fieldCnt[$field->table] ? $fieldCnt[$field->table] : '');
$tableAlias = $database->escapeTable($tableAlias);
$valueArray = is_array($selector->value) ? $selector->value : array($selector->value);
$join = '';
$fieldtype = $field->type;
foreach ($valueArray as $value) {
// shortcut for blank value condition: this ensures that NULL/non-existence is considered blank
// without this section the query would still work, but a blank value must actually be present in the field
if ($subfield == 'data') {
if (empty($value) && in_array($selector->operator, array('=', '!=', '<>'))) {
// handle blank values -- look in table that has no pages_id relation back to pages, using the LEFT JOIN / IS NULL trick
// OR check for blank value as defined by the fieldtype
$blankValue = $database->escapeStr($fieldtype->getBlankValue(new NullPage(), $field));
if ($field->table === $tableAlias) {
$query->leftjoin("{$tableAlias} ON {$tableAlias}.pages_id=pages.id");
} else {
$query->leftjoin($database->escapeTable($field->table) . " AS {$tableAlias} ON {$tableAlias}.pages_id=pages.id");
}
$whereFields .= strlen($whereFields) ? ' OR ' : '';
if ($selector->operator == '=') {
$whereFields .= "({$tableAlias}.pages_id IS NULL OR {$tableAlias}.data='{$blankValue}')";
} else {
$whereFields .= "({$tableAlias}.pages_id IS NOT NULL AND {$tableAlias}.data!='{$blankValue}')";
}
unset($blankValue);
continue;
}
}
if (isset($subqueries[$tableAlias])) {
$q = $subqueries[$tableAlias];
} else {
$q = new DatabaseQuerySelect();
}
$q->set('field', $field);
// original field if required by the fieldtype
$q->set('selector', $selector);
// original selector if required by the fieldtype
$q->set('parentQuery', $query);
$q = $fieldtype->getMatchQuery($q, $tableAlias, $subfield, $selector->operator, $value);
if (count($q->select)) {
$query->select($q->select);
}
if (count($q->join)) {
$query->join($q->join);
}
if (count($q->leftjoin)) {
$query->leftjoin($q->leftjoin);
}
if (count($q->orderby)) {
$query->orderby($q->orderby);
}
if (count($q->groupby)) {
$query->groupby($q->groupby);
}
if (count($q->where)) {
// $and = $selector->not ? "AND NOT" : "AND";
$and = "AND";
/// moved NOT condition to entire generated $sql
$sql = '';
foreach ($q->where as $w) {
$sql .= $sql ? "{$and} {$w} " : "{$w} ";
}
$sql = "({$sql}) ";
if ($selector->operator == '!=') {
$join .= $join ? "\n\t\tAND {$sql} " : $sql;
} else {
if ($selector->not) {
$sql = "((NOT {$sql}) OR ({$tableAlias}.pages_id IS NULL))";
$join .= $join ? "\n\t\tAND {$sql} " : $sql;
} else {
$join .= $join ? "\n\t\tOR {$sql} " : $sql;
}
}
}
$cnt++;
}
if ($join) {
$joinType = 'join';
if (count($fields) > 1 || $subfield == 'count' || $selector->not && $selector->operator != '!=' || $selector->operator == '!=') {
$joinType = "leftjoin";
if ($where) {
$whereType = $lastSelector->str == $selector->str ? "OR" : ") AND (";
$where .= "\n\t{$whereType} ({$join}) ";
} else {
$where .= "({$join}) ";
}
if ($selector->not) {
// removes condition from join, but ensures we still have a $join
$join = '1=1';
}
}
// we compile the joins after going through all the selectors, so that we can
// match up conditions to the same tables
if (isset($joins[$tableAlias])) {
$joins[$tableAlias]['join'] .= " AND ({$join}) ";
} else {
$joins[$tableAlias] = array('joinType' => $joinType, 'table' => $field->table, 'tableAlias' => $tableAlias, 'join' => "({$join})");
}
}
$lastSelector = $selector;
}
// fields
if (strlen($whereFields)) {
$where .= (strlen($where) ? 'AND ' : '') . "({$whereFields})";
}
}
// selectors
if ($where) {
$query->where("({$where})");
}
$this->getQueryAllowedTemplates($query, $options);
// complete the joins, matching up any conditions for the same table
foreach ($joins as $j) {
$joinType = $j['joinType'];
$query->{$joinType}("{$j['table']} AS {$j['tableAlias']} ON {$j['tableAlias']}.pages_id=pages.id AND ({$j['join']})");
}
if (count($sortSelectors)) {
foreach (array_reverse($sortSelectors) as $s) {
$this->getQuerySortSelector($query, $s);
}
}
return $query;
}
