Laravel orderBy on a relationship

Question

I am looping over all comments posted by the Author of a particular 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 order by the post_id so that the above list is ordered as 3,3,5

Answer 1

It is possible to extend the relation with query functions:

<?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
    }
}

default User model + simple Controller example; when getting the list of comments, just apply the orderBy() based on Input::get(). (be sure to do some input-checking ;) )

Answer 2

I believe you can also do:

$sortDirection = 'desc';

$user->with(['comments' => function ($query) use ($sortDirection) {
    $query->orderBy('column', $sortDirection);
}]);

That allows you to run arbitrary logic on each related comment record. You could have stuff in there like:

$query->where('timestamp', '<', $someTime)->orderBy('timestamp', $sortDirection);

Answer 3

Using sortBy... could help.

$users = User::all()->with('rated')->get()->sortByDesc('rated.rating');

Answer 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 change the column name in join() as per your database structure.

Answer 5

I made a trait to order on a relation field. I had this issues with webshop orders that have a status relation, and the status has a name field.

Example of the situation

Ordering on with "joins" of eloquent models is not possible since they are not joins. They are query's that are running after the first query is completed. So what i did is made a lil hack to read the eloquent relation data (like table, joining keys and additional wheres if included) and joined it on the main query. This only works with one to one relationships.

The first step is to create a trait and use it on a model. In that trait you have 2 functions. The first one:

/**
 * @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 "detection" function that reads the eloquent data.

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 adds a scope to the model to use within query's. Now just use the trait on your model and you can use it like this:

Order::joinRelation('status')->select([
    'orders.*',
    'status.name AS status_name'
])->orderBy('status_name')->get();