Filters query take a long time

[ThanhSonITNIC]

Laravel Rest Api Version

2.4

Laravel Version

10

PHP Version

8.1

Database Driver & Version

mysql

Description

Filters query take a long time in both local and server

Steps To Reproduce

• Search with filters [POST] /api/products/search

{
  "search": {
    "filters": [
        {
            "field": "id",
            "operator": "in",
            "value": ["1", "2"]
        }
    ]
  }
}

[2024-01-22 03:09:08] local.DEBUG: select * from `personal_access_tokens` where `personal_access_tokens`.`id` = ? limit 1 {"bindings":["1"],"time":2.84} 
[2024-01-22 03:09:08] local.DEBUG: select * from `users` where `users`.`id` = ? limit 1 {"bindings":["1"],"time":0.54} 
[2024-01-22 03:09:08] local.DEBUG: update `personal_access_tokens` set `last_used_at` = ?, `personal_access_tokens`.`updated_at` = ? where `id` = ? {"bindings":["2024-01-22 03:09:08","2024-01-22 03:09:08",1],"time":2.59} 

> take 20s here

[2024-01-22 03:09:28] local.DEBUG: select count(*) as aggregate from `products` where `seller_id` = ? and (`products`.`id` in (?, ?)) {"bindings":["1","1","2"],"time":1.99} 
[2024-01-22 03:09:28] local.DEBUG: select * from `products` where `seller_id` = ? and (`products`.`id` in (?, ?)) order by `products`.`id` desc limit 50 offset 0 {"bindings":["1","1","2"],"time":0.76} 

• Search without filters [POST] /api/products/search

[2024-01-22 03:10:53] local.DEBUG: select * from `personal_access_tokens` where `personal_access_tokens`.`id` = ? limit 1 {"bindings":["1"],"time":20.95} 
[2024-01-22 03:10:53] local.DEBUG: select * from `users` where `users`.`id` = ? limit 1 {"bindings":["1"],"time":0.66} 
[2024-01-22 03:10:53] local.DEBUG: update `personal_access_tokens` set `last_used_at` = ?, `personal_access_tokens`.`updated_at` = ? where `id` = ? {"bindings":["2024-01-22 03:10:53","2024-01-22 03:10:53",1],"time":3.55} 

> take 1s here

[2024-01-22 03:10:54] local.DEBUG: select count(*) as aggregate from `products` where `seller_id` = ? {"bindings":["1"],"time":0.71} 
[2024-01-22 03:10:54] local.DEBUG: select * from `products` where `seller_id` = ? order by `products`.`id` desc limit 50 offset 0 {"bindings":["1"],"time":0.66} 

[ThanhSonITNIC]

after debug, i found this problem

laravel-rest-api/src/Rules/SearchRules.php

Lines 99 to 143 in 3e7667e

	// @TODO: For now it's prohibited to have more than one nested depth, is this needed ?
	/**
	* Define the validation rules for filters within the search request.
	*
	* @param \Lomkit\Rest\Http\Resource $resource
	* @param string $prefix
	* @param bool $isMaxDepth
	*
	* @return array
	*/
	public function filtersRules(\Lomkit\Rest\Http\Resource $resource, string $prefix, bool $isMaxDepth = false)
	{
	$rules = array_merge(
	[
	$prefix.'.*.field' => [
	Rule::in($resource->getNestedFields($this->request)),
	"required_without:$prefix.*.nested",
	'string',
	],
	$prefix.'.*.operator' => [
	Rule::in('=', '!=', '>', '>=', '<', '<=', 'like', 'not like', 'in', 'not in'),
	'string',
	],
	$prefix.'.*.value' => [
	"exclude_if:$prefix.*.value,null",
	"required_without:$prefix.*.nested",
	],
	$prefix.'.*.type' => [
	'sometimes',
	Rule::in('or', 'and'),
	],
	$prefix.'.*.nested' => !$isMaxDepth ? [
	'sometimes',
	"prohibits:$prefix.*.field,$prefix.*.operator,$prefix.*.value",
	'prohibits:value',
	'array',
	] : [
	'prohibited',
	],
	],
	!$isMaxDepth ? $this->filtersRules($resource, $prefix.'.*.nested', true) : []
	);
	return $rules;
	}

this is a lot of data with size 92MB

[ThanhSonITNIC]

#100

[GautierDele]

Hello @ThanhSonITNIC,

Thanks for your contribution, if I understand well, in your case the problem is that the fields in the rules are taking a lot of memory ? How much resources do you have in your project ?
I'll investigate also because a few weeks ago I decided to be aware about nested fields because in other places it took times to calculate 😄

[ThanhSonITNIC]

I have 24 resources, each resource only 2 to 5 relations, and around 10 fields each resource
this function generate many nested relation fields

laravel-rest-api/src/Rules/SearchRules.php

Line 114 in 3e7667e

Rule::in($resource->getNestedFields($this->request)),

in some resources it throw error memory size exhausted
this pull request #100 can limit useable fields

[GautierDele]

@ThanhSonITNIC I can confirm on my side on a large project i went from 3.5 sec to 300ms on a search