laravel-datatables: Count query slow performance.

Summary of problem or feature request

My user model have a has one relation with profile table.now when I am trying to search anything that time search not working only processing showing forever.like if I write a single letter then search work but if I write more then one letter in search box then processing forever. But if I remove relational column(profile.area’) then search working.I am working over 70000 row. datatable

Code snippet of problem

        $('#users-table').DataTable({
            processing: true,
            serverSide: true,
            ajax: '{{route('route)}}',
            'aaSorting' : [[0,'asc']],
            columns: [
                {data: 'id',name: 'users.id'},
                {data: 'name',name: 'users.name'},
                {data: 'mobile_number',name: 'users.mobile_number'},
                {data: 'profile.area',name: 'profile.area'},
                {data: 'is_active',name: 'users.is_active',searchable: false},
                {data: 'is_allowed',name: 'users.is_allowed',searchable: false},
                {data: 'action',searchable: false}
            ]
        });


    $users = User::with('profile')->select('users.*');
      return DataTables::eloquent($users)
          ->addColumn('action', function ($user) {
              return '<a href="#edit-'.$user->id.'" class="btn btn-xs btn-primary"><i class="glyphicon glyphicon-edit"></i> Edit</a>';
          })
          ->editColumn('is_active', function($status){
              switch($status->is_active)
              {
                //0= Inactive, 1 = Active
                  case '0': return '<span class="label label-danger">Inactive</span>'; break;
                  case '1': return '<span class="label label-success">Active'; break;
                  default: return '<span class="label label-warning">Undefine</span>';
              }
          })
          ->editColumn('is_allowed', function($status){
              switch($status->is_allowed)
              {
                  //0= Inactive, 1 = Active
                  case '0': return '<span class="label label-danger">Not Allowed</span>'; break;
                  case '1': return '<span class="label label-success">Allowed</span>'; break;
                  default: return '<span class="label label-warning">Undefine</span>';
              }
          })
          ->rawColumns(['is_active', 'action','is_allowed'])
          ->blacklist(['created_at', 'updated_at','deleted_at','action','is_allowed','is_active'])
          ->toJson();

My Thinking

If I am changing bellow this two method then it works fine and super fast.

    public function count()
    {
        return $this->prepareCountQuery()->count();
    }

    protected function prepareCountQuery()
    {
        $builder = clone $this->query;
        return $builder;
    }

System details

Windows 10. Local server used Laragon 4.0
PHP Version 7.3
Laravel Version 5.7
Laravel-Datatables Version 8.x

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 1
Comments: 21 (16 by maintainers)

Commits related to this issue

Fixes #1928 — committed to sebastienheyd/laravel-datatables by sebastienheyd 3 years ago
Merge pull request #2612 from sebastienheyd/9.0 Fix the regression when using a having condition in a query. Fix #1928 — committed to yajra/laravel-datatables by yajra 3 years ago

Most upvoted comments

Some queries are indeed slow when counting. To lessen the counting query, try chaining

->skipTotalRecords().

yajra on Jan 23, 2019

Hey @sebastienheyd and @kcrob2

can you confirm that the new solution works? #2616

nuernbergerA on Apr 15, 2021

To add, maybe we can use $this->isComplexQuery($builder) to use for simple count. But I can’t test and would need help to verify if it works.

public function count()
{
    $builder = $this->prepareCountQuery();
    $query = $builder->toSql();

    if (!$this->isComplexQuery($builder)) {
        return $builder->count();
    }

    $table = $this->connection->raw('('.$query.') count_row_table');

    return $this->connection->table($table)
        ->setBindings($builder->getBindings())
        ->count();
}

yajra on Apr 15, 2021

Or try this :

public function count()
{
    $builder = $this->prepareCountQuery();
    $query = $builder->toSql();

    if (!preg_match('#\shaving\s|^select.*\sas\s.*from#i', $query)) {
        return $builder->count();
    }

    $table = $this->connection->raw('('.$query.') count_row_table');

    return $this->connection->table($table)
        ->setBindings($builder->getBindings())
        ->count();
}

sebastienheyd on Apr 14, 2021

@sebastienheyd @kcrob2 thanks for the feedbacks. I released the having patch on v9.17.3. Please check again and verify if it fixes the issue. If not, I will have to revert all the changes on count until we find a better solution. 🍻

yajra on Apr 14, 2021

Hi,

I have the same problem, I think the wrapping was there to avoid error when using an sql query with an alias.

A very simple example :

$query = DB::table('posts')->select(Db:raw("status AS state"))->having('state', '!=', 'deleted');
Datatables::of($query);

After the PR : when calling count(), status AS state will be replaced by a simple count(*) and then the having condition will fail because it doesn’t know the state alias.

sebastienheyd on Apr 13, 2021

Hi guys,

My code broke after this PR. I use filter to override the global search.

Next I use the orHavingRaw function from Laravel on the query that gives me the possibility to use MySql aliases. This is my code:

` $datatableObject = DataTables::eloquent($items) ->filter(function ($query) use ($module) { $query->orHavingRaw(“id LIKE ?”, [“%” . request(‘search.value’) . “%”]);

            foreach ($module->attributeColumns as $attribute) {
                $query->orHavingRaw("{$attribute->key} LIKE ?", ["%" . request('search.value') . "%"]);
            }
        });

I’ve tried searching through the code of the package for solutions, but haven’t find anything. Do you guys know a solution?

Thanks in advance!

kcrob2 on Apr 13, 2021