kyslik/column-sortable

Package for handling column sorting in Laravel 5/6/7/8

column-sortable's Issues

More config...

Support for
change parameters name sort, order, page.

from #14 (comment)

Readme structure, Wiki page for package.

Clean up readme file and create wiki page(s) for package.

FatalErrorException Call to undefined method Kyslik\ColumnSortable\Sortable::link()

When upgrading from version 5.0.0 to version 5.1.3 an error is

FatalErrorException in 33617f6cdbd88d82f918e5066c0c635838aff197.php line 104:
Call to undefined method Kyslik \ ColumnSortable \ Sortable :: link ()

include column title with request

Hi,

I am trying to show the sort criteria and It would be nice if the 'title' used in the @sortablelink() was passed in the request in addition to sort and order so that it can be shown.

I am currently using ucfirst($request->sort), however this only works if the column name is the same as the title which in some cases will produce differences

L5.2 aliasing does not work

How to use trans with @sortablelink

Hello,

No sure if it's possible but I would like to use localization with your directive @sortablelink on my headers.
This is not working:

@sortablelink('user.username',"{{ trans('user.username') }}")

Any way to do it?

Thank you

BadMethodCallException in Builder.php

I'm working on a laravel 5.2 project,
when i put
$users = $user->sortable(['id' => 'desc'])->paginate(10);
or any other code with the sortable() method I get this error:

BadMethodCallException in Builder.php line 2161:
Call to undefined method Illuminate\Database\Query\Builder::sortable().

Is this a bug for laravel 5.2 or am I doing something wrong?
I'm new to Laravel, but this package seems very good for my project!

Sorting hasOne with select and avg.

In Model

public function user() 
{
    return $this->hasOne('App\User', 'id', 'user_id')
                        ->select('id', 'fullname', 'avatar');
}

public function rating() 
{ 
    return $this->hasOne('App\Rating', 'recipe_id', 'id')
                        ->selectRaw('recipe_id, avg(rating) as avg, count(rating) as count')
                        ->groupBy('recipe_id');

}

In Controller

    $recipes = Recipe::select(array('id', 'user_id', 'name', 'created_at'))
                        ->sortable(['created_at' => 'desc'])
                        ->with('user', 'rating')
                        ->paginate(10, ['*'], 'p');

?sort=created_at&order=asc queries:

select * from users where users.id = '2' limit 1
select count(*) as aggregate from recipes
select id, user_id, name, created_at from recipes order by recipes.created_at asc limit 10 offset 0
select id, fullname, avatar from users where users.id in ('3', '2', '1')
select recipe_id, avg(rating) as avg, count(rating) as count from ratings where ratings.recipe_id in ('3', '4', '2', '1', '7', '6', '5', '20', '19', '18') group by recipe_id

?sort=rating.avg&order=asc queries:

select * from users where users.id = '2' limit 1
select column_name from information_schema.columns where table_schema = 'vk_app' and table_name = 'ratings'
select count(*) as aggregate from recipes inner join ratings on recipes.id = ratings.recipe_id
select recipes.* from recipes inner join ratings on recipes.id = ratings.recipe_id limit 10 offset 0
select id, fullname, avatar from users where users.id in ('2')
select recipe_id, avg(rating) as avg, count(rating) as count from ratings where ratings.recipe_id in ('1') group by recipe_id

Return only one recepe, that have a rating.

?sort=user.fullname&order=asc queries:

select * from users where users.id = '2' limit 1
select column_name from information_schema.columns where table_schema = 'vk_app' and table_name = 'users'
select count(*) as aggregate from recipes inner join users on recipes.id = users.id
select recipes.* from recipes inner join users on recipes.id = users.id order by users.fullname asc limit 10 offset 0
select id, fullname, avatar from users where users.id in ('2', '3')
select recipe_id, avg(rating) as avg, count(rating) as count from ratings where ratings.recipe_id in ('1', '3', '2') group by recipe_id

Set user ids instead recipe ids.

Belongs to relations

