Search Result pagination too much slow with length aware paginator in laravel

Question

I am trying to implement most relevant search in my application.

I have a search page to search customers with search input field. The input field is matched with some fields in the database. I have following db structure.

Customer

email, status

Field

name, slug, status

Field Value (Field Table contains fields like name, address, phone number etc. )

customer_id, field_id, value

I just take the input value from search page and match the values in field values table for fields like :

full_name, designation, address etc.

For every matching I have increased a weight parameter and later I will order it by the weight. Here is the code.

Search Key array:

$keyword = $request->get('keyword');

/**
* Split key into string array
*/
$keys = explode(' ', $keyword);

$searchItems = [];
foreach ($keys as $key){
    $key = str_replace(' ','', $key);
    $key = preg_replace('/[^a-zA-Z0-9_]/', '', $key);
    if($key){
        $searchItems[] = $key;
    }
}
$keys = $searchItems;

Search Logic

/**
 * Filter customer Ids based on search keys and field ids
 *
 * @param $fieldIds
 * @param $customerIds
 * @param $keys
 * @return mixed
 */
public function searchCustomersForYellowPage($fieldIds, $customerIds, $keys)
{
    $result =  $this->model->whereIn('customer_id', $customerIds)
                        ->whereIn('field_id', $fieldIds)
                        ->where(function ($query) use ($keys){
                            foreach ($keys as $key){
                                $query->orWhere('value', 'LIKE', '%'.$key.'%');
                            }
                        })
                        ->groupBy('customer_id')
                        ->pluck('customer_id');

    return $result->sortByDesc(function ($item) use($fieldIds, $keys){
                $weight = 0;
                foreach ($fieldIds as $fieldId){
                    foreach ($keys as $key){
                        $exists = $this->model->where('field_id', $fieldId)
                                            ->where('customer_id', $item)
                                            ->where('value', 'LIKE', '%'.$key.'%')
                                            ->exists();
                        if($exists)
                            $weight++;
                    }
                }
                return $weight;

    })->toArray();
}

In this function, $keys parameter is the same as explained above, $customerIds is ids of all customers and $fieldIds are id of the fields used for search.

This function returns array of all customer ids after search in most relevent order. Later I have used this array to get all customers and paginated with custom length aware paginator.

The problem I am facing is it takes long time and after pagination when I load each page it takes same long time. Is there any way to execute this code only once and paginating search results over multiple pages so that it takes long time for first case and quicker when loading second third .... pages.

The code you have used for weight might have low performance because for every 10 result it will be executed by 10*10*3 if 3 is your keys so total 300 query executions as well. — Dhaval Purohit
@DhavalPurohit I have four fields and keys length could be 3/4, So what could be the best way to do this ? — Sagar Gautam
why don't you just use the group count with customer id to find the weight? — Dhaval Purohit
in your first query of searchCustomersForYellowPage you have already used the group so use select(['customer_id',DB::raw('count(*) as cnt']) and last orderBy('cnt','desc') — Dhaval Purohit

Dhaval Purohit Dhaval Purohit · Accepted Answer · 2019-09-03T06:30:24

in your first query of searchCustomersForYellowPage you have already used the group so use select(['customer_id',DB::raw('count(*) as cnt']) and last orderBy('cnt','desc')

Search Result pagination too much slow with length aware paginator in laravel

1 Answers