Level 56
For sorting on a relation you have no other choices than joining the table.
Apr 30, 2015
14
Level 88
Sorting tables with relations
Hi guys,
I was wondering how to sort my tables that contain the information of let's say locations. Now each location is connected to a client. Of course I have the relations set up.
Now what I have is that when you click on the <th> it will sort the table by that column by adding the parameters to the url. So my url looks like this when I click on '
http://project.app/locations?sortBy=address&direction=asc&page=1
Now the problem when I need to sort on for example the client name. Using the relation I can show the name of client using $location->client->name because I eager load the client as well. But when I want to sort on the client I get this url
http://project.app/locations?sortBy=client_id&direction=asc&page=1
Now for the sorting part I added a scope to my model for each sorting that I can call with this query
return Location::with('client')->sortable($request)->paginate(10);
And here is the scope function
public function scopeSortable($query, Request $request)
{
if ($request->get('sortBy') && $request->get('direction'))
return $query->orderBy($request->get('sortBy'), $request->get('direction'));
return $query;
}
As you can see this will only work for columns in the model and not for relations. So my question is how can I make this work with relations as well! The scope is now sorting on the client_id instead of the client name..
If you need more information please let me know
All help is welcome ;)
Level 88
I was afraid of that... My queries are so beautiful right now and the joins just mess them up...
Level 56
@bobbybouwmann maybe we can come up with something beautiful :)
What if you also pass the relation name in the url ? So you can get the relation object, and from this you can have the tables/keys names to generate the join.... ? Or you just have a scope with a switch to prepare the query according to the relation name before passing it to the sortable scope.
Level 88
You mean something like this
http://project.app/locations?sortBy=client.name&direction=asc&page=1
And then we need to update the scope to handle that I guess or add another scope of course...
public function scopeSortable($query, Request $request)
{
$requestSortByParams = explode('.', $request->get('sortBy'));
if (count($requestSortByParams) > 1)
// Do the join and return the results
if ($request->get('sortBy') && $request->get('direction'))
return $query->orderBy($request->get('sortBy'), $request->get('direction'));
return $query;
}
It's not beautiful hehe, I'm out of ideas :P
Level 56
@bobbybouwmann sometimes you just have to get dirty :p
Level 88
In that case I might be better of with a join.. Well dirty it is!
Level 53
@bobbybouwmann For x-1 relations use my https://github.com/jarektkaczyk/eloquence#mappable :
// Location model
protected $maps = [
'client_name' => 'client.name',
];
// now you can do this:
Location::orderBy('client_name')->get();
It will take care of joining the table and sorting by the relation. Works for x-to-1, ie. belongsTo, hasOne, morphOne, morphTo relations.
Just give me a while, because orderBy and things like pluck & aggregates are not pushed yet, I'm finishing them right now.
Level 88
Awesome package, I will give it a go ;)
Level 88
@JarekTkaczyk So I started to play with your package and as you mentioned you are still working on the groupBy stuff but I get this query
select * from `locations` where (select count(*) from `clients` where `locations`.`client_id` = `clients`.`id` order by `client_name` desc) >= 1
When I do this
// In model
protected $maps = [
'client_name' => 'client.name'
];
// In repository
return Location::with('client')->orderBy('client_name', 'desc')->toSql();
Either I'm doing something wrong or you are just not done with the orderBy part since the results are not ordered by the client_name!
Level 53
@bobbybouwmann It's been pushed 20 mins ago. Try again pulling latest v0.4.6
Level 88
I will give it a try! Thanks man, you rock!
Level 88
@JarekTkaczyk Awesome! It works like a charm :D
Level 56
@JarekTkaczyk cant you propose this to the main laravel repo ? I dislike to include too many external packages :)
Level 53
@pmall It's rather heavy extension to the eloquent, so it wouldn't be wise to include it in the core.
Also Taylor is gonna say use package, and on this I agree with him.
And.. you won't need any other for eloquent :)
Please or to participate in this conversation.