Skip to content

Instantly share code, notes, and snippets.

@InToSSH
Last active June 17, 2023 00:33
Show Gist options
  • Save InToSSH/ba4cb4d196229df729bdb3568758883c to your computer and use it in GitHub Desktop.
Save InToSSH/ba4cb4d196229df729bdb3568758883c to your computer and use it in GitHub Desktop.
Laravel Eloquent - Order By Relation macro
<?php
Builder::macro('orderByRelation', function(string $searchColumn, string $dir) {
list($relation, $column) = explode('.', $searchColumn);
$relation_table = $this->getRelation($relation)->getModel()->getTable();
$relation_foreign_key = $this->getRelation($relation)->getForeignKeyName();
$query_table = $this->getModel()->getTable();
$this->select($query_table . '.*')
->join($relation_table, $query_table . '.' . $relation_foreign_key, '=', $relation_table . '.id')
->orderBy($relation_table . '.' . $column, $dir);
return $this;
});
@InToSSH
Copy link
Author

InToSSH commented Apr 19, 2021

Laravel Eloquent - Order By Relation

Usage

Put the macro to AppServiceProvider.php -> boot() function

Example

Models

Post.php

class Post extends Model
{

    public function group()
    {
        return $this->belongsTo(Group::class);
    }


}

Group.php

class Group extends Model
{

    public function posts()
    {
        return $this->hasMany(Post::class);
    }


}

Get posts ordered by Group name

App\Models\Post::orderByRelation('group.name', 'DESC')->get()

@laudaikinhdi
Copy link

Thanks

@paulgiorgi
Copy link

paulgiorgi commented Jan 21, 2023

can I propose an add-on?
I just added an if but I think it could be done better_

<?php

Builder::macro('orderByRelation', function(string $searchColumn, string $dir) {
            if(str_contains($searchColumn,'.')){
            list($relation, $column) = explode('.', $searchColumn);
            $relation_table = $this->getRelation($relation)->getModel()->getTable();
            $relation_foreign_key = $this->getRelation($relation)->getForeignKeyName();
            $query_table = $this->getModel()->getTable();
            $this->select($query_table . '.*')
                ->join($relation_table, $query_table . '.' . $relation_foreign_key, '=', $relation_table . '.id')
                ->orderBy($relation_table . '.' . $column, $dir);
            }else{
            $this->orderBy($searchColumn,$dir);
            }
        });

This way we let the dev decide if the orderBy inherits a relationship or not

@InToSSH
Copy link
Author

InToSSH commented Jan 22, 2023

thanks for the suggestion @paulgiorgi but I think this is not needed. This macro (Eloquent function) is specifically for ordering by relation where you need the dot notation. What you are doing is basically just reverting back to normal orderBy() function, which you can use in the first place instead of this one if you need to order by column.

@paulgiorgi
Copy link

This way we can keep that ->orderByRelation at the end of a model's filtering and decide. but you know, you're right, there's no need here. thanks man, so precious

@dev-rizwanqureshi
Copy link

Hi, This might help you.

$users = User::get()->sortBy(function($query){
return $query->role->name;
})->all();

$users = User::with(['role' => function ($q) {
$q->orderBy('name', 'desc');
}])->get();

$users = User::select('*')
->orderBy(Role::select('name')
->whereColumn('roles.id', 'users.role_id')
);

@klaas0
Copy link

klaas0 commented Jun 14, 2023

You should return $this at the end so you could chain your query together.
For example: you can call ->get() directly on the orderByRelation (orderByRelation('relation.column')->get())

@InToSSH
Copy link
Author

InToSSH commented Jun 17, 2023

You should return $this at the end so you could chain your query together. For example: you can call ->get() directly on the orderByRelation (orderByRelation('relation.column')->get())

Good catch, forgot to include that. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment