/** * Provides additions to the ___load query for when selectors or selector string are provided * * @param Selectors $selectors * @param DatabaseQuerySelect $query * @throws WireException * @return DatabaseQuerySelect * */ protected function getLoadQuerySelectors($selectors, DatabaseQuerySelect $query) { $database = $this->wire('database'); if (is_object($selectors) && $selectors instanceof Selectors) { // iterable selectors } else { if ($selectors && is_string($selectors)) { // selector string, convert to iterable selectors $selectors = new Selectors($selectors); } else { // nothing provided, load all assumed return $query; } } $functionFields = array('sort' => '', 'limit' => '', 'start' => ''); $item = $this->makeBlankItem(); $fields = array_keys($item->getTableData()); foreach ($selectors as $selector) { if (!$database->isOperator($selector->operator)) { throw new WireException("Operator '{$selector->operator}' may not be used in {$this->className}::load()"); } if (in_array($selector->field, $functionFields)) { $functionFields[$selector->field] = $selector->value; continue; } if (!in_array($selector->field, $fields)) { throw new WireException("Field '{$selector->field}' is not valid for {$this->className}::load()"); } $selectorField = $database->escapeTableCol($selector->field); $value = $database->escapeStr($selector->value); $query->where("{$selectorField}{$selector->operator}'{$value}'"); // QA } if ($functionFields['sort'] && in_array($functionFields['sort'], $fields)) { $query->orderby("{$functionFields['sort']}"); } if ($functionFields['limit']) { $query->limit(($functionFields['start'] ? (int) $functionFields['start'] . "," : '') . $functionFields['limit']); } return $query; }
/** * Match a number of children count * */ protected function getQueryNumChildren(DatabaseQuerySelect $query, $selector) { if (!in_array($selector->operator, array('=', '<', '>', '<=', '>=', '!='))) { throw new WireException("Operator '{$selector->operator}' not allowed for 'num_children' selector."); } if ($this->getQueryNumChildren) { throw new WireException("You may only have one 'children.count' selector per query"); } $value = (int) $selector->value; $this->getQueryNumChildren++; $n = $this->getQueryNumChildren; if (in_array($selector->operator, array('<', '<=', '!=')) && $value || ($selector->operator == '=' || $selector->operator == '>=') && !$value) { // allow for zero values $query->select("count(pages_num_children{$n}.id) AS num_children{$n}"); $query->leftjoin("pages AS pages_num_children{$n} ON (pages_num_children{$n}.parent_id=pages.id)"); $query->groupby("HAVING count(pages_num_children{$n}.id){$selector->operator}{$value}"); } else { // non zero values $query->select("pages_num_children{$n}.num_children{$n} AS num_children{$n}"); $query->leftjoin("(" . "SELECT p{$n}.parent_id, count(p{$n}.id) AS num_children{$n} " . "FROM pages AS p{$n} " . "GROUP BY p{$n}.parent_id " . "HAVING num_children{$n}{$selector->operator}{$value}" . ") pages_num_children{$n} ON pages_num_children{$n}.parent_id=pages.id"); $query->where("pages_num_children{$n}.num_children{$n}{$selector->operator}{$value}"); } }
/** * Get the query that matches a Fieldtype table's data with a given value * * Possible template method: If overridden, children should NOT call this parent method. * * @param DatabaseQuerySelect $query * @param string $table The table name to use * @param string $field Name of the field (typically 'data', unless selector explicitly specified another) * @param string $operator The comparison operator * @param mixed $value The value to find * @return DatabaseQuery $query * */ public function getMatchQuery($query, $table, $subfield, $operator, $value) { $value = $this->fuel('db')->escape_string($value); if (!$this->fuel('db')->isOperator($operator)) { throw new WireException("Operator '{$operator}' is not implemented in {$this->className}"); } $query->where("{$table}.{$subfield}{$operator}'{$value}'"); 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; }
/** * Get the query that matches a Fieldtype table's data with a given value * * Possible template method: If overridden, children should NOT call this parent method. * * @param DatabaseQuerySelect $query * @param string $table The table name to use * @param string $subfield Name of the field (typically 'data', unless selector explicitly specified another) * @param string $operator The comparison operator * @param mixed $value The value to find * @return DatabaseQuery $query * */ public function getMatchQuery($query, $table, $subfield, $operator, $value) { self::$getMatchQueryCount++; $n = self::$getMatchQueryCount; $field = $query->field; $database = $this->wire('database'); $table = $database->escapeTable($table); if ($subfield === 'count' && (empty($value) || ctype_digit(ltrim("{$value}", '-'))) && in_array($operator, array("=", "!=", ">", "<", ">=", "<="))) { $value = (int) $value; $t = $table . "_" . $n; $c = $database->escapeTable($this->className()) . "_" . $n; $query->select("{$t}.num_{$t} AS num_{$t}"); $query->leftjoin("(" . "SELECT {$c}.pages_id, COUNT({$c}.pages_id) AS num_{$t} " . "FROM " . $database->escapeTable($field->table) . " AS {$c} " . "GROUP BY {$c}.pages_id " . ") {$t} ON {$t}.pages_id=pages.id"); if (in_array($operator, array('<', '<=', '!=')) && $value || in_array($operator, array('>', '>=')) && $value < 0 || in_array($operator, array('=', '>=')) && !$value) { // allow for possible zero values $query->where("(num_{$t}{$operator}{$value} OR num_{$t} IS NULL)"); // QA } else { // non zero values $query->where("num_{$t}{$operator}{$value}"); // QA } // only allow matches using templates with the requested field $sql = 'pages.templates_id IN('; foreach ($field->getTemplates() as $template) { $sql .= (int) $template->id . ','; } $sql = rtrim($sql, ',') . ')'; $query->where($sql); // QA } else { $query = parent::getMatchQuery($query, $table, $subfield, $operator, $value); } return $query; }
/** * Get the query that matches a Fieldtype table's data with a given value * * Possible template method: If overridden, children should NOT call this parent method. * * @param DatabaseQuerySelect $query * @param string $table The table name to use * @param string $subfield Name of the subfield (typically 'data', unless selector explicitly specified another) * @param string $operator The comparison operator * @param mixed $value The value to find * @return DatabaseQuery $query * @throws WireException * */ public function getMatchQuery($query, $table, $subfield, $operator, $value) { $database = $this->wire('database'); if (!$database->isOperator($operator)) { throw new WireException("Operator '{$operator}' is not implemented in {$this->className}"); } $table = $database->escapeTable($table); $subfield = $database->escapeCol($subfield); $quoteValue = $database->quote($value); $query->where("{$table}.{$subfield}{$operator}{$quoteValue}"); // QA return $query; }
/** * Match a number of children count * */ protected function getQueryNumChildren(DatabaseQuerySelect $query, $selector) { if (!in_array($selector->operator, array('=', '<', '>', '<=', '>=', '!='))) { throw new PageFinderSyntaxException("Operator '{$selector->operator}' not allowed for 'num_children' selector."); } $value = (int) $selector->value; $this->getQueryNumChildren++; $n = (int) $this->getQueryNumChildren; $a = "pages_num_children{$n}"; $b = "num_children{$n}"; if (in_array($selector->operator, array('<', '<=', '!=')) && $value || in_array($selector->operator, array('>', '>=', '!=')) && $value < 0 || ($selector->operator == '=' || $selector->operator == '>=') && !$value) { // allow for zero values $query->select("COUNT({$a}.id) AS {$b}"); $query->leftjoin("pages AS {$a} ON ({$a}.parent_id=pages.id)"); $query->groupby("HAVING COUNT({$a}.id){$selector->operator}{$value}"); /* FOR REFERENCE $query->select("count(pages_num_children$n.id) AS num_children$n"); $query->leftjoin("pages AS pages_num_children$n ON (pages_num_children$n.parent_id=pages.id)"); $query->groupby("HAVING count(pages_num_children$n.id){$selector->operator}$value"); */ return $b; } else { // non zero values $query->select("{$a}.{$b} AS {$b}"); $query->leftjoin("(" . "SELECT p{$n}.parent_id, COUNT(p{$n}.id) AS {$b} " . "FROM pages AS p{$n} " . "GROUP BY p{$n}.parent_id " . "HAVING {$b}{$selector->operator}{$value} " . ") {$a} ON {$a}.parent_id=pages.id"); $where = "{$a}.{$b}{$selector->operator}{$value}"; $query->where($where); /* FOR REFERENCE $query->select("pages_num_children$n.num_children$n AS num_children$n"); $query->leftjoin( "(" . "SELECT p$n.parent_id, count(p$n.id) AS num_children$n " . "FROM pages AS p$n " . "GROUP BY p$n.parent_id " . "HAVING num_children$n{$selector->operator}$value" . ") pages_num_children$n ON pages_num_children$n.parent_id=pages.id"); $query->where("pages_num_children$n.num_children$n{$selector->operator}$value"); */ return "{$a}.{$b}"; } }
/** * Builds a WHERE clause for the query */ protected function _buildQueryWhere(DatabaseQuerySelect $query) { //Get only the unique states $states = $this->getState()->getValues(true); if (!empty($states)) { $states = $this->getTable()->mapColumns($states); foreach ($states as $key => $value) { if (isset($value)) { $query->where('tbl.' . $key . ' ' . (is_array($value) ? 'IN' : '=') . ' :' . $key)->bind(array($key => $value)); } } } }
/** * Special case when field is native to the pages table * * TODO not all operators will work here, so may want to add some translation or filtering * */ protected function getQueryNativeField(DatabaseQuerySelect $query, $selector, $field) { $value = $selector->value; $valueArray = is_array($value) ? $value : array($value); $sql = ''; if ($field == 'template') { // convert templates specified as a name to the numeric template ID // allows selectors like 'template=my_template_name' foreach ($valueArray as $k => $v) { if (!ctype_digit("{$v}")) { $valueArray[$k] = ($template = $this->fuel('templates')->get($v)) ? $template->id : 0; } } $field = 'templates_id'; } else { if ($field == 'parent') { // convert parent fields like '/about/company/history' to the equivalent ID foreach ($valueArray as $k => $v) { if (ctype_digit("{$v}")) { continue; } $parent = $this->fuel('pages')->get($v); if (!$parent instanceof NullPage) { $valueArray[$k] = $parent->id; } else { $valueArray[$k] = null; } } $field = 'parent_id'; } } foreach ($valueArray as $value) { if (is_null($value)) { // an invalid/unknown walue was specified, so make sure it fails $sql .= "1>2"; continue; } if (in_array($field, array('created', 'modified'))) { // prepare value for created or modified date fields if (!ctype_digit($value)) { $value = strtotime($value); } $value = date('Y-m-d H:i:s', $value); } if (!$this->db->isOperator($selector->operator)) { throw new WireException("Operator '{$selector->operator}' is not yet supported for fields native to pages table"); } $value = $this->db->escape_string($value); $s = "pages." . $field . $selector->operator . (ctype_digit("{$value}") ? (int) $value : "'{$value}'"); if ($selector->not) { $s = "NOT ({$s})"; } $sql .= $sql ? " OR {$s}" : "{$s}"; } $query->where("({$sql})"); }