Coder Perfect

orderBy in Laravel is based on a relationship.


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:

public function comments()
    return $this->hasMany('Comment')->orderBy('column');

[edit after comment]

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', '=', '')
    ->orderBy('relation_table.title', 'ASC')
    ->with(['relationModal' => function ($q) {
        $q->where('column', 'value');


$user = User::where('city', 'kullu')
    ->join('salaries', '', '=', 'salaries.user_id')
    ->orderBy('salaries.amount', 'ASC')
    ->with(['salaries' => function ($q) {
        $q->where('amount', '>', '500000');

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()
        case BelongsTo::class:
            $keys = [
                'foreign' => $relationData->getOwnerKeyName(),
                'local' => $relationData->getForeignKeyName()
            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))
    $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:

    ' AS status_name'

Answered by Sebastiaan

Post is based on