Eager Loading Filter and Search Issues

[Tarcisiofl]

Summary of problem or feature request

I'm having issues with search and filters on company eager loading relationship.
Also, the company model has a MorphTo relationship where filter or search also didn't work.

Could someone point me out why it is not working?

Code snippet of problem

class Contributor extends Model
{
    protected $fillable = ['name', 'enrollment', 'function', 'cpf', 'rg', 'phone', 'role'];

    public function company()
    {
        return $this->belongsTo(Company::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

class Company extends Model
{
    protected $fillable = ['name', 'contact', 'email', 'phone', 'color'];

    public function address()
    {
        return $this->morphOne(Address::class, 'addressable');
    }

    public function contributors()
    {
        return $this->hasMany(Contributor::class);
    }
}

class UserController extends Controller
{
    /**
     * @var \Yajra\Datatables\Datatables
     */
    private $dataTable;

    /**
     * UserController constructor.
     *
     * @param \Yajra\Datatables\Datatables $dataTable
     */
    public function __construct(Datatables $dataTable)
    {
        $this->dataTable = $dataTable;
    }

    public function index(Request $request)
    {
        if($request->ajax()) {
            $model = Contributor::with('user')->withTrashed()->select('contributors.*');
            return $this->dataTable
                ->eloquent($model)
                ->addColumn('name', function (Contributor $contributor) {
                    return '<div class="table-data__info"><h6>'.$contributor->name.'</h6><span><a>'.$contributor->enrollment.'</a></span></div>';
                })
                ->addColumn('company.name', function (Contributor $contributor) {
                    return $contributor->company->name;
                })
                ->addColumn('company.city', function (Contributor $contributor) {
                    return $contributor->company->address->city;
                })
                ->addColumn('role', function (Contributor $contributor) {
                    if($contributor->role == 'Admin')
                        return '<span class="role admin">'.$contributor->role.'</span>';
                    elseif($contributor->role == 'Client')
                        return '<span class="role client">'.$contributor->role.'</span>';
                    elseif($contributor->role == 'User')
                        return '<span class="role user">'.$contributor->role.'</span>';
                    else
                        return '<span class="role member">'.$contributor->role.'</span>';
                })
                ->addColumn('deleted_at', function (Contributor $contributor) {
                    if($contributor->trashed())
                        return '<span class="status--denied">INACTIVE</span>';
                    return '<span class="status--process">ACTIVE</span>';
                })
                ->addColumn('edit', function (Contributor $contributor) {
                    return '<div class="table-data-feature"><a href="'.route('users.edit', ['contributor' => $contributor]).'" class="item" data-toggle="tooltip" data-placement="top" data-original-title="Edit"><i class="zmdi zmdi-edit"></i></a></div>';
                })
                ->rawColumns(['name', 'role', 'deleted_at', 'edit'])
                ->make(true);
        }
    }
}

<script type="text/javascript">
    $(document).ready(function () {
        var table = $('.table-data2').DataTable({
            processing: true,
            serverSide: true,
            dom: 'frt',
            ajax: '{{route('users.index')}}',
            columns: [
                {data: 'name', name: 'contributors.name'},
                {data: 'role', name: 'contributors.role'},
                {data: 'company.name', name: 'company.name'},
                {data: 'company.city', name: 'company.city'},
                {data: 'deleted_at', name: 'contributors.deleted_at', 'searchable': false},
                {data: 'edit', name:'edit', 'searchable': false, 'orderable': false}
            ],
            drawCallback: function () {
                if ($('.au-btn-load').is(':visible')) {
                    $('html, body').animate({
                        scrollTop: $('.au-btn-load').offset().top
                    }, 1000);
                }

                $('.au-btn-load').toggle(this.api().page.hasMore());
            }
        });

        $('.au-btn-load').on('click', function () {
            table.page.loadMore();
        });
    });
</script>

System details

• Operating System
  Operating System: Windows 10
  PHP Version: 7.2
  Laravel Version: 5.8
  Laravel Datatables Version: Oracle 9.0

[atiar-cse]

Hi,

I am also facing same problem - searching and sorting not working for 'Eager Loading'.

Thanks

[imTigger]

I have the same problem.
The problem seems like that "deleted_at" timestamp is referring to not-aliased table, while table is aliased.

The error I got:

Exception Message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'logistics_providers.deleted_at' in 'where clause' (SQL: select count(*) as aggregate from (select '1' as `row_count` from `orders` left join `logistics_providers` as `logistics_providers_0` on `orders`.`logistics_provider_id` = `logistics_providers_0`.`id` where  `logistics_providers`.`deleted_at` is null and LOWER(`logistics_providers_0`.`name`) LIKE %%test%% and `orders`.`deleted_at` is null) count_row_table)

The above query should refer to logistics_providers_0.deleted_at but generated query refereed to logistics_providers.deleted_at

Problem seems come from this commit: 6c7f997#diff-d7663255b88a6aed388d85442e3ec2e0

[imTigger]

Downgrading to 9.0.1 fixed my issue.

[yajra]

Thanks for reporting. Reverted the support for self join in relation to this.

[yajra]

v9.1.1 released, kindly check again. Thanks!

[Tarcisiofl]

Hello @yajra, it still not working for me when trying to filter by any rawColumn.
Any hint?

[Morinohtar]

Hi,

The "Self Joins" stopped working (#2051 ), the alias was removed. When do you plan to get this working again?

[yajra]

@Morinohtar we need to resolve the issue on soft deletes first as it breaks existing apps. Haven't got the chance to check it further though.

[Morinohtar]

Well, maybe this would work, assuming the PR i did...

Adding this new line:

$deletedAtAs = $deletedAt ? "{$alias}.{$deletedAt}" : $deletedAt;

And then replace the performJoin() with:

$this->performJoin($tableAs ?? $table, $foreign, $owner ?? $other, $deletedAtAs ?? $deletedAt);

I added this to the fork i used for the PR.

[yajra]

Yes, that may work. But we need to find a way to identify if the relation uses SoftDeletes trait before applying the delete column. The challenged I think of here was that Laravel automatically appends the delete scope for every query thus it does not uses the table alias that we generated.

[Morinohtar]

Ok, i will leave that to you then ;) But the working self joins are needed.

Awesome job, keep up, and tks :)