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).