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