PHP code example of vuthaihoc / laravel-db-portable

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

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

    

vuthaihoc / laravel-db-portable example snippets

// Numeric comparisons on a JSON key
Video::query()->whereJsonNumber('flags->word_sync_ratio', '>=', 0.8)->get();
Video::query()->whereJsonNumber('flags->ratio', '<', 0.2)->orWhereJsonNumber('flags->ratio', '>', 0.9)->get();

// Numeric ordering, optionally with NULLs (missing keys) last
ToeicExam::query()->orderByJsonNumber('meta->profile_index')->get();
Video::query()->orderByJsonNumber('flags->word_sync_ratio', 'desc', nullsLast: true)->get();

// NULLS LAST for any column (MySQL has no NULLS LAST)
Post::query()->orderByNullsLast('published_at', 'desc')->get();

// Aggregates of a JSON number
DB::table('plan_orders')->where('status', 1)->sumJson('plan_data->amount');
Order::query()->avgJson('meta->total');           // also minJson(), maxJson()

// Increment a JSON counter (a missing key counts as 0; NULL or "[]" starts from {})
Video::query()->whereKey($id)->incrementJson('video_reactions->like');
Video::query()->whereKey($id)->decrementJson('video_reactions->like', 2);

// Several counts and sums in one query (count(*) FILTER / sum(case ...) without writing either)
DB::table('orders')
    ->selectCountWhere('paid_orders', fn ($q) => $q->where('status', 'paid'))
    ->selectSumWhere('refunded_total', 'total', fn ($q) => $q->where('status', 'refunded'))
    ->selectAggregateWhere('max', 'total', fn ($q) => $q->where('channel', 'ios'), 'ios_max')   // count, sum, avg, min, max
    ->first();

// Subtotals per region and a grand total (rows where the grouped column is NULL)
DB::table('sales')
    ->select('region', 'product')
    ->selectRaw('sum(amount) as total')
    ->groupBy('region', 'product')
    ->rollup()          // call it last
    ->get();

Order::query()->readStale()->selectSumWhere('paid', 'total', fn ($q) => $q->where('status', 'paid'))->first();
DB::table('orders')->asOfTime('-10s')->count();              // or a DateTimeInterface
DB::table('orders')->asOfTime(now()->subHour())->readCurrent()->count();   // back to current data

use DbPortable\Portable;

DB::table('plan_orders')->select(Portable::jsonText('flags->device'))->get();       // default connection
$query->selectRaw('sum(' . Portable::on($query)->number('plan_data->amount')->getValue($query->getGrammar()) . ') as total');
Portable::on('crdb')->asText('tags');                                              // "tags"::text

Word::suggest('word', $search)->limit(10)->get();             // autocomplete
Word::suggest('word', $search, unaccent: true)->limit(10)->get();   // "chao" finds "chào"
Word::whereStartsWith('word', $search)->get();                // % and _ are matched literally
Word::whereContains('word', $search)->get();
Word::whereSimilar('word', $search)->orderBySimilarity('word', $search)->get();   // typo tolerant

Post::searchFullText(['title', 'body'], $search)->get();      // whereFullText(), most relevant first
Post::select('*')->selectFullTextRelevance(['title', 'body'], $search)->get();

Schema::create('videos', function (Blueprint $table) {
    $table->id();

    // A JSON column with a default value (arrays and scalars are encoded as JSON)
    $table->jsonWithDefault('tags', []);
    $table->jsonWithDefault('settings', ['theme' => 'dark'], binary: true);   // jsonb on PostgreSQL

    // A GIN index on a whole JSON column
    $table->jsonb('meta')->nullable();
    $table->jsonIndex('meta');

    // Descending indexes
    $table->timestamp('published_at')->nullable();
    $table->descIndex('published_at');
    $table->descIndex(['score' => 'desc', 'id' => 'asc'], 'videos_ranking');

    // An index on a JSON key, an index carrying extra columns, fuzzy search, an index on some rows
    $table->jsonKeyIndex('meta->source');
    $table->coveringIndex('video_id', ['title']);
    $table->trigramIndex('title');                    // PostgreSQL needs `create extension pg_trgm`
    $table->partialIndex('slug', 'deleted_at is null');

    // Driver-specific parts: a driver name (crdb, matrixone, mariadb...) wins over its family
    // (pgsql, mysql, sqlite); several keys separated by commas; "default" otherwise.
    $table->forDriver([
        'pgsql' => fn (Blueprint $table) => $table->index('title', null, 'gin'),       // PostgreSQL, CockroachDB
        'matrixone' => fn (Blueprint $table) => $table->fullText('title'),
        'default' => fn (Blueprint $table) => $table->index('title'),
    ]);
});

// Outside a blueprint, e.g. raw statements
Schema::forDriver([
    'pgsql' => fn () => DB::statement("create index files_meta_source on files ((meta->>'source'))"),
    'default' => fn () => null,
]);

bash
php artisan db-portable:scan --target=matrixone          # app/, database/, routes/
php artisan db-portable:scan app/Filament --target=mysql --target=sqlite
php artisan db-portable:scan --json --fail               # for CI

bash
php artisan db-portable:audit --from=crdb --to=matrixone
php artisan db-portable:audit --from=crdb --to=matrixone --table=videos --table=toeic_exam_user_logs