Problem
I’m going through all of the comments left by the author of a specific post.
foreach($post->user->comments as $comment)
{
echo "<li>" . $comment->title . " (" . $comment->post->id . ")</li>";
}
This gives me
I love this post (3)
This is a comment (5)
This is the second Comment (3)
How would I sort the above list by post id so that it is numbered 3,3,5?
Asked by PrestonDocks
Solution #1
Query functions can be used to expand the relationship:
<?php
public function comments()
{
return $this->hasMany('Comment')->orderBy('column');
}
[edit after comment]
<?php
class User
{
public function comments()
{
return $this->hasMany('Comment');
}
}
class Controller
{
public function index()
{
$column = Input::get('orderBy', 'defaultColumn');
$comments = User::find(1)->comments()->orderBy($column)->get();
// use $comments in the template
}
}
When getting the list of comments, simply apply the orderBy() based on Input::get to the default User model (). (Be sure to double-check your input ;))
Answered by Rob Gordijn
Solution #2
You could also be able to:
$sortDirection = 'desc';
$user->with(['comments' => function ($query) use ($sortDirection) {
$query->orderBy('column', $sortDirection);
}]);
This gives you the freedom to apply any logic you want to each associated comment record. You might include things like:
$query->where('timestamp', '<', $someTime)->orderBy('timestamp', $sortDirection);
Answered by agm1984
Solution #3
SortBy… could be useful.
$users = User::all()->with(‘rated’)->get()->sortByDesc(‘rated.rating’);
Answered by Harry Bosh
Solution #4
Try this solution.
$mainModelData = mainModel::where('column', $value)
->join('relationModal', 'main_table_name.relation_table_column', '=', 'relation_table.id')
->orderBy('relation_table.title', 'ASC')
->with(['relationModal' => function ($q) {
$q->where('column', 'value');
}])->get();
Example:
$user = User::where('city', 'kullu')
->join('salaries', 'users.id', '=', 'salaries.user_id')
->orderBy('salaries.amount', 'ASC')
->with(['salaries' => function ($q) {
$q->where('amount', '>', '500000');
}])->get();
You can adjust the column name in join() to match the structure of your database.
Answered by PHP Worm…
Solution #5
On a relation field, I created a trait to sort it. I faced similar problem with webshop orders that had a status relation and a name field in the status.
An illustration of the situation
It is not possible to order on using “joins” of eloquent models because they are not joins. They are queries that run after the previous one has finished. So I created a little hack to read the eloquent relation info (such as tables, joining keys, and additional wheres if applicable) and join it to the main query. This only works in one-on-one situations.
Create a trait and apply it to a model as the first step. There are two functions in that trait. The first is as follows:
/**
* @param string $relation - The relation to create the query for
* @param string|null $overwrite_table - In case if you want to overwrite the table (join as)
* @return Builder
*/
public static function RelationToJoin(string $relation, $overwrite_table = false) {
$instance = (new self());
if(!method_exists($instance, $relation))
throw new \Error('Method ' . $relation . ' does not exists on class ' . self::class);
$relationData = $instance->{$relation}();
if(gettype($relationData) !== 'object')
throw new \Error('Method ' . $relation . ' is not a relation of class ' . self::class);
if(!is_subclass_of(get_class($relationData), Relation::class))
throw new \Error('Method ' . $relation . ' is not a relation of class ' . self::class);
$related = $relationData->getRelated();
$me = new self();
$query = $relationData->getQuery()->getQuery();
switch(get_class($relationData)) {
case HasOne::class:
$keys = [
'foreign' => $relationData->getForeignKeyName(),
'local' => $relationData->getLocalKeyName()
];
break;
case BelongsTo::class:
$keys = [
'foreign' => $relationData->getOwnerKeyName(),
'local' => $relationData->getForeignKeyName()
];
break;
default:
throw new \Error('Relation join only works with one to one relationships');
}
$checks = [];
$other_table = ($overwrite_table ? $overwrite_table : $related->getTable());
foreach($keys as $key) {
array_push($checks, $key);
array_push($checks, $related->getTable() . '.' . $key);
}
foreach($query->wheres as $key => $where)
if(in_array($where['type'], ['Null', 'NotNull']) && in_array($where['column'], $checks))
unset($query->wheres[$key]);
$query = $query->whereRaw('`' . $other_table . '`.`' . $keys['foreign'] . '` = `' . $me->getTable() . '`.`' . $keys['local'] . '`');
return (object) [
'query' => $query,
'table' => $related->getTable(),
'wheres' => $query->wheres,
'bindings' => $query->bindings
];
}
This is the function that reads the eloquent data and is called “detection.”
The second one:
/**
* @param Builder $builder
* @param string $relation - The relation to join
*/
public function scopeJoinRelation(Builder $query, string $relation) {
$join_query = self::RelationToJoin($relation, $relation);
$query->join($join_query->table . ' AS ' . $relation, function(JoinClause $builder) use($join_query) {
return $builder->mergeWheres($join_query->wheres, $join_query->bindings);
});
return $query;
}
This is the function that gives the model a scope that may be used in queries. Simply apply the trait to your model and utilize it as follows:
Order::joinRelation('status')->select([
'orders.*',
'status.name AS status_name'
])->orderBy('status_name')->get();
Answered by Sebastiaan
Post is based on https://stackoverflow.com/questions/18143061/laravel-orderby-on-a-relationship