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;
}
