laravel-datatables
laravel-datatables copied to clipboard
yajra
laravel relation where clause not apply in laravel datatable sorting
Summary of problem or feature request
hi Sorry I'm not good at English; search is working with related models but when i want to sort as related field it's duplicated because translate where clause not applying in sorting
any solution ?
Code snippet of problem
System details
- linux
- php version : 7.1
- Laravel Version : 5.4
- Laravel-Datatables Version : 7.0
class OrderFood extends Model
{
protected $table = 'order_foods';
protected $fillable = [
'order_id','food_id','quantity','food_price'
];
public function order()
{
return $this->belongsTo(Order::class);
}
public function food()
{
return $this->belongsTo(Food::class)
->with('translate');
}
}
class Food extends Model
{
protected $table = 'foods';
protected $fillable = [
'title','date','tax','tax_enabled','tax_included','images','star','description','recipes','status','lang'
];
public function translate()
{
return $this->hasOne(FoodTranslator::class,'food_id')
->where('lang',app()->getLocale());
}
}
public function query()
{
DB::statement(DB::raw('set @rownum=0'));
DB::statement(DB::raw('SET collation_connection = utf8_general_ci'));
DB::statement(DB::raw('SET character_set_connection = utf8'));
$order_id = $this->order_id;
$order_foods = Order::find($order_id)->foods()->with(['food.translate'])->select(['*','order_foods.created_at',DB::raw('@rownum := @rownum + 1 AS rownum')]);
return $this->applyScopes($order_foods);
}
private function getColumns()
{
return [
['title' => trans('form.row'),'name' => 'rownum', 'data' => 'rownum','searchable'=>false],
['title' => trans('order.order_food'),'name' => 'food.translate.title', 'data' => 'food.translate.title','orderable'=>false],
['title' => trans('order.quantity'),'name' => 'quantity', 'data' => 'quantity'],
['title' => trans('order.food_price'),'name' => 'food_price', 'data' => 'food_price'],
['title' => trans('form.created_at'),'name' => 'created_at', 'data' => 'created_at'],
['title' => trans('form.action'),'name' => 'action', 'data' => 'action','searchable'=>false,'exportable'=>false,'printable'=>false],
];
}
This issue was already fixed on v8. It's compatible to Laravel 5.4 but have breaking changes. Note though that it's not yet tagged as stable. Thanks!
The issue here is on translate relation right?
public function translate()
{
return $this->hasOne(FoodTranslator::class,'food_id')
->where('lang',app()->getLocale());
}
The issue is the where clause not applied? This was addressed on v8 unless there is another issue I'm not seeing?
on ordering 3 queries is executed
1:
bindings:[]
query:"select count(*) as aggregate from (select '1' as `row_count` from `foods`) count_row_table"
time:0.29
2:
bindings:[]
query:"select *, @rownum := @rownum + 1 AS rownum from `foods` left join `food_translator` on `food_translator`.`food_id` = `foods`.`id` order by `food_translator`.`title` desc limit 10 offset 0"
time:0.81
3:
bindings:["fa", 1]
query:"select * from `food_translator` where `lang` = ? and `food_translator`.`food_id` in (?)"
time:0.46
maybe where clause must be in second query not 3
this query
query:"select * from `food_translator` where `lang` = ? and `food_translator`.`food_id` in (?)"
is for
public function translate()
{
return $this->hasOne(FoodTranslator::class,'food_id')
->where('lang',app()->getLocale());
}
that not effect on results
@aliworkshop I think I'm seeing the issue now. Will dig this further when I got the chance.
I fixed issue with edit EloquentDataTable class in namespace Yajra\DataTables
protected function joinEagerLoadedColumn($relation, $relationColumn)
{
$table = '';
$lastQuery = $this->query;
foreach (explode('.', $relation) as $eachRelation) {
$model = $lastQuery->getRelation($eachRelation);
$fillable = [];
switch (true) {
case $model instanceof BelongsToMany:
$pivot = $model->getTable();
$pivotPK = $model->getExistenceCompareKey();
$pivotFK = $model->getQualifiedParentKeyName();
$this->performJoin($pivot, $pivotPK, $pivotFK);
$related = $model->getRelated();
$table = $related->getTable();
$tablePK = $related->getForeignKey();
$foreign = $pivot . '.' . $tablePK;
$other = $related->getQualifiedKeyName();
$lastQuery->addSelect($table . '.' . $relationColumn);
$this->performJoin($table, $foreign, $other);
break;
case $model instanceof HasOneOrMany:
$table = $model->getRelated()->getTable();
$foreign = $model->getQualifiedForeignKeyName();
$other = $model->getQualifiedParentKeyName();
$fillable = $model->getRelated()->getFillable();
break;
case $model instanceof BelongsTo:
$table = $model->getRelated()->getTable();
$foreign = $model->getQualifiedForeignKey();
$other = $model->getQualifiedOwnerKeyName();
break;
default:
throw new Exception('Relation ' . get_class($model) . ' is not yet supported.');
}
$this->performJoin($table, $foreign, $other,'inner',$fillable);
$lastQuery = $model->getQuery();
}
return $table . '.' . $relationColumn;
}
protected function performJoin($table, $foreign, $other, $type = 'left', $fillable = [])
{
$joins = [];
foreach ((array) $this->getBaseQueryBuilder()->joins as $key => $join) {
$joins[] = $join->table;
}
if (! in_array($table, $joins)) {
$this->getBaseQueryBuilder()->join($table, $foreign, '=', $other, $type);
if (in_array('lang',$fillable))
$this->query->where('lang', app()->getLocale());
}
}
this change manually apply lang column where clause to join query
if a way would have existed that relation where clauses would applied as this
it would very good
@aliworkshop thanks, but the proposed solution will only work on your case. Can you try maybe using the code below?
Replace:
$this->getBaseQueryBuilder()->join($table, $foreign, '=', $other, $type);
With:
$this->query->join($table, $foreign, '=', $other, $type);
A bit busy on work projects and can't dedicate much time on oss lately. Thanks!
hi again master i am find another solve for this problem if we edit EloquentDataTable class in namespace Yajra\DataTables as this , It probably works
protected function joinEagerLoadedColumn($relation, $relationColumn)
{
$table = '';
$lastQuery = $this->query;
foreach (explode('.', $relation) as $eachRelation) {
$model = $lastQuery->getRelation($eachRelation);
switch (true) {
case $model instanceof BelongsToMany:
$pivot = $model->getTable();
$pivotPK = $model->getExistenceCompareKey();
$pivotFK = $model->getQualifiedParentKeyName();
$this->performJoin($pivot, $pivotPK, $pivotFK);
$related = $model->getRelated();
$table = $related->getTable();
$tablePK = $related->getForeignKey();
$foreign = $pivot . '.' . $tablePK;
$other = $related->getQualifiedKeyName();
$lastQuery->addSelect($table . '.' . $relationColumn);
$this->performJoin($table, $foreign, $other);
break;
case $model instanceof HasOneOrMany:
$table = $model->getRelated()->getTable();
$foreign = $model->getQualifiedForeignKeyName();
$other = $model->getQualifiedParentKeyName();
break;
case $model instanceof BelongsTo:
$table = $model->getRelated()->getTable();
$foreign = $model->getQualifiedForeignKey();
$other = $model->getQualifiedOwnerKeyName();
break;
default:
throw new Exception('Relation ' . get_class($model) . ' is not yet supported.');
}
$wheres = $model->getQuery()->getQuery()->wheres;
$this->performJoin($table, $foreign, $other,'inner',$wheres);
$lastQuery = $model->getQuery();
}
return $table . '.' . $relationColumn;
}
protected function performJoin($table, $foreign, $other, $type = 'left', $wheres = [])
{
$joins = [];
foreach ((array) $this->getBaseQueryBuilder()->joins as $key => $join) {
$joins[] = $join->table;
}
if (! in_array($table, $joins)) {
$this->query->join($table, $foreign, '=', $other, $type);
foreach ($wheres as $where) {
if ($where['boolean'] == 'and')
$this->query->where($where['column'], $where['operator'], $where['value']);
else
$this->query->orWhere($where['column'], $where['operator'], $where['value']);
}
}
}
please apply this code in next version