Scout / Meilisearch Support

[frknakk]

I have implemented a custom solution for scout/meilisearch support in my datatables around a month ago. Here are some explanations of it so you get the idea. If there is interest in this, i can clean this up, try to make it generally compatible to all scout engines (currently only meilisearch) and create a pull request @yajra

The core idea was to keep the normal search/ordering/filter abilities and add the meilisearch engine on top of it so both can be used together. If the meilisearch engine is not indexed yet or has a downtime, the system automatically falls back to the normal search engine. Additionally it is possible to define both meilisearch filters and normal filters so that you can join and filter other tables/cols after the meilisearch search was already done.

In my case there are static products (that are indexed to meilisearch and searched via meilisearch), but these products had dynamic prices that were changing too often to index them properly. So the search engine first searches for all products matching the keyword and then adds a mysql filter on top of them.

Example usage:

public function dataTable(QueryBuilder $query): EloquentDataTable
{
	return datatables()
		->eloquent($query)
		
		// Enable scout search engine for App\Models\Product
		->enableScoutSearchWith(Product::class)
		
		// Add filters for the scout search engine
		->scoutFilter(function ($keyword) {
			// ...
		})
		
		// Add filters that are applied after getting the results of the scout search engine / on the fallback search engine
		->filter(function ($query) {
			// ...
		}, true);
}

Process:

1. User types in search keyword
2. Check if scout search engine is enabled (enableScoutSearchWith method)
3. Apply scout search (or if it fails, fall back to default search)
   1. Raw search for the keyword + filters from scoutFilter method (limit: 1000 / all)
   2. Pluck the primary key of the results
   3. Apply a whereIn('id', all_search_result_ids) to the query
   4. Apply a orderBy to the query with the ordering of the search results
   5. Continue the normal process (adding the filters from filter method)

If there are any questions, feel free to hit me up :)

[yajra]

@frknakk thank you for taking the time to implement this. I did attempt to support Scout via https://github.com/yajra/laravel-datatables-scout but wasn't able to update it accordingly.

Anyway, I like your idea and implementation flow. Please submit a PR and will review it ASAP.

---

Question:

So the search engine first searches for all products matching the keyword and then adds a mysql filter on top of them.

It's a great feat that you accomplished this. Does it cause an N+1 issue?

Looking forward to your PR. Thanks!

[frknakk]

Question:

So the search engine first searches for all products matching the keyword and then adds a mysql filter on top of them.

Does it cause an N+1 issue?

Nope. The queries can get ugly if there are a lot of results, but it's limited to 1000 hits per default anyway for most engines.

Queries for default search

1. select count(*) as aggregate from `products`
2. select count(*) as aggregate from `products` where (LOWER(`products`.`id`) LIKE ? or LOWER(`products`.`name`) LIKE ?
3. select * from `products` where (LOWER(`products`.`id`) LIKE ? or LOWER(`products`.`name`) LIKE ? order by `id` desc limit 10 offset 0

Queries for my scout search implementation
Before: fetch product id's via scout engine

1. select count(*) as aggregate from `products`
2. select count(*) as aggregate from (select * from `products` where `id` in (5865, 6487) order by FIELD(id, '5865','6487')) count_row_table
3. select * from `products` where `id` in (5865, 6487) order by FIELD(id, '5865','6487') limit 10 offset 0