/**
* 주어진 db query를 실행한다.
*
* @param Builder $query 질의
* @param int|int[]|stdClass $navigation 검색시 사용할 navigation(page, perPage, sort, order) 정보
*
* @return array
*/
protected function executeQuery($query, $navigation = null)
{
// set default
$order = $this->defaultOrder;
$sort = $this->defaultSort;
$perPage = $this->defaultPerPage;
$page = null;
if (is_array($navigation)) {
list($page, $perPage) = $navigation;
} elseif (is_object($navigation)) {
$page = data_get($navigation, 'page', $page);
$perPage = data_get($navigation, 'perPage', $perPage);
$order = data_get($navigation, 'order', $order);
$sort = data_get($navigation, 'sort', $sort);
}
if ($sort !== null) {
$query->orderBy($sort, $order);
}
if ($navigation === null) {
$collection = $query->get();
} elseif ($page !== null) {
$collection = $query->forPage($page, $perPage);
} else {
$collection = $query->paginate($perPage);
}
return $collection;
}
/**
* {@inheritdoc}
*/
public function order(Builder $builder, $direction)
{
$builder->orderBy($this->id, $direction);
return $this;
}
public function build()
{
if (is_string($this->source) && strpos(" ", $this->source) === false) {
//tablename
$this->type = "query";
$this->query = $this->table($this->source);
$this->total_rows = $this->query->count();
} elseif (is_a($this->source, "\\Illuminate\\Database\\Eloquent\\Model") || is_a($this->source, "\\Illuminate\\Database\\Eloquent\\Builder")) {
$this->type = "model";
$this->query = $this->source;
$this->total_rows = $this->query->count();
} elseif (is_array($this->source)) {
$this->type = "array";
$this->total_rows = count($this->source);
}
//exception
//offset and pagination setup/detect
$config = array('cid' => $this->cid, 'total_items' => $this->total_rows, 'items_per_page' => $this->per_page, 'num_links' => round($this->num_links / 2), 'hash' => $this->hash, 'url' => $this->url, 'current_page' => $this->current_page);
$this->pagination = new \Rapyd\Helpers\Pagination($config);
$offset = $this->pagination->offset();
$this->limit($this->per_page, $offset);
//build orderby urls
$this->orderby_uri_asc = $this->app->url->remove('pag' . $this->cid)->remove('reset' . $this->cid)->append('orderby' . $this->cid, array("-field-", "asc"))->get() . $this->hash;
$this->orderby_uri_desc = $this->app->url->remove('pag' . $this->cid)->remove('reset' . $this->cid)->append('orderby' . $this->cid, array("-field-", "desc"))->get() . $this->hash;
//detect orderby
$orderby = $this->app->url->value("orderby" . $this->cid);
if ($orderby) {
$this->orderby_field = $orderby[0];
$this->orderby_direction = $orderby[1];
$this->orderby($this->orderby_field, $this->orderby_direction);
}
//build subset of data
switch ($this->type) {
case "array":
//orderby
if (isset($this->orderby)) {
list($field, $direction) = $this->orderby;
$column = array();
foreach ($this->source as $key => $row) {
$column[$key] = $row[$field];
}
if ($direction == "asc") {
array_multisort($column, SORT_ASC, $this->source);
} else {
array_multisort($column, SORT_DESC, $this->source);
}
}
//limit-offset
if (isset($this->limit)) {
$this->source = array_slice($this->source, $this->limit[1], $this->limit[0]);
}
$this->data = $this->source;
break;
case "query":
case "model":
//orderby
if (isset($this->orderby)) {
$this->query = $this->query->orderBy($this->orderby[0], $this->orderby[1]);
}
//limit-offset
if (isset($this->limit)) {
$this->query = $this->query->skip($this->pagination->offset())->take($this->per_page);
}
$this->data = $this->query->get();
break;
}
return $this;
}
/**
* @param string $column
* @param string $direction
* @return $this
*/
public function applyOrderBy($column, $direction = 'asc')
{
/**
* Save to conditons.
*/
$this->addCondition('order by', [$column, $direction]);
$this->model = $this->model->orderBy($column, $direction);
return $this;
}
/**
* {@inheritdoc}
*/
public function order(Builder $builder, $direction)
{
if ($this->columnClause !== null) {
$builder->orderBy($this->columnClause, $direction);
}
return $this;
}
/**
* flush events, build pagination and sort items
*
* @return $this
*/
public function build()
{
BurpEvent::flush('dataset.sort');
BurpEvent::flush('dataset.page');
$this->paginator = Paginator::make($this->total_rows, $this->per_page, $this->page);
$offset = $this->paginator->offset();
$this->limit($this->per_page, $offset);
if (is_array($this->source)) {
//orderby
if (isset($this->orderby)) {
list($field, $direction) = $this->orderby;
array_orderby($this->source, $field, $direction);
}
//limit-offset
if (isset($this->limit)) {
$this->source = array_slice($this->source, $this->limit[1], $this->limit[0]);
}
$this->data = $this->source;
} else {
//orderby
if (isset($this->orderby)) {
$this->query = $this->query->orderBy($this->orderby[0], $this->orderby[1]);
}
//limit-offset
if (isset($this->per_page)) {
$this->query = $this->query->skip($offset)->take($this->per_page);
}
$this->data = $this->query->get();
}
return $this;
}
public function execute(Builder $query)
{
foreach ($this->getValuesIterator() as $orderBy) {
$query->orderBy($orderBy->getName(), $orderBy->getValue());
}
return $query;
}
/**
*
* @param string $value
*/
public function orderBy($value = [])
{
//if array is not empty
if (count($value) > 0) {
$this->query = $this->query->orderBy($value[0], $value[1]);
}
return $this;
}
/**
* Order the results by the given column in the given direction.
*
* @param string $key
* @param string $direction
* @return $this
* @throws TableNotSetException
*/
public function orderBy($key, $direction = 'asc')
{
if (!$this->query) {
throw new TableNotSetException("You must set a database table to get results from.");
}
$this->query = $this->query->orderBy($key, $direction);
return $this;
}
/**
* Setup basic query string filter to eloquent or query builder.
*
* @param \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder $query
* @param array $input
*
* @return \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder
*/
protected function setupBasicQueryFilter($query, array $input = [])
{
$orderBy = $this->getBasicQueryOrderBy($input);
$direction = $this->getBasicQueryDirection($input);
$columns = isset($input['columns']) ? $input['columns'] : null;
if (is_array($columns) && $this->isColumnExcludedFromFilterable($orderBy, $columns)) {
return $query;
}
!empty($orderBy) && $query->orderBy($orderBy, $direction);
return $query;
}
/**
* @param \Illuminate\Database\Query\Builder|\Minhbang\Kit\Extensions\Model $query
* @param bool $position
*
* @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
*/
protected function optionAppliedPaginate($query, $position = false)
{
list($column, $direction) = $this->options->column('sort');
if ($column) {
$query->orderBy($column, $direction);
} else {
if ($position) {
$query->orderPosition();
}
}
return $query->paginate($this->options->get('page_size', 6));
}
/**
* A reusable query to pass params
*
* @param Builder $query
* @param array $queryFilter
*
* @return Builder
*/
public function scopeQueryWithParams($query, $queryFilter)
{
$limit = isset($queryFilter['limit']) ? $queryFilter['limit'] : 10;
$perPage = isset($queryFilter['perPage']);
$orderBy = isset($queryFilter['orderBy']) ? $queryFilter['orderBy'] : 'created_at';
$sortOrder = isset($queryFilter['sortOrder']) ? $queryFilter['sortOrder'] : 'desc';
$query->orderBy($orderBy, $sortOrder);
if ($perPage) {
return $query->paginate($perPage);
}
return $query->take($limit);
}
/**
* @param array $orders
* @param string $v 兼容原来的方法
* @return $this
*/
public function orderBy($orders = [], $v = 'desc')
{
if (empty($orders)) {
return $this;
}
if (is_array($orders)) {
foreach ($orders as $k => $v) {
$this->operator->orderBy($k, $v);
}
} else {
$this->operator->orderBy($orders, $v);
}
return $this;
}
/**
* Datatable ordering
*
* @return null
*/
protected function ordering()
{
if (array_key_exists('order', $this->input) && count($this->input['order']) > 0) {
$columns = $this->cleanColumns($this->aliased_ordered_columns);
for ($i = 0, $c = count($this->input['order']); $i < $c; $i++) {
$order_col = (int) $this->input['order'][$i]['column'];
if (isset($columns[$order_col])) {
if ($this->input['columns'][$order_col]['orderable'] == "true") {
$this->query->orderBy($columns[$order_col], $this->input['order'][$i]['dir']);
}
}
}
}
}
/**
* Return the filtered, ordered and paginated rows from the crud's custom query
*
* @param Builder $query
* @param int $start
* @param int $length
* @param array $filters
* @param null $order
* @return array
*/
public static function getFilteredQueryListRows(Builder $query, $start, $length, $filters = [], $order = null)
{
// Get the total count of rows with no filters and pagination
$countTotal = $query->count();
// Check if any filters were submitted
if ($filters) {
foreach ($filters as $filterName => $filterValue) {
// Check if there is any filter operator in the field string
$operation = SQL::findOperationWhere($filterValue);
$query->where($filterName, $operation['operation'], $operation['text']);
}
}
// Get the total count of rows filtered, but without pagination
$countFiltered = $query->count();
// Execute pagination
$query->skip($start)->take($length);
// Check if an order by was submitted
if ($order) {
$query->orderBy($order['name'], $order['dir']);
}
return ['rows' => $query->get(), 'count_filtered' => $countFiltered, 'count_total' => $countTotal];
}
/**
* @param QueryBuilder $query
*
* @return QueryBuilder
*/
public function scopeSorted($query)
{
$sortableField = static::getSortableField();
return $query->orderBy($sortableField);
}
/**
* @param Builder|QueryBuilder $queryBuilder
* @param $order
*/
protected function buildOrderBySingle($queryBuilder, $order)
{
$order = strtolower($order);
$orderBy = str_replace([' asc', ' desc'], '', $order);
$orderDirection = ends_with($order, ' desc') ? 'desc' : 'asc';
$queryBuilder->orderBy($orderBy, $orderDirection);
}
/**
* @param \Illuminate\Database\Query\Builder $query
*
* @return \Illuminate\Database\Query\Builder
*/
public function scopeSorted($query)
{
return $query->orderBy('position');
}
/**
* Add order by to query builder
* @param \Illuminate\Database\Query\Builder $query object is by reference
*/
protected function addOrderByQuery($query)
{
if (isset($this->orderBy)) {
$query->orderBy($this->orderBy['column'], $this->orderBy['direction']);
} else {
// Default order by
if (isset($this->attributes['order_by'])) {
$query->orderBy($this->attributes('order_by'), $this->attributes('order_by_dir'));
}
// Else omit the order by statement completely which will use tables clustered index as order
}
}
/**
* @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
*/
public function loadLatest()
{
return $this->productResource->orderBy('updated_at', 'desc')->with(Product::standardRelations())->paginate();
}
/**
* @param \Illuminate\Database\Query\Builder $query
* @param array $orderCandidates
* @param string $orderDefault
* @param string $order
* @param string $direction
* @param integer $offset
* @param integer $limit
* @return array
*/
protected function getWithQueryBuilder($query, $orderCandidates = [], $orderDefault = "id", $order, $direction, $offset, $limit)
{
$order = strtolower($order);
$direction = strtolower($direction);
$offset = intval($offset);
$limit = intval($limit);
$order = in_array($order, $orderCandidates) ? $order : strtolower($orderDefault);
$direction = in_array($direction, ['asc', 'desc']) ? $direction : 'asc';
if ($limit <= 0) {
$limit = 10;
}
if ($offset < 0) {
$offset = 0;
}
return $query->orderBy($order, $direction)->offset($offset)->limit($limit)->get();
}
/**
* Add an "order by" clause by translated column to the query.
*
* @param string $column
* @param string $direction
* @return $this
*/
public function orderBy($column, $direction = 'asc')
{
if (in_array($column, $this->model->translatableAttributes())) {
return $this->orderByTranslated($column, $direction);
}
return parent::orderBy($column, $direction);
}
/**
* order by
* @param $attribute
* @param bool $desc 默认升序(false), 如需降序, 传入 true
* @return static
*/
public function orderBy($attribute, bool $desc = false)
{
$this->original->orderBy($attribute, $desc ? 'desc' : 'asc');
return $this;
}
/**
* Метод указывает сортировку данных
*
* @param string $sortName
* @param string $orderBy
*/
protected function filterOrderBy($sortName, $orderBy)
{
if ($sortName) {
$this->_query->orderBy($this->_query->getModel()->getTable() . '.' . $sortName, $orderBy);
}
}
/**
* Let's be nice and provide an ordered scope.
*
* @param \Illuminate\Database\Query\Builder $query
*
* @return \Illuminate\Database\Query\Builder
*/
public function scopeOrdered(\Illuminate\Database\Query\Builder $query)
{
return $query->orderBy($this->determineOrderColumnName());
}
/**
* Add an "order by" clause to the query.
*
* @param string $column
* @param string $direction
* @return $this
* @static
*/
public static function orderBy($column, $direction = 'asc')
{
return \Illuminate\Database\Query\Builder::orderBy($column, $direction);
}
/**
* Perform ordering
*
* @return mixed
*/
public function ordering()
{
foreach ($this->request->getOrder() as $key => $reverse) {
$this->query->orderBy($key, $reverse ? 'desc' : 'asc');
}
}
public function build()
{
if (is_string($this->source) && strpos(" ", $this->source) === false) {
//tablename
$this->type = "query";
$this->query = $this->table($this->source);
} elseif (is_a($this->source, "\\Illuminate\\Database\\Eloquent\\Model")) {
$this->type = "model";
$this->query = $this->source;
$this->key = $this->source->getKeyName();
} elseif (is_a($this->source, "\\Illuminate\\Database\\Eloquent\\Builder")) {
$this->type = "model";
$this->query = $this->source;
$this->key = $this->source->getModel()->getKeyName();
} elseif (is_a($this->source, "\\Illuminate\\Database\\Query\\Builder")) {
$this->type = "model";
$this->query = $this->source;
} elseif (is_a($this->source, "\\Zofe\\Rapyd\\DataFilter\\DataFilter")) {
$this->type = "model";
$this->query = $this->source->query;
if (is_a($this->query, "\\Illuminate\\Database\\Eloquent\\Model")) {
$this->key = $this->query->getKeyName();
} elseif (is_a($this->query, "\\Illuminate\\Database\\Eloquent\\Builder")) {
$this->key = $this->query->getModel()->getKeyName();
}
} elseif (is_array($this->source)) {
$this->type = "array";
} else {
throw new DataSetException(' "source" must be a table name, an eloquent model or an eloquent builder. you passed: ' . get_class($this->source));
}
//build orderby urls
$this->orderby_uri_asc = $this->url->remove('page' . $this->cid)->remove('reset' . $this->cid)->append('ord' . $this->cid, "-field-")->get() . $this->hash;
$this->orderby_uri_desc = $this->url->remove('page' . $this->cid)->remove('reset' . $this->cid)->append('ord' . $this->cid, "--field-")->get() . $this->hash;
//detect orderby
$orderby = $this->url->value("ord" . $this->cid);
if ($orderby) {
$this->orderby_field = ltrim($orderby, "-");
$this->orderby_direction = $orderby[0] === "-" ? "desc" : "asc";
if ($this->canOrderby($this->orderby_field)) {
$this->orderBy($this->orderby_field, $this->orderby_direction);
}
}
//build subset of data
switch ($this->type) {
case "array":
//orderby
if (isset($this->orderby)) {
list($field, $direction) = $this->orderby;
$column = array();
foreach ($this->source as $key => $row) {
$column[$key] = is_object($row) ? $row->{$field} : $row[$field];
}
if ($direction == "asc") {
array_multisort($column, SORT_ASC, $this->source);
} else {
array_multisort($column, SORT_DESC, $this->source);
}
}
$limit = $this->limit ? $this->limit : 100000;
$current_page = $this->url->value('page' . $this->cid, 0);
$offset = max($current_page - 1, 0) * $limit;
$this->data = array_slice($this->source, $offset, $limit);
$this->paginator = new LengthAwarePaginator($this->data, count($this->source), $limit, $current_page, ['path' => Paginator::resolveCurrentPath(), 'pageName' => "page" . $this->cid]);
break;
case "query":
case "model":
//orderby
if (isset($this->orderby)) {
$this->query = $this->query->orderBy($this->orderby[0], $this->orderby[1]);
}
//limit-offset
if (isset($this->limit)) {
$this->paginator = $this->query->paginate($this->limit, ['*'], 'page' . $this->cid);
$this->data = $this->paginator;
} else {
$this->data = $this->query->get();
}
break;
}
return $this;
}
/**
* @return array
*/
public function resolveAll()
{
return $this->eloquent->orderBy('id')->get()->toArray();
}
/**
* Takes the supplied $selectedItems mixed value and formats it to a usable array
*
* @param \Illuminate\Database\Query\Builder $query
* @param array $selectedItems
* @param \Frozennode\Administrator\Fields\Field $fieldObject
* @param string $relatedKeyTable
*
* @return array
*/
public function filterQueryBySelectedItems(QueryBuilder &$query, array $selectedItems, Field $fieldObject, $relatedKeyTable)
{
$query->whereIn($relatedKeyTable, $selectedItems);
//if this is a BelongsToMany and a sort field is set, order it by the sort field
if ($fieldObject->getOption('multiple_values') && $fieldObject->getOption('sort_field'))
{
$query->orderBy($fieldObject->getOption('sort_field'));
}
//otherwise order it by the name field
else
{
$query->orderBy($fieldObject->getOption('name_field'));
}
}
