/**
* Fired just before querying
* for table entries.
*
* @param Builder $query
*/
public function onQuerying(Builder $query)
{
$uploaded = $this->getUploaded();
$query->whereIn('id', $uploaded ?: [0]);
$query->orderBy('updated_at', 'ASC');
$query->orderBy('created_at', 'ASC');
}
/**
* Adds a OrderBy to the query
*
* @param string
* @param string
* @return $this
*/
public function orderBy($column, $order = 'asc')
{
if ($column != null) {
$this->query->orderBy($column, $order);
}
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;
}
/**
* Order by columns based on parameters
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string $orderBy
* @return \Illuminate\Database\Eloquent\Builder
*/
public static function orderByColumns(Builder $query, $orderBy)
{
if (Request::has('orderbycolumn') == TRUE && Request::has('orderbytype') == TRUE) {
$query->orderBy(Request::input('orderbycolumn'), Request::input('orderbytype'));
} else {
foreach ($orderBy as $orderByColumn => $orderByType) {
$query->orderBy($orderByColumn, $orderByType);
}
}
return $query;
}
/**
* Sort
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param string|null $sort Optional sort string
*
* @return \Illuminate\Database\Query\Builder
*/
public function scopeSort(Builder $builder, $sort = null)
{
if ((is_null($sort) || empty($sort)) && Input::has($this->getSortParameterName())) {
$sort = Input::get($this->getSortParameterName());
}
if (!is_null($sort)) {
$sort = explode(',', $sort);
foreach ($sort as $field) {
$field = trim($field);
$order = 'asc';
switch ($field[0]) {
case '-':
$field = substr($field, 1);
$order = 'desc';
break;
case '+':
$field = substr($field, 1);
break;
}
$field = trim($field);
if (in_array($field, $this->getSortable())) {
$builder->orderBy($field, $order);
}
}
}
}
/**
* Fired just before querying
* for table entries.
*
* @param Builder $query
*/
public function onQuerying(Builder $query)
{
$uploaded = $this->getUploaded();
if ($fieldType = $this->getFieldType()) {
/*
* If we have the entry available then
* we can determine saved sort order.
*/
$entry = $fieldType->getEntry();
$table = $fieldType->getPivotTableName();
if ($entry->getId() && !$uploaded) {
$query->join($table, $table . '.file_id', '=', 'files_files.id');
$query->where($table . '.entry_id', $entry->getId());
$query->orderBy($table . '.sort_order', 'ASC');
} else {
$query->whereIn('id', $uploaded ?: [0]);
}
} else {
/*
* If all we have is ID then just use that.
* The JS / UI will be handling the sort
* order at this time.
*/
$query->whereIn('id', $uploaded ?: [0]);
}
}
/**
* @param Builder $src
* @param OperationInterface|SortOperation $operation
* @return mixed
*/
public function process($src, OperationInterface $operation)
{
$field = $operation->getField();
$order = $operation->getOrder();
$src->orderBy($field, $order);
return $src;
}
/**
* Adds a sort scope.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string $column
* @param string $direction
*
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeSort(Builder $query, $column, $direction)
{
if (!in_array($column, $this->sortable)) {
return $query;
}
return $query->orderBy($column, $direction);
}
protected function parseSort(array $params, Query $query)
{
if (!isset($params['sort'])) {
return $query->orderBy('id', 'desc');
}
$column = strtolower($params['sort']);
$direction = 'asc';
if (strpos($params['sort'], '-') === 0) {
$direction = 'desc';
$column = ltrim($column, '-');
}
if (!in_array($column, Unit::getColumns())) {
return $query->orderBy('id', 'desc');
}
return $query->orderBy($column, $direction);
}
public function apply(Builder $builder, Model $model)
{
$table = $model->getTable();
$columns = $model->getDefaultOrderableColumns() ?: [];
foreach ($columns as $column => $order) {
$builder->orderBy("{$table}.{$column}", $order);
}
}
/**
* Apply scope on the query
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
*/
public function apply(Builder $builder, Model $model)
{
$columns = $model->getQualifiedOrderByColumns();
foreach ($columns as $column => $direction) {
$builder->orderBy($column, $direction);
}
$this->addUnordered($builder);
}
/**
* @param $field
* @param $direction
*
* @return $this
*/
protected function sortBy($field, $direction)
{
if (!in_array($direction, [DoplioDbTable::SORT_ASC, DoplioDbTable::SORT_DESC])) {
throw new \InvalidArgumentException(sprintf('%s is an invalid sort direction'), $direction);
}
$this->specificationQuery->orderBy($field, $direction);
return $this;
}
/**
* Orders a given query.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeOrder($query)
{
if ($this->orderBy[0] !== null and $this->orderBy[1] !== null) {
return $query->orderBy($this->orderBy[0], $this->orderBy[1]);
}
// If the orderBy values are invalid, let's just return the query for now.
return $query;
}
/**
* Applies criterion to query.
*
* @param Builder $builder query builder
*/
public function apply(Builder $builder)
{
$sortMethod = 'sort' . studly_case($this->getField());
if (method_exists($builder->getModel(), $sortMethod)) {
call_user_func_array([$builder->getModel(), $sortMethod], [$builder, $this->getOrder()]);
} else {
$builder->orderBy($this->getField(), $this->getOrder());
}
}
/**
* Set the direction of the query.
*
* @param string|null $direction The date direction to order by
* @return Parser The parser instance itself
*/
public function direction($direction = null)
{
if ($direction == 'Latest') {
$this->query->orderBy('created_at', 'desc');
} elseif ($direction == 'Oldest') {
$this->query->orderBy('created_at', 'asc');
}
return $this;
}
public function newQuery()
{
$this->query = $this->getNew()->newQuery();
foreach ($this->orderBy as $column => $dir) {
$this->query->orderBy($column, $dir);
}
$this->applyScope();
return $this;
}
/**
* @param string $sessionKey
* @param Builder $query
* @return Builder
*/
public static function updateQuery($sessionKey, Builder $query)
{
foreach (Session::get($sessionKey, []) as $field => $clause) {
if ($clause == 'asc' || $clause == 'desc') {
$query->orderBy($field, $clause);
}
}
return $query;
}
/**
* 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;
}
/**
* Apply the scope to a given Eloquent query builder.
* @param Builder $builder
* @param Model $model
*/
public function apply(Builder $builder, Model $model)
{
$field = 'sort';
$order = 'asc';
if (property_exists(get_class($model), 'autosort')) {
$autosort = (array) $model->autosort;
$field = $autosort[0];
$order = isset($autosort[1]) ? $autosort[1] : $order;
}
$builder->orderBy($field, $order);
}
/**
* Get a new query builder instance with the applied
* the order by and scopes.
*
* @param bool $skipOrdering
*
* @return self
*/
public function newQuery($skipOrdering = false)
{
$this->query = $this->getNew()->newQuery();
// Apply order by
if ($skipOrdering === false) {
foreach ($this->orderBy as $column => $dir) {
$this->query->orderBy($column, $dir);
}
}
$this->applyScope();
return $this;
}
public function build()
{
$request = $this->request;
$needle = $request->needle();
if ($needle && $request->searchable) {
$this->qb->where(function ($subQB) use($needle, $request) {
foreach ($request->searchable as $column) {
$subQB->orWhere($column, 'LIKE', "%{$needle}%");
}
});
}
$request->modifyQuery($this->qb);
$this->qb->orderBy($request->orderBy(), $request->sortBy());
$count = $this->qb->count();
$this->qb->take($request->perPage());
$this->qb->offset($request->offset());
$paginator = new LengthAwarePaginator($request->items($this->qb), $count, $request->perPage(), $request->page());
$paginator->request = $request;
$paginator->appends($request->query());
$this->paginator = $paginator;
}
/**
* Set query order
*
* @return $this
*/
public function order()
{
$by = $this->getOrderBy();
$dir = $this->getOrderDir();
if ($by instanceof Closure) {
$by($this->getOrderByField(), $dir, $this->query);
} else {
if ($by) {
$this->query->orderBy($by, $dir);
}
}
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']);
}
}
}
}
}
/**
* @param JsonApiSerializer $serializer
* @param Builder $builder
* @param Model $model
*
* @return Builder
*/
protected static function sort(JsonApiSerializer $serializer, Builder $builder, Model $model)
{
$mapping = $serializer->getTransformer()->getMappingByClassName(get_class($model));
$sorts = RequestFactory::create()->getSortDirection();
if (!empty($sorts)) {
$aliased = $mapping->getAliasedProperties();
$sortsFields = str_replace(array_values($aliased), array_keys($aliased), array_keys($sorts));
$sorts = array_combine($sortsFields, array_values($sorts));
foreach ($sorts as $field => $direction) {
$builder->orderBy($field, $direction === 'ascending' ? 'ASC' : 'DESC');
}
}
return $builder;
}
/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return Builder
*/
public function apply(Builder $builder, Model $model)
{
$defaultSort = $model->getDefaultSort() ?? null;
if (!$defaultSort) {
return $builder;
}
// if multidimensional we have multiple orderBy's
if (!is_array($defaultSort[0])) {
return $builder->orderBy(...$defaultSort);
}
foreach ($defaultSort as $orderBy) {
$builder = $builder->orderBy(...$orderBy);
}
return $builder;
}
/**
* Set clauses on the query builder
*
* @return $this
*/
protected function setClauses()
{
foreach ($this->wheres as $where) {
$this->query->where($where['column'], $where['operator'], $where['value']);
}
foreach ($this->whereIns as $whereIn) {
$this->query->whereIn($whereIn['column'], $whereIn['values']);
}
foreach ($this->orderBys as $orders) {
$this->query->orderBy($orders['column'], $orders['direction']);
}
if (isset($this->take) and !is_null($this->take)) {
$this->query->take($this->take);
}
if (isset($this->skip) and !is_null($this->skip)) {
$this->query->skip($this->skip);
}
if (isset($this->paginate) and !is_null($this->paginate)) {
$this->query->paginate($this->paginate);
}
return $this;
}
/**
* @param Builder $query
* @return Builder
*/
public function scopeRandom(Builder $query)
{
return $query->orderBy('RAND()');
}
/**
* Sort the query.
*
* @param Builder $builder
* @param string $direction
*/
public function scopeSorted(Builder $builder, $direction = 'asc')
{
$builder->orderBy('sort_order', $direction);
}
/**
* 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(EloquentBuilder &$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'));
} else {
$query->orderBy($fieldObject->getOption('name_field'));
}
}
/**
* Let's be nice and provide an ordered scope.
*
* @param \Illuminate\Database\Eloquent\Builder $query
*
* @return \Illuminate\Database\Query\Builder
*/
public function scopeOrdered(\Illuminate\Database\Eloquent\Builder $query)
{
return $query->orderBy($this->determineOrderColumnName());
}