How to increase speed in a rather complicated situation?

[mihai-waydev]

I tried to find an answer to my problem for so many hours that I had to seek help from you :) My situation is a little bit complicated, but I will try to explain it as best as I can.

I have a Laravel 7 application which implements the package yajra/laravel-datatables 9.

What I am trying to achieve, and where I am now:

I have 3 tables: users, users_info and user_merges. I try to retrieve in a DataTable all the users from the database along with some data about them that is found in the other two tables. What I've done at first was an Eloquent query finished with get(), I've done the necessary actions on the result (like adding the rows from the other tables, these can get complicated, I don't think they are doable as queries) and then I return everything with DataTables::of($result)->make(true).

This works well, but as you can probably imagine, every time I do any action on the table like sort, change page, search etc. all the data is retrieved, not only the data I need for the respective page, then the actions are done, then the package returns only the data for that page. This means that when I have a lot of data (about 100.000 records), and I want for example to change the page, it takes a lot of time...

What I want to achieve is to speed up this process by a lot, because right now with a large number of data, it is unusable. What I've found is that it is better to give the package the query, and not use get(). And here come the multiple problems.

$users = $project->users()
                                ->whereIn('id', $users_teams)
                                ->whereNotIn('id', $users_merges)
                                ->select('id', 'name')
                                ->with(['mergedUsers' => function ($query) use($project) {
                                    $query->where('project_id', $project->id)->select('project_id', 'user_id', 'primary_id');
                                }]);
                                
            $dataTable = DataTables::of($users)->make(true);
            $response = $dataTable->getData(true);

            $response['data'] = collect($response['data'])->map(function ($d, $d_key) { 
                //stuff that has to do with data like: if column "name" in users_info table is null, then take the column "name" in users table, if it is empty there, then use the email from table users and so on... the resulting value will be called "final_name"
                return $d;
            });

            //Here I do my customized filters.
            if(!empty((array)$advanced_filters)) {
                $response['data'] = $this->applyFilters($response['data']->toArray(), $advanced_filters);
            }
            
            return $response;

The problems are the following:

• with this query I can not sort/search on the new "final_name" field, because it is not in the query
• because the pagination and all DataTables variables are set at first, after I apply my custom filters, they work, but the results are shown across multiple pages, even though they would fit on one page

For the first problem, I've tried using accessors (but I can't use them without get() to order and search on query, so it defeats the purpose), I've tried making a join on users with users_info but it did not work (I suppose because it was already on a join/relationship).

For the second problem, I've tried to somehow do a DataTables::of($response['data'])->make('true'); after I apply the filters, but it doesn't seem to work. (in filters logic I just remove the ones that don't respect de filters)

Any idea what I can do? Or how can I optimize the speed of this? The query route has made me lost a lot of nights...

I hope I've explained this well enough. Thank you so much in advance!

[yajra]

with this query I can not sort/search on the new "final_name" field, because it is not in the query

The only way for this to work is have a custom query that will handle this field. You can use filterColumn or any laravel way to query a field.

because the pagination and all DataTables variables are set at first, after I apply my custom filters, they work, but the results are shown across multiple pages, even though they would fit on one page

Are you using query or collection? I think you're mixing things up since you parsed the dataTable response. I suggest you let the package handle the column transformation via addColumn, editColumn, etc.

$users = $project->users()
    ->whereIn('id', $users_teams)
    ->whereNotIn('id', $users_merges)
    ->select('id', 'name')
    ->with(['mergedUsers' => function ($query) use($project) {
        $query->where('project_id', $project->id)->select('project_id', 'user_id', 'primary_id');
    }]);
                                
return DataTables::of($users)
  ->editColumn('name', function($user) {
    return $user->info->name ?? $user->name;
  })
  ->toJson();

[mihai-waydev]

The only way for this to work is have a custom query that will handle this field. You can use filterColumn or any laravel way to query a field.

Sadly I have some actions that would require a really complex query, but I could try to make something out of it.

Are you using query or collection? I think you're mixing things up since you parsed the dataTable response. I suggest you let the package handle the column transformation via addColumn, editColumn, etc.

What I was trying to achieve was a combination of them. I needed to use query because I heard it was faster. And I wanted all columns to be searchable/sortable. If I'm not wrong, with the editColumn the values do indeed change and the sorting and searching work, but they are made on the initial value, not on the edited value resulted from editColumn, which is not something that I would want.

I even tried to do DataTables::of(query) then I do something with the result then DataTables::of($result) but it doesn't seem to work either. Maybe it makes sense that it doesn't work this way.

Thank you for your time, really appreciate it.

[mihai-waydev]

One other question I have is: is filterColumn safe with SQL injection? Let's say I write something like in the example from the docs, if the keyword is something harmful, is the data safe?

[yajra]

is filterColumn safe with SQL injection?

Yes, if you will follow the correct way of binding the values. It will depend on your implementation.