PHP code example of vuthaihoc / scout-crdb-driver

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

vuthaihoc / scout-crdb-driver example snippets

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

return new class extends Migration {
    public function up(): void
    {
        // Enable trigram extension (run once per database)
        DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');

        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('sku')->index();           // B-Tree for prefix/exact
            $table->string('title');
            $table->text('content');
            $table->string('status')->index();         // B-Tree for exact
            $table->timestamps();
        });

        // GIN Trigram index for ILIKE / word_similarity
        DB::statement('CREATE INDEX products_title_trgm ON products USING GIN (title gin_trgm_ops)');

        // Full-text Inverted index (simple dictionary = no stemming, good for Vietnamese)
        DB::statement("CREATE INDEX products_content_fts ON products USING GIN (to_tsvector('simple', content))");
    }
};

use Illuminate\Database\Eloquent\Model;
use Laravel\Scout\Searchable;
use Laravel\Scout\Attributes\SearchUsingFullText;
use Laravel\Scout\Attributes\SearchUsingPrefix;
use Hocvt\CrdbScout\Attributes\SearchUsingFuzzy;
use Hocvt\CrdbScout\Attributes\SearchUsingTrigram;
use Hocvt\CrdbScout\Attributes\SearchUsingExact;

class Product extends Model
{
    use Searchable;

    // Full-text search with 'simple' dictionary (no stemming, good for Vietnamese)
    #[SearchUsingFullText(['content'], ['language' => 'simple'])]

    // Fuzzy search: allows typos, threshold 0.4 (0.0 = very loose, 1.0 = exact)
    #[SearchUsingFuzzy(['title'], 0.4)]

    // Trigram substring: ILIKE '%keyword%'
    #[SearchUsingTrigram(['tags'])]

    // Prefix: ILIKE 'keyword%'  (uses B-Tree index)
    #[SearchUsingPrefix(['sku'])]

    // Exact match: col = ?
    #[SearchUsingExact(['status'])]
    public function toSearchableArray(): array
    {
        return [
            'content' => $this->content,
            'title'   => $this->title,
            'tags'    => $this->tags,
            'sku'     => $this->sku,
            'status'  => $this->status,
        ];
    }
}

// Searches across all configured columns
$products = Product::search('iphone')->get();

// Only search in 'title' and 'tags' columns
$products = Product::search('iphone')
    ->searchInFields(['title', 'tags'])
    ->get();

Product::search('wireless')
    ->searchInFields(['title', 'content'])
    ->where('status', 'active')
    ->paginate(15);

// config/scout-crdb.php
return [
    // Default threshold for #[SearchUsingFuzzy] when not explicitly set
    'default_fuzzy_threshold' => env('SCOUT_CRDB_FUZZY_THRESHOLD', 0.3),

    // Override the DB connection used for search queries (null = app default)
    'connection' => env('SCOUT_CRDB_CONNECTION', null),
];

// Use 'simple' dictionary (no stemming — recommended for Vietnamese or multi-language)
#[SearchUsingFullText(['content'], ['language' => 'simple'])]

// Use phrase matching
#[SearchUsingFullText(['content'], ['language' => 'english', 'mode' => 'phrase'])]

// Use websearch syntax (AND, OR, -)
#[SearchUsingFullText(['content'], ['language' => 'english', 'mode' => 'websearch'])]

bash
php artisan vendor:publish --tag=scout-crdb-config