Fulltext search

Mysql & PostgreSQL

Laravel, Lumen, Maravel and Maravelith offer the Fulltext search for MySQL and PostgreSQL. However this is not built-in maravel-rest-wizard or laravel-crud-wizard-free but here is how you can add it.

namespace App\Services;

use App\Models\Item;
use Illuminate\Database\Eloquent\Builder;
use MacropaySolutions\LaravelCrudWizard\DataTransferObjects\ListFilterDTO;
use MacropaySolutions\LaravelCrudWizard\Services\BaseResourceService;

class ItemsService extends BaseResourceService
{
    /**
     * @inheritDoc
     */
    protected function setBaseModel(): void
    {
        $this->model = new Item();
    }

    /**
     * @inheritDoc
     */
    public function list(array $request, ListFilterDTO $listFilterDTO): Builder
    {
        $builder = parent::list($request, $listFilterBuilder);


        // do your magic here

        if ('' !== $request['search'] ?? '') {
            // This query runs in natural language mode automatically.
            $builder->whereFullText(['title', 'content'], $request['search'])
        }
        
        if ('' !== $request['searchExpanded'] ?? '') {
            // You can also enable query expansion or switch to boolean mode using the options array:
            $builder->whereFullText(['title', 'content'], $request['searchExpanded'], ['expanded' => true])
        }
        
        if ('' !== $request['searchBooleanAll'] ?? '') {
            // if you want all words from the search to be present in each result
            $builder->whereFullText(
              ['title', 'content'],
              \collect(explode(' ', \preg_replace('/\\p{P}/u', '', $request['searchBooleanAll'])))->map(fn(string $word): string => '+' . $word)->implode(' '),
              ['mode' => 'boolean'])
        }
        
        if ('' !== $request['searchBooleanAllLike'] ?? '') {
            // if you want all words (with starts with) from the search to be present in each result
            $builder->whereFullText(
              ['title', 'content'],
              \collect(explode(' ', \preg_replace('/\\p{P}/u', '', $request['searchBooleanAllLike'])))->map(fn(string $word): string => '+' . $word . '*')->implode(' '),
              ['mode' => 'boolean'])
        }

        return $builder;
    }
}

Note that ft_min_word_len in MySql is usually 3 or 4 meaning the index will disregard words shorter than that. PostgreSQL has no such limit but it excludes common stop words from the index.

Elasticseach

For Elasticsearch SQL plugin the whereFullText will not work but, you can use raw queries because it allows using SQL-like syntax for full-text searches, translating functions like MATCH_QUERY, QUERY, and related ones into Elasticsearch's query DSL.

MATCH_QUERY Function

To search for text within a single field, use MATCH_QUERY or MATCHQUERY. These functions perform a standard full-text search where the input text is analyzed before matching.

Example:

SELECT * FROM items WHERE MATCH_QUERY(address, 'Holmes')

An alternate syntax is also supported:

SELECT * FROM items WHERE address = MATCH_QUERY('Holmes')

QUERY Function

Use the QUERY function to parse a query string with operators like OR, AND, wildcards or regex. It supports complex search expressions.

Example:

SELECT * FROM items WHERE QUERY('address:Lane OR address:Street')

MATCH Function

Search for documents where the address field contains "Holmes":

SELECT * FROM items WHERE MATCH(address, 'Holmes');

Search across multiple fields, giving higher relevance (boost) to matches in the title field:

SELECT * FROM items WHERE MATCH('address^2,title^5', 'example text');

This prioritizes matches in the title field over the address field in scoring.

Apply advanced matching options like operator, fuzziness, and minimum_should_match:

SELECT *, SCORE() FROM items
WHERE MATCH(title, 'to the star', 'operator=OR;fuzziness=AUTO:1,5;minimum_should_match=1') 
ORDER BY SCORE() DESC 
LIMIT 2;