Here some idea about BelongsTo relationship

    private function queryJoinBuilder($query, $relation)
    {
        $relatedModel = $relation->getRelated();
        $relatedKey = $relation->getForeignKey(); // table.key
        $relatedTable = $relatedModel->getTable();
        $relatedPk = $relatedTable . '.' . $relatedModel->primaryKey;

        $parentModel = $relation->getParent();
        $parentTable = $parentModel->getTable();
        $parentKey = $parentTable . '.' . $parentModel->primaryKey; // table.key


        if ($relation instanceof HasOne)
        {
            return $query->select($parentTable . '.*')->join($relatedTable, $parentKey, '=', $relatedKey);
        }

        if ($relation instanceof BelongsTo)
        {
            return $query->select($parentTable . '.*')->join($relatedTable, $relatedPk, '=', $relatedKey);
        }

    }

Merge query parameters

Hi.
I'm using Eloquence searchable with your package.
I have url like this: /admin/products?q=keyword
But when I want to order results with this code:

 @sortablelink ('status', 'Status')

It generating url like /admin/products?sort=status&order=desc

Can you fix it, please?
I temporary fixed it by using this code instead:

{{ route( 'admin.products', array_merge($request->except('sort', 'order'), ['sort' => 'id', 'order' => $request->order == 'desc' ? 'asc' : 'desc' ]) ) }}

But it's ugly and very large.

Blade directive 2nd parameter while relational sorting - bad anchor text

Relation one-to-one sorting while using blade directive @sortablelink('detail.phone_number') creates bad anchor text Detail.phone_number instead of Phone number - or Phone_number

Possible to support polymorphic relationship?

I am considering addressing this in a PR. If so would it be accepted, or maybe you'd rather do it yourself?

Hi i have a question. how to deal with the dynamic attributes?

I want to know how to use it with dynamic attributes. e.g:

products

id	name	price
1	p1	300
2	p2	400

product_attributes

product_id	attribute_name	attribute_value
1	color	red
1	weight	20
2	size	2
2	color	green

In some scenarios, color is sortable in others weight. How to deal with this?

Show the proper icon on page load, so that user knows what column is sorted by default

Hi, I have a little issue, hope you can help :-)
Given a query like:

$users = $user->sortable(['name'])->paginate(10);

And a table that has of course a column sortable by name:

@sortablelink ('name', 'Customer Name')

I'd like to show the proper icon when the page loads (in this case, the fa-sort-alpha-asc icon instead of the generic fa-sort), so that user knows what column is sorted by default

Thank you :-)

Sorting a column with relationship can shrink result

Please change join type in queryJoinBuilder to "LEFT JOIN". (currently "INNER JOIN")
Currently the (sorted) result contains only rows which have foreign rows.

Example:

user:

id	name	email
1	John Doe	[email protected]
2	Lucy Donut	[email protected]

user_detail:

user_id	phone
1	12345678

Sorting by phone asc/desc will only show John Doe

Loosing "page" parameter on the QUERY_STRING after sort a column

I did read this #14 but is not clear to me about the usage (I couldn't find anything in the README). This is what I have:

• The route is defined at routes/web.php as follow:

Route::resource('bands', 'BandController');

• The model is defined at app/Band.php as follow:

use Kyslik\ColumnSortable\Sortable;

class Band extends Model
{
    use Sortable;
    public $sortable = ['name'];

    public function albums()
    {
        return $this->hasMany(Album::class);
    }
}

• The method index of the controller Band is defined at app/Http/Controllers/BandController.php as follow:

    public function index(Band $band)
    {
        $bands = $band->sortable()->paginate(10);
        
        return view('bands.index')->withBands($bands);
    }

• And finally the template is defined at resources/views/bands/index.blade.php with the following code:

<table class="table table-bordered">
        <tr>
            <th>@sortablelink('name', 'Name')</th>
            <th>Start Date</th>
        </tr>
        @foreach ($bands as $band)
            <tr>
                <td>{{ $band->name }}</td>
                <td>{{ $band->start_date }}</td>
            </tr>
        @endforeach
    </table>
    {!! $bands->appends(\Request::except('page'))->render() !!}

When I click on the Name column the query is being executed and table is rendered with proper values but I am missing pagination parameter on the QUERY_STRING so everytime it goes back to page=1, why? Why I didn't see on docs?

Method sortable does not exist.

I used your full example and got this error -

BadMethodCallException in Macroable.php line 74:
Method sortable does not exist.

trans Thai string and version > 5.0.3 returns blank

Hi,

I have an app with Thai language (and trans) but since version 5.0.4, I get empty headers for my columns.

