Global Search performance

[isecchin]

Summary of problem or feature request

Hi all, I've been using this package for a few months now and it's been working great for my requirements !

This weekend though, during some patches to boost the performance of my app, I've noticed that a few queries were taking a bit longer than I expected, and I think I managed to find what was causing it.

I'm not sure if this is intentional or not, but when looking through the source code of the package, I saw that on the globalSearch() method, we don't call the resolveRelationColumn() like we do on the column search and ordering.

The result of this is that it adds a subquery for each column of a relationship that is available on the global search instead of performing the join. On a table with just a few thousands of records, the SQL ran 3x slower than it did with the join.

Code snippet of problem

Say that you have an instance of the DataTables for a User model that has a relation to an "Employee" model, which stores all the basic information, such as first and last names, address, etc.

Now you want to allow the user to perform the global search with any of the parameters of the Employee relationship, this will add a subquery for each attribute, for example:

where (
    exists (select * from `employees`
        where `users`.`employee_id` = `employees`.`id`
            and LOWER(`employees`.`first_name`) LIKE '%ian%'
    )
    or exists (select * from `employees`
        where `users`.`employee_id` = `employees`.`id`
            and LOWER(`employees`.`last_name`) LIKE '%ian%'
    )
)

If we add the line

$column  = $this->resolveRelationColumn($column);

Right before the following one:

laravel-datatables/src/QueryDataTable.php

Line 751 in 7e45885

$this->compileQuerySearch($query, $column, $keyword);

We get a much nicer and faster query (with a single sub-query per relationship):

where (
    exists (select * from `employees`
        where `users`.`employee_id` = `employees`.`id`
            and (LOWER(`employees`.`first_name`) LIKE '%ian%' or LOWER(`employees`.`last_name`) LIKE '%ian%')
    )
)

So my question here is: is this intentional and we can't do this for the globalSearch or is it just something that went unnoticed?

I'm glad to open a PR with the patch if needed.

System details

• Operating System: MacOS 10.15.7
• PHP Version: 7.4
• Laravel Version: 7.28.1
• Laravel-Datatables Version: 9.11.0

[yajra]

@isecchin I guess it's intentional since we just basically loop on each related column. However, I do agree on your suggestion to group related columns to a single query.

I would gladly accept a PR for this one. Thanks 🍻

[isecchin]

Ok, great !
I'll start working on that, I need to understand a little bit better how everything works, cause simply adding that line as I mentioned above works fine for almost everything, but I saw that it can have an impact on the order by clause, so I need to do a bit more testing before coming with a final solution.