Laravel Sorting from another table with Eloquent

index.php

/*
By: Arief Hikam

Laravel Sorting from another table with Eloquent

Sometimes it is difficult to perform sorting by Eloquent because relationships do not use the JOIN in query builder but use 
Eager Load which mean we can not sorting by another table field because the field is doesn't exist.
This is how we use the simple code Eloquent and sorting another table by using LEFT JOIN.

I think this is the simplest way I can think of.


*/

/*
Model

scope HasField

how to use: 
$table = Model::with(['relation_name']);
$table->hasField('relation_name.field_name');

this will generate query:
SELECT `eloquentTable.*`,`relationTable`.`field_name` AS `relation_name_field_name` from `eloquentTable` 
LEFT JOIN `relationTable`
ON `eloquentTable`.`foreign_key` = `relationTable`.`primaryKey`
*/

public function scopeHasField($query, $relation_name, $operator = '=', $type = 'left', $where = false) {
    $split = explode('.', $relation_name);
    $relation = $this->{$split[0]}();
    $related_column = $split[1];
    $table = $relation->getRelated()->getTable();
    $one = $table.'.'.$relation->getRelated()->primaryKey;
    $two = $relation->getForeignKey();

    if (empty($query->columns)) {
        $query->select($this->getTable().".*");
    }
    $query->addSelect(new Expression("`$table`.`$related_column` AS `$split[0]_$related_column`"));
    
    return $query->join($table, $one, $operator, $two, $type, $where);

}

/*
Controller

put in your function 

after we doing left join we can sorting from relatioship :)

your $request->sort should be :
$request->sort = 'relation_name.field_you_want_to_sort';

full URL Example:
http://toko.app:8000/pembelian/index?response=json&sort=pajak.nama_pajak&sortType=DESC&search=&page=

&sort=pajak.nama_pajak
&sortType=DESC
`pajak` is the relationship name and `nama_pajak` is the field we want to sort.

*/

     if(isset($request->sort) && isset($request->sortType)){
            if(str_contains($request->sort,'.')){
                $split = explode('.', $request->sort);

                $table->hasField($request->sort);

                $table->orderBy($split[0]."_".$split[1],$request->sortType);
            }else{
                $table->orderBy($request->sort,$request->sortType);
            }
		}else{
       $table->orderBy('created_at','DESC');
    }