/**
* Apply filter to query builder
*
* @param \Engine\Mvc\Model\Query\Builder $dataSource
* @return string
*/
public function filterWhere(Builder $dataSource)
{
$adapter = $dataSource->getModel()->getReadConnection();
$fields = [];
foreach ($this->_fields as $field) {
$fields[] = $adapter->escapeIdentifier($field);
}
$expr = implode(',', $fields);
return "MATCH (" . $expr . ") AGAINST (" . $this->getFilterStr($dataSource) . ")";
}
/**
* Return filter value string
*
* @param \Engine\Mvc\Model\Query\Builder $dataSource
* @return string
*/
protected function getFilterStr(Builder $dataSource)
{
$compare = $this->getCompareCriteria();
$values = $this->_value;
if (!is_array($values)) {
$values = [$values];
}
$adapter = $dataSource->getModel()->getReadConnection();
foreach ($values as &$value) {
$value = $adapter->escapeString($value);
}
if (count($values) == 0) {
return "";
}
return $compare . "(" . implode(", ", $values) . ")";
}
/**
* Return values
*
* @param string $id
* @return array
*/
protected function _getManyValues($id)
{
if (!$this->_queryBuilder) {
$path = $this->_path;
$workedModel = array_shift($path);
$model = new $workedModel();
$this->_queryBuilder = $model->queryBuilder();
$name = \Engine\Mvc\Model::NAME;
$this->_queryBuilder->columnsJoinOne($path, $name);
$mainModel = $this->_grid->getContainer()->getDataSource()->getModel();
$relations = $mainModel->getRelationPath($workedModel);
if (!$relations) {
throw new \Engine\Exception("Relations to model '" . get_class($model) . "' by path '" . implode(", ", $path) . "' not valid");
}
$relation = array_pop($relations);
$field = $relation->getReferencedFields();
$this->_queryBuilder->where($field . " = :id:");
if ($this->_count) {
$this->_queryBuilder->limit($this->_count);
}
$this->_queryBuilder->orderBy($this->_orderBy);
}
$rows = $this->_queryBuilder->getQuery()->execute(['id' => $id]);
$values = [];
foreach ($rows as $row) {
$values[] = $row->{$name};
}
return $values;
}
/**
* Set datasource
*
* @return void
*/
protected function _setDataSource()
{
$this->_dataSource = $this->_model->queryBuilder();
foreach ($this->_joins as $table) {
$this->_dataSource->columnsJoinOne($table);
}
$this->_dataSource->columns($this->_columns);
foreach ($this->_conditions as $cond) {
if (is_array($cond)) {
$this->_dataSource->addWhere($cond['cond'], $cond['params']);
} else {
$this->_dataSource->addWhere($cond);
}
}
$sort = $this->_grid->getSortKey();
if (null === $sort) {
$sort = $this->_model->getOrderExpr();
}
$direction = $this->_grid->getSortDirection();
if (null === $direction) {
$direction = $this->_model->getOrderAsc() ? "ASC" : "DESC";
}
if ($sort) {
$alias = $this->_dataSource->getCorrelationName($sort);
if ($alias) {
$sort = $alias . "." . $sort;
}
if ($direction) {
$this->_dataSource->orderBy($sort . ' ' . $direction);
} else {
$this->_dataSource->orderBy($sort);
}
}
}
/**
* Apply filter to query builder
*
* @param \Engine\Mvc\Model\Query\Builder $dataSource
* @return string
*/
public function filterWhere(Builder $dataSource)
{
$model = $dataSource->getModel();
if ($this->_column === self::COLUMN_ID) {
$expr = $model->getPrimary();
$alias = $dataSource->getAlias();
} elseif ($this->_column === self::COLUMN_NAME) {
$expr = $model->getNameExpr();
$alias = $dataSource->getAlias();
} else {
$expr = $this->_column;
$alias = $dataSource->getCorrelationName($this->_column);
}
if (!$alias) {
throw new \Engine\Exception("Field '" . $this->_column . "' not found in query builder");
}
return $alias . "." . $expr . " " . $this->getFilterStr($dataSource);
}
/**
* Set datasource
*
* @return void
*/
protected function _setDataSource()
{
$this->_dataSource = $this->_model->queryBuilder();
foreach ($this->_joins as $table) {
$this->_dataSource->columnsJoinOne($table);
}
$this->_dataSource->columns($this->_columns);
foreach ($this->_conditions as $cond) {
if (is_array($cond)) {
$this->_dataSource->addWhere($cond['cond'], $cond['params']);
} else {
$this->_dataSource->addWhere($cond);
}
}
$sort = $this->_grid->getSortKey();
$direction = $this->_grid->getSortDirection();
if (!empty($sort)) {
if (!empty($direction)) {
$this->_dataSource->orderBy($this->_grid->getSortKey() . ' ' . $this->_grid->getSortDirection());
} else {
$this->_dataSource->orderBy($this->_grid->getSortKey());
}
}
}
/**
* Apply filter to query builder
*
* @param \Engine\Mvc\Model\Query\Builder $dataSource
* @return string
*/
public function filterWhere(Builder $dataSource)
{
$where = [];
$adapter = $dataSource->getModel()->getReadConnection();
$exprEq = $adapter->escapeString($this->_value);
$exprLike = $adapter->escapeString("%{$this->_value}%");
$exprBegins = $adapter->escapeString("{$this->_value}%");
$model = $dataSource->getModel();
foreach ($this->_columns as $column => $criteria) {
if ($column === self::COLUMN_ID) {
$alias = $dataSource->getAlias();
$column = $model->getPrimary();
} elseif ($column === self::COLUMN_NAME) {
$alias = $dataSource->getAlias();
$column = $model->getNameExpr();
$t = true;
} else {
$alias = $dataSource->getCorrelationName($column);
}
if ($criteria === self::CRITERIA_EQ) {
$where[] = "{$alias}.{$column} = {$exprEq}";
} elseif ($criteria === self::CRITERIA_LIKE) {
$where[] = "{$alias}.{$column} LIKE {$exprLike}";
} elseif ($criteria === self::CRITERIA_BEGINS) {
$where[] = "{$alias}.{$column} LIKE {$exprBegins}";
} elseif ($criteria === self::CRITERIA_MORE) {
$where[] = "`{$alias}`.`{$column}` > {$exprEq}";
} elseif ($criteria === self::CRITERIA_LESS) {
$where[] = "{$alias}.{$column} < {$exprEq}";
}
}
if (count($where) > 0) {
$where = implode(" OR ", $where);
return "({$where})";
}
return false;
}
/**
* Apply filter to query builder
*
* @param \Engine\Mvc\Model\Query\Builder $dataSource
* @return string
*/
public function filterWhere(Builder $dataSource)
{
$adapter = $adapter = $dataSource->getModel()->getReadConnection();
return $this->_field . ($this->_criteria == self::CRITERIA_NOTEQ) ? " NOT " : " " . "BETWEEN " . $adapter->escapeString($this->_min) . " AND " . $adapter->escapeString($this->_max);
}
/**
* Create a criteria for a especific model
*
* @param string $alias
* @return \Engine\Mvc\Model\Query\Builder
*/
public function queryBuilder($alias = null)
{
$params = [];
$builder = new Builder($params);
$builder->setModel($this, $alias);
if (static::$_conditions !== null) {
$builder->where($this->normalizeConditions(static::$_conditions));
}
return $builder;
}
static function prepareOptionsAll(\Engine\Mvc\Model\Query\Builder $queryBuilder, $name = null, $category = null, $categoryName = null, $where = null, $emptyCategory = "n/a", $emptyItem = "n/a", $multiselect = false, &$fields = null, $category_order = null)
{
if ($emptyCategory === null) {
$emptyCategory = self::EMPTY_CATEGORY;
}
if ($emptyItem === null) {
$emptyItem = self::EMPTY_ITEM;
}
$model = $queryBuilder->getModel();
if ($where) {
if (!is_array($where)) {
$where = array($where);
}
foreach ($where as $whereItem) {
$queryBuilder->where($whereItem);
}
}
if (null !== $fields) {
if (is_array($fields)) {
foreach ($fields as $field => $value) {
if (is_array($value)) {
if (isset($value['case'])) {
$case = self::selectCase($field, $value['case'], $queryBuilder->getAlias());
$queryBuilder->from(null, [$field . "_case" => $case]);
unset($fields[$field]);
$fields[$field . "_case"] = $value['title'];
}
}
}
}
}
if ($category) {
$queryBuilder->columnsJoinOne($category, 'category');
if (null == $category_order) {
$queryBuilder->orderBy(['category', 'name']);
} else {
$queryBuilder->orderBy($category_order);
}
$data = ($result = $queryBuilder->getQuery()->execute()) === null ? [] : $result;
$options = [];
foreach ($data as $item) {
if ($item['id'] === 0) {
continue;
}
$category = trim($item['category']) ? $item['category'] : $emptyCategory;
$options[$category][$item['id']] = $item;
}
} else {
$queryBuilder->orderNatural();
$queryBuilder->orderBy('name');
$data = ($result = $queryBuilder->getQuery()->execute()) === null ? [] : $result;
$options = array();
foreach ($data as $item) {
if ($item['id'] === 0) {
continue;
}
$options[$category][$item['id']] = $item;
}
if ($multiselect) {
//$options = [0 => $options];
}
}
return $options;
}
