Laravel Orderby on a Relationship

Laravel orderBy on a relationship

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

Perform order by relationship field in Eloquent

This will sort the subquery. Not the "first query (the product query)".

Basically, your subquery will be:
select * from variants where product_id in (....) order by price, and that is not what you want, right?

<?php 
// ...

$order = $request->sort;

$products = Product::whereHas('variants')->with(['reviews',  'variants' => function($query) use ($order) {
  if ($order == 'price') {
    $query->orderBy('price');
  }
}])->paginate(20);

If you want to sort product +/or variant you need to use join.

$query = Product::select([
          'products.*',
          'variants.price',
          'variants.product_id'
        ])->join('variants', 'products.id', '=', 'variants.product_id');

if ($order == 'new') {
    $query->orderBy('products.created_at', 'DESC');
} else if ($order == 'price') {
    $query->orderBy('variants.price');
}

return $query->paginate(20);

Laravel orderBy with paginate and relations

What i have done in this situations that i want to sort based on a column of a relationship is that i call the model of the relationship to sort it:

So let's say that your query is:

$myQuery = $model->with('employee')->where('this_column','=','that_column');

After comes the sorting part. For the example let's assume that the model of your relationship is called Employee

    $myQuery->orderBy(
        Employee::select('id')
            ->whereColumn('employee_id', 'employee.id')
            ->orderBy('PRSVORNAME', 'asc')
            ->limit(1),
        'asc'
    );

return $myQuery->get();

After that you can add your pagination or whatever you like since your collection is already sorted after the ->get()

For the above example i assumed that the foreign key between the two tables is the id column. In case it's another column you can match it in the whereColumn

Laravel - orderBy not working on relationship collection

When you look at the relation definition in the source code of Thread.php, you see that posts are already ordered by created_at by default:

public function posts()
{
        $withTrashed = config('forum.preferences.display_trashed_posts') || Gate::allows('viewTrashedPosts');
        $query = $this->hasMany(Post::class)->orderBy('created_at');
        return $withTrashed ? $query->withTrashed() : $query;
}

I think you would need to override the posts method, remove the orderBy('created_at') statement, and then your own orderBy will work.

How to use orderBy using laravel relationship?

in the model you would have:

public function scopecountDescending($query)
{
    return $query->orderBy('comments_count','DESC');
  }  

And in your controller:

 $posts = Post::countDescending()->get();

or simply in your controller:

Post::orderBy('comments_count' , 'DESC')->get();

---

Related Topics