• If I have only Thai characters the result is an empty string:
  name' => 'ชื่อ' => <th class="sorting_disabled" rowspan="1" colspan="1" style="width: 242px;"><a href="url&amp;order=desc"></a> <i class="fa fa-sort"></i></th>
• If I add some latin letter at the beginning, the result is ok with a capital for the first letter.
  'name' => 'aa-ชื่อ' => <th class="all sorting_disabled" rowspan="1" colspan="1" style="width: 162px;"><a href="url&amp;order=desc">Aa-ชื่อ</a> <i class="fa fa-sort"></i></th>
• If I use some letter with accent, the result is good without capital letter
  'name' => 'âชื่อ' => <th class="all sorting_disabled" rowspan="1" colspan="1" style="width: 138px;"><a href="url&amp;order=desc">âชื่อ</a> <i class="fa fa-sort"></i></th>

Thanks for your help.

Remark 1: This problem happens with a windows server, not with my dev Homestead.

column-sortable not working with withCount()

This package seems not to work correctly with the new laravel function withCount().

I have models "Entry" and "Vote". One Entry can have many Votes, so in Entry

public function votes() {
    return $this->hasMany('App\Vote');
}

Entry::withCount('votes') returns the number of votes correctly in votes_count, but trying to sort by votes_count (via sortable) results in an error:

Unknown column 'entries.votes_count' in 'order clause'.

I was able to make it work by overloading the sort for "votes_count":

public function votesCountSortable($query, $direction) {
        return $query->orderBy('votes_count', $direction);
}

(simply making sure the name of the order column is only votes_count without a table name).

Note: the name of the function: votes_count in camel-case translate to votesCount.

Using laravel accessor for sortable

Hi,

is it possible to use a property from Laravels accessor for sorting? I could not get it to work.

Example:
Model:
public function getFullnameAttribute() { return $this->firstname . ' ' . $this->lastname; }

that gives me the property "fullname" and now i can use it in my views. But when i use
<th>@sortablelink ('fullname', 'NAME')</th> and try to sort that field, it's just letting me know that this field does not exist in the DB which is correct.

https://laravel.com/docs/5.2/eloquent-mutators#accessors-and-mutators

Could 'column-sortable' allow for SQL UPPER() and LOWER() ORDER BY ?

I would like 'column-sortable' to support SQL UPPER() and LOWER() functionality. Do you plan to add this sometime in the future?
As a suggestion, I do not know if this is acceptable in your view, but I was able to provide this functionality by using the "aliasing" feature and changing the code in queryOrderBuilder() to use orderByRaw() instead of orderBy().

private function queryOrderBuilder($query, array $sortParameters)
{
    // other code...

    if (isset($model->sortableAs) && in_array($column, $model->sortableAs)) {
        $query = $query->orderByRaw("$column $direction");
    } elseif ($this->columnExists($model, $column)) {
        $column = $model->getTable().'.'.$column;
        $query = $query->orderByRaw("$column $direction");
    }

    return $query;
}

In the model:

$sortableAs = ['lower(nick_name)'];

In the view:

@sortablelink('lower(nick_name)'], 'nick')

Would you consider something like this so SQL UPPER() and LOWER() would work with column-sortable ?

Relation - count order?

Hi!

How can we order a column that is a count() from a relation?

Issue with parent relations

First of all: great package!

I found an issue, if the model relates itself. Imagine you have a model Category, which references its parent category.

class Category extends Model
{

 public function parent() {
    	return $this->belongsTo('App\Category', 'parent_id', 'category_id');
 }

}

If you try to sort by parent.name or anything related to the parent category, it will fail, because both tables have the same name. But it can be fixed easily if one of the related tables would get an alias by default, when executing the join.

I may create a pull request.

By the way: Is the package still maintained?

Ability to change asc / desc class suffix.

It would be nice if we could customize (configure) the -asc and -desc class suffixes.

