Complex relational search using Laravel Scout (Meilisearch driver) - Laravel

Question

Summary

I'm new to Laravel scout and Meilisearch, I'm building a CRM application with Laravel, there already a index method I have that returns paginated data after fetching from database.

However the query is very dynamic, I mean it depends on many things like: if the user doesnt have global permissions, then limit the query to records where he/she has permission to that specific record (using Many-To-Many relation)

Here's my current code, we want the same logic but also Laravel Scout implemented. I've tried to explain the code line by line with comments:

    public function index(Request $request, string $pipeline_id)
    {
        $request->validate([
            'search' => ['nullable', 'string'],
            'filters.*' => ['nullable', 'array'],
            'filters.*.*' => ['string'],
        ]);

        $pipelineQuery = Pipeline::query();

        // If user doesn't have "View Pipelines" permission.
        if ($request->user()->cannot('View Pipelines')) {
            // Limit the Pipeline query to pipelines which current user can access.
            $pipelineQuery = $request->user()->pipelines()
                ->where('user_can_view_leads', true)
                ->orWhere('user_can_view_all_leads', true);
        }

        // Fetch the pipeline from Database.
        $pipeline = $pipelineQuery->findOrFail($pipeline_id);

        $leadQuery = $pipeline->leads();

        // If user doesn't have "View Leads" permission, as well as "user_can_view_all_leads" permission for this pipeline.
        if ($request->user()->cannot('View Leads') && (! $pipeline->pipeline_user_pivot?->user_can_view_all_leads)) {
            // Limit the Lead Query to leads that only the current user has access to.
            $leadQuery = $request->user()->leads()->where('pipeline_id', $pipeline->id);
        }

        $filterableFields = [
            'stage_id',
        ];

        foreach (request('filters', []) as $fieldName => $values) {
            // Filter using requested filter if user is allowed to filter with that field.
            if (in_array($fieldName, $filterableFields)) {
                $leadQuery->whereIn($fieldName, $values);
            }
        }

        // Finally fetch the leads from Database
        $leads = $leadQuery->with(['customer', 'address'])->paginate(10);

        return $leads;
    }

The Problem

Meilisearch works very differently than traditional databases, in MySQL we can utilize relations.

With meilisearch we need to index the data in a format so that we can search, filter based on the indexed data.(as suggested here: https://laravel.com/docs/10.x/scout#modifying-the-import-query)

But in my case, the User-Lead is a many to many relation, User can have thousands of Leads, so having that to import everytime to Meilisearch doesn't sound very efficient.

Even if we import thoes data to Meilisearch, and someone update any record, that update won't be instantly reflected in Meilisearch index, as Meilisearch updates indexes asynchronously.

Solutions I thought of:

• I thought we can just search the Leads first and get the IDs, like: Lead::search('...')->keys(), then query the database with my where clauses and fetch the data.

  But meilisearch by default limits the searched results to 20, which is probably a good thing. otherwise we can get thousands of search results, and all of them would load in memory.

  Now, it's possible that the first 20 best searched results that meilisearch returned, most of the records didn't pass the conditions (database where clauses) and that much data is not sufficiant to fill the ->paginate(10)

• Another soludiont I thought of, first I can get the IDs from the database, then search using meilisearch with Meilisearch's filtering with those IDs.

  However, it's possible that database has many records and loading all those IDs won't be efficient.

--

I'm not sure, what would be the most efficient/optimal way of doing this.