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