(citation from disqus thread on packalyst.com

Problem with implementation of #53

See 017d3ff#commitcomment-21388855 for more details.

Array to string conversion error when column name is not defined in the config

I get an

"Array to string conversion" error (in Sortable.php - line 85)

when trying to sort on a column name that is not defined in the config (for example 'unit_price').

The issue is that $icon is initialized as an array (the config):

$icon = Config::get('columnsortable');

Then on line 85, $icon is appended with the sort order presumably to get the default font-awesome icon for ascending/descending:

$icon = $icon . '-' . Input::get('order');

Hence the array to string conversion error.

I think this is easily fixed by initialising $icon with default_icon_set from the config:

$icon = Config::get('columnsortable.default_icon_set');

That seems to work for me.

Sorting multiple tables in one request

See more http://stackoverflow.com/q/40612147/1564365

something like

@sortablelink('username','user name', ['prefix' => 'abc'])

where ['prefix' => 'abc'] is identifier of "sorting instance"

which may be simplified to

@sortablelink('username','user name', 'abc')

Support for laravel 5.5.*

Is there any stable branch for laravel 5.5?

It seems like there is only a dev branch for laravel 5.5. If there is none, when is the release?

How to change color of column headings

<td>@sortablelink('category')</td>

My heading backgrounds are dark so I can't see the text. In your code you have the column name wrapped with an anchor. Surrounding the anchor with a style doesn't work. The editor will not allow me to change your code even after changing permissions.

return '<a href="' . url(Request::path() . '?' . $query_string) . '"' . '>' . htmlentities($title) . '</a>' . ' ' . '<i class="' . $icon . '"></i>';

Please add this feature.

Release 5.1.0 refactor causes trans() helper to fail when used as 2nd param of @sortablelink directive

When using the Laravel translate helper trans() as the second parameter of the @sortablelink() directive, in release 5.1.0, the literal is returned, not the result of the function.

This worked correctly in release 5.0.9, prior to the refactor.

Example: @sortablelink('segment_name',trans('entities.segment_segment_name'))

The space still is an issue in blade extension

Hi it would be nice to follow Blade pattern here.

https://github.com/Kyslik/column-sortable#blade-extension

The space-less format doesn't work.

Thanks,

Arthur

Bug in One To One Relation sorting

I have model Blog with hasOne relation to User model

 public function author_sort(){
    return $this->hasOne('App\Models\User', 'user_id', 'id');
}

Request is invalid

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.user_id' in 'on clause' (SQL: select count(*) as aggregate from `blogs` inner join `users` on `blogs`.`id` = `users`.`user_id`)

createMatcher() failing in ColumnSortableServiceProvider with L5.1

Its been reconfigured in L5.1

http://laravel.com/docs/5.1/blade#extending-blade

remove empty keys from URL

when submit search form, empty keys may be exist, ex.:
/accounts?cat=1&name=abc&year=&month=&sort=name&order=asc
It is better if we remove the useless paramerter from url
/accounts?cat=1&name=abc&sort=name&order=asc

just modify the Sortable.php line 171:
$queryString = http_build_query(array_merge(Request::except('sort', 'order', 'page'), $parameters));
add array_filter:
$queryString = http_build_query(array_merge(array_filter(Request::except('sort', 'order', 'page')), $parameters));

what do you think?

Route [] not defined.

I do exactly what's in README.md but I got this error

Route [] not defined.

How to apply sortable() on join results queries

How to apply sortable() on join results queries and by using QueryBuider like DB::table('xyz').
please help me on this..otherwise for a single table it is working fine.. thanks

Same field name, two related tables

Original:

Another problem (i don't know if open extra issue) is that if i use a relationship order (like client -> projects), if i use the same field name the order uses the parent field.

Example:

Clients has projects. I want to order the projects by clients.name, so i use the order 'clients.name' . The SQL gets: 'SELECT * FROM projects INNER JOIN clients ON clients.id = projects.client_id ORDER BY name'

I think that this must be 'ORDER BY clients.name', is'n it?

---

If i use a relationship order (like client -> projects), if i use the same field name the order uses the parent field.

Example:

Clients has projects. I want to order the projects by clients.name, so i use the order 'clients.name' . The SQL gets: 'SELECT * FROM projects INNER JOIN clients ON clients.id = projects.client_id ORDER BY name'

I think that this must be 'ORDER BY clients.name', is'n it?

Support for Laravel Scout

Is there a simple way that I'm missing to use this with Laravel Scout? Model::search returns a Scout builder and only works off the eloquent model and Model::sortable() returns a database query builder and also only works off the eloquent model.

Cannot install on Laravel 5.5

composer require kyslik/column-sortable:5.4.*
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.

  Problem 1
    - Installation request for kyslik/column-sortable 5.4.7 -> satisfiable by kyslik/column-sortable[5.4.7].
    - Conclusion: remove laravel/framework v5.5.0
    - Conclusion: don't install laravel/framework v5.5.0
    - kyslik/column-sortable 5.4.7 requires illuminate/database 5.4.* -> satisfiable by illuminate/database[v5.4.0, v5.4.13, v5.4.17, v5.4.19, v5.4.27, v5.4.36, v5.4.9].
    - don't install illuminate/database v5.4.0|don't install laravel/framework v5.5.0
    - don't install illuminate/database v5.4.13|don't install laravel/framework v5.5.0
    - don't install illuminate/database v5.4.17|don't install laravel/framework v5.5.0
    - don't install illuminate/database v5.4.19|don't install laravel/framework v5.5.0
    - don't install illuminate/database v5.4.27|don't install laravel/framework v5.5.0
    - don't install illuminate/database v5.4.36|don't install laravel/framework v5.5.0
    - don't install illuminate/database v5.4.9|don't install laravel/framework v5.5.0
    - Installation request for laravel/framework 5.5.* -> satisfiable by laravel/framework[v5.5.0].


Installation failed, reverting ./composer.json to its original content.

Sorting by aliases

Anyway of doing something like this?

$invoices = Invoice::select(
    DB::raw('(select sum(cost * quantity) from invoice_items where invoice_id = invoices.id) as total')
)->sortable('total')->paginate(24);

It currently produces the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invoices.total' in 'order clause' (SQL: select (select sum(cost * quantity) from invoice_items where invoice_id = invoices.id) as total from `invoices` order by `invoices`.`total` asc limit 24 offset 0)

It's caused by the table name being added to the column here. I've tested by commenting out this line and it works but that's not the solution as it's the fix for #39.

Could it be possible to override this feature by specifying a field name when needed in the Model::$sortable config array?

public $sortable = [
    'total' => 'total',
    'created_at',
];

On-model 'sortable' assets should be protected type

To promote best-design practices, the '$sortable' class property and any other properties related to this package ought to be added to the Model class as a "protected" property, not "public". Getter methods should be provided for external resources to access those properties as required.

As it is I think that the Sortable properties can be included as "protected" and it won't break the functionality of this package itself, the only consequence being that anyone accessing $myModel->sortable externally will incur a breaking change. But, this issue with future support is exactly why getter methods are necessary.

Any thoughts? This is simple for the project owner to implement however I wouldn't mind submitting a PR if it will be merged.

Not to sort by model value

I have a user table that also holds API tokens. The API token column on the view will display as "YES" or "NO" depends on whether or not the column actually has value.

I don't necessary want to sort by the token value but the display value of "YES" or "NO". Is there a way to configure this?

Thanks

Alan

Exceptions

Let package throw only one exception so end user does not have to catch all of them...

ColumnSortable overloading not working yet?

I tried to use the ColumnSortable overloading but found out its not working yet . It only refreshes the page without sorting. I tried to die and dump within the function but it seems that the function is not being called anywhere.

Add active class to @sortablelink

Hi!
It will be helpful for styling add 'active' class along to anchor_class if this sorting is active.

Multiple sort

Hi and thanks for this module.

There is a way to create a sortableLink but with multiple column ?

On "num column" i want to sort on column "num" AND "date_year" (same direction)

I have 2 special fields, substrings of another and want to sort on them but only in one action.

sorting using a different column on another table

Is it anyway possible to sort using another table field? Example: Users_table(id, name, country_id), Countries_table(id, name). There is anyway to sort the users table based on country name and not country id?

Extra GET parameters at blade template

Hi, it's possible add initial extra GET parameters at blade template?

Something like this:

@sortablelink('name', 'name', 'sec=test')

Thanks!

Set initial / default sorting column.

something like

$users = $user->sortable('default sorting column', 'initial sorting column')->paginate(10);

Missing pagination links.

When the last page has only 1 record, the pagination links will not appear.

Tried few tables, same result.

Thanks.

Update: Found out this is rather the Laravel pagination prob than column-sortable prob.

Sorry for disturb, any idea how to solve this? Using laravel 5.3. Thanks.

-Steven