Skip to content

Instantly share code, notes, and snippets.

@ariefhikam
Last active August 6, 2019 12:25
Show Gist options
  • Save ariefhikam/78168367b879931afdb872a6b83d0a24 to your computer and use it in GitHub Desktop.
Save ariefhikam/78168367b879931afdb872a6b83d0a24 to your computer and use it in GitHub Desktop.
Laravel Sorting from another table with Eloquent
/*
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');
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment