/**
* 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})");
}
