How to apply sortable() on join results queries

[johnarc5]

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

[Kyslik]

This package doesn't work with joins yet :( and I don't have a lot of time to make it work. Perhaps in winter (northern hemisphere).

Any commits are welcome!

[Kyslik]

@johnarc5 Hello, could you elaborate a little; what kind of ordering do you have in mind?

[saimiris]

Hi there,

Your package is really great but yes, it would be even better if we could do something like:

• say you have a Student model that has a hasOne relationship with a User model (through a "user_id" field).
• when paginating a Students list, I would like to be able to sort the Students by User's email with something like this: @sortablelink ('student.user.email', 'Email') or @sortablelink ('user.email', 'Email')

Do you think that is something you could include to your package?

That would be really really great and very useful for a lot of users.

Many thanks!

[Kyslik]

Thank you for feedback, I am going to look at this for sure.

I guess it can be applied only for 1-1 relationship.

see @neutralrockets #8 (comment)

[saimiris]

Actually it could be interested also for a 1-many relationship for some
special cases, like for instance sorting the SUM of a field of the
associated model.

But if you extend your module to work with a 1-1 relationship, that would
just great!!! :-)

Many thanks!

[johnarc5]

Whenever I have time I also will look on to it and will make available changes to make it best.

[Kyslik]

This is now done in 5.0.0 release, see readme for more information.

https://github.com/Kyslik/column-sortable#one-to-one-relation-sorting--example

[neutralrockets]

Hi,

I too am interested in being able to sort on some other column on a related table. For example, being able to sort Addresses by their Country name.

class Address extends Model
{
    use Sortable;

    protected $table = 'addresses';

    protected $sortable = ['first_name', 'last_name', 'country', 'email'];

    public function country()
    {
        return $this->belongsTo('App\Country');
    }
}

class Country extends Model
{
    protected $table = 'countries';

    protected $fillable = ['name', 'alpha2', 'alpha3', 'numeric'];
}

One way I have been able to achieve this using this package is by extending the "queryOrderBuilder" method in the Sortable trait slightly:

if (method_exists($this, camel_case($sort) . 'Sortable')) {
    return call_user_func_array(array($this, camel_case($sort) . 'Sortable'), array($query, $order));
} else {
    return $query->orderBy($sort, $order);
}

Now by adding an additional "overriding" method in the Address model, I am able to get the sort happening using my own custom join on the associated table:

    protected function countrySortable($query, $order) {
        return $query->join('countries', 'addresses.country_id', '=', 'countries.id')
            ->orderBy('countries.name', $order)
            ->select('addresses.*');
    }

Then in the controller, it remains the same, but the country column is sorted using the overriding method:

    $addresses = Address::sortable()->paginate(25);

What do you think about introducing such functionality for users to override special cases?

Thanks

[Kyslik]

What relation is between the two? (Country/Address).

[neutralrockets]

The relationship is one-to-many (each Address can be in one Country, each Country can contain many Addresses).

There is a foreign key on Address table called country_id but I am trying to sort a list of addresses by country name instead.

I hope the above example and solution makes some sense. The capability just allows users to extend or override the sorting behaviour for specific columns.

While this might not be convenient for the general case of one-to-many relationships, it could be a useful feature for allowing users to specify their own custom sorting rules for e.g. on "derived" attributes that don't actually exist in the data model.

[neutralrockets]

Here's another example to illustrate the "derived" attribute use case.

Say we have a Product model that has a length, width and height column in the database. In our view, we have a table that lists all the products and their length, width and height but now we also have a derived attribute "volume" that is the product of length * width * height. Now we want to be able to sort on volume, even though the underlying Model does not have this attribute:

class Product extends Model
{
    use Sortable;

    protected $table = 'products';

    protected $sortable = ['length', 'width', 'height', 'volume'];

    protected function volumeSortable($query, $order) {
        return $query->selectRaw('products.*, (products.width * products.height * products.length) as volume')->orderBy('volume', $order);
    }
}

Our controller is the same:

$products = Product::sortable()->paginate(15);

Our view is the same:

<th>@sortablelink('volume', 'Volume')</th>

We can specify our own rule on how to sort this column even though it doesn't exist in the Model or the database. Theres probably better ways to achieve this, but it illustrates being able to specify a custom sorting rule for a particular column.

Hope that makes sense.

[Kyslik]

Thank you for detailed "report" I will definitely keep this issue open or perhaps move it to another one and when time comes (I hope soon one or two weeks) I will implement this, make documentation etc. :)

Before that I will try to make tests for package so further developing is much easier.

I must say, perfect idea and example with volume is awesome! Readme file is getting big I will have to think about wiki #18.

[ivansammartino]

+1 for a One-To-Many relationship support

[Kyslik]

@neutralrockets Hey, I am really glad you posted this here, I am toying with "overriding" and it is pretty awesome!

you can check that out on dev-master thank you a TON!

[msdavid1296]

Hi,
In my case when I sort and click the pagination button first time the sorting is not working.Next time it works fine.

[Kyslik]

@msdavid1296 please open new issue and show all relevant code (relationship definition, controller, and maybe blade template if you are using @sortable directive.

Oh you already did #92

[UserCosti]

does this package works with belongsToMany (Many to Many) relationship??

[Kyslik]

@UserCosti can you try implementing it yourself?

[jagdish4249]

@neutralrockets @Kyslik I have one question ... if my table name is product_types ... what should be the name of function instead of countrySortable as mentioned in #8 (comment)
should it be productTypeSortable or product_typeSortable?

[Kyslik]

@jagdish4249

I have one question ... if my table name is product_types ... what should be the name of function instead of countrySortable as mentioned in #8 (comment)
should it be productTypeSortable or product_typeSortable?

You listed two options, you tried both?