PHP code example of tpetry / laravel-postgresql-enhanced

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

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

    

tpetry / laravel-postgresql-enhanced example snippets

use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Schema\Concerns\ZeroDowntimeMigration;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

class Test123 extends Migration
{
    use ZeroDowntimeMigration;

    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('user', function (Blueprint $table) {
            $table->string('name', 128)->change();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('user', function (Blueprint $table) {
            $table->string('name', 32)->change();
        });
    }
}

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createExtension('tablefunc');
Schema::createExtensionIfNotExists('tablefunc');

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropExtension('tablefunc');
Schema::dropExtensionIfExists('tablefunc');

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropExtension('tablefunc', 'fuzzystrmatch');
Schema::dropExtensionIfExists('tablefunc', 'fuzzystrmatch');

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createFunction(
  name: 'sales_tax',
  parameters: ['subtotal' => 'numeric'],
  return: 'numeric',
  language: 'plpgsql',
  body: '
    BEGIN
      RETURN subtotal * 0.06;
    END;
  '
);

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createFunction('sales_tax', ['subtotal' => 'numeric'], 'numeric', 'sql:expression', 'subtotal * 0.06', [
  'parallel' => 'safe',
  'volatility' => 'immutable',
]);

Schema::createFunction('search_user', ['pattern' => 'text'], ['id' => 'int', 'email' => 'text'], 'plpgsql', "
  BEGIN
    RETURN QUERY select user_id, contactemail from users where name ilike '%' || pattern || '%';
  END;
");

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropFunction('sales_tax');
Schema::dropFunctionIfExists('sales_tax');

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('projects', function (Blueprint $table): void {
    $table->trigger('rollup_quota', 'update_quota_by_projects()', 'AFTER INSERT OR DELETE');
});

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('projects', function (Blueprint $table): void {
    $table->dropTrigger('update_quota');
    $table->dropTriggerIfExists('update_quota');
});

use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createView('users_with_2fa', 'SELECT * FROM users WHERE two_factor_secret IS NOT NULL');
Schema::createViewOrReplace('users_without_2fa', DB::table('users')->whereNull('two_factor_secret'));

use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createView('users_with_2fa', DB::table('users')->select('id')->whereNull('two_factor_secret'), ['user_id']);

use Illuminate\Support\Facades\DB;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

// TODO simple example explaining the concept
Schema::createRecursiveView('viewname', 'SELECT id, col1, col2 FROM ....', ['id', 'col1', 'col2']);
Schema::createRecursiveViewOrReplace('viewname', 'SELECT id, col1, col2 FROM ....', ['id', 'col1', 'col2']);

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropView('myview');
Schema::dropViewIfExists('myview');

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropView('myview1', 'myview2');
Schema::dropViewIfExists('myview1', 'myview2');

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createMaterializedView('users_with_2fa', 'SELECT * FROM users WHERE two_factor_secret IS NOT NULL');
Schema::createMaterializedView('users_with_2fa', DB::table('users')->whereNull('two_factor_secret'));
Schema::createMaterializedView('users_with_2fa', DB::table('users')->select('id')->whereNull('two_factor_secret'), columns: ['user_id']);

Schema::createMaterializedView('very_slow_query_materialized', 'SELECT ...', withData: false);

Schema::dropMaterializedView('users_with_2fa');
Schema::dropMaterializedViewIfExists('users_with_2fa');

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::refreshMaterializedView('users_with_2fa');
Schema::refreshMaterializedView('users_with_2fa', concurrently: true);
Schema::refreshMaterializedView('users_with_2fa', withData: false);
Schema::refreshMaterializedView('users_with_2fa', withData: true);

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function(Blueprint $table) {
    $table->uniqueIndex('email');
});

use Illuminate\Database\Migrations\Migration;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

return new class extends Migration
{
    public $withinTransaction = false;

    public function up(): void
    {
        Schema::table('blog_visits', function (Blueprint $table) {
            $table->index(['url', 'ip_address'])->concurrently();
        });
    }
};

use Illuminate\Database\Query\Builder;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::create('subscriptions', function(Blueprint $table) {
    $table->id('user_id');
    $table->timestampTz('cancelled_at');


    $table->uniqueIndex(['user_id', 'cancelled_at'])->nullsNotDistinct();
});

use Illuminate\Database\Query\Builder;
use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function(Blueprint $table) {
    $table->uniqueIndex('email')->where("deleted_at IS NULL");
    // or:
    $table->uniqueIndex('email')->where(fn (Builder $condition) => $condition->whereNull('deleted_at'));
});

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function(Blueprint $table) {
    // The query "SELECT firstname, lastname FROM users WHERE email = '[email protected]'" can be executed as an index-only scan without loading the table data
    $table->index('email')->

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('invoices', function(Blueprint $table) {
    $table->index(['target', 'division', 'date'])->ifNotExists();
});

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('bookmarks', function(Blueprint $table) {
    $table->index('data')->algorithm('gin')->with(['fastupdate' => false]);
});

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function(Blueprint $table) {
    $table->uniqueIndex('(LOWER(email))', 'users_email_unique');
    $table->index(['firstname ASC NULLS FIRST', 'lastname ASC NULLS FIRST']);
    $table->index('attributes jsonb_path_ops')->algorithm('gin');
});

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('book', function (Blueprint $table) {
    $table->fullText(['title', 'description'])
        ->language('spanish')
        ->weight(['A', 'B']);
});

use Tpetry\PostgresqlEnhanced\Query\Builder;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::createDomain('price', 'numeric(9,2)');
Schema::createDomain('price', 'numeric(9,2)', 'VALUE >= 0');
Schema::createDomain('price', 'numeric(9,2)', fn (Builder $query) => $query->where('VALUE', '>=', 0));

Schema::create('products', function (Blueprint $table): void {
  $table->id();
  $table->string('item_name');
  $table->domain('item_price', 'price');
  $table->timestampsTz();
});

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

// To drop the validation condition:
Schema::changeDomainConstraint('price', null);

// To change validation condition:
Schema::changeDomainConstraint('price', 'VALUE > 0');
Schema::changeDomainConstraint('price', fn (Builder $query) => $query->where('VALUE', '>', 0));

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropDomain('price');
Schema::dropDomainIfExists('price');

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::dropDomain('price', 'license_plate');
Schema::dropDomainIfExists('price', 'license_plate');

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('sessions', function (Blueprint $table): void {
    // make the table unlogged
    $table->unlogged();
    
    // make the table crash-safe again
    $table->unlogged(false);
});

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('sessions', function (Blueprint $table): void {
    $table->with([
        // Tune statistics generation for tables with millions of records
        'autovacuum_analyze_scale_factor' => 0.02,
        // Tune table for frequent UPDATE statements
        'fillfactor' => 90,
    ]);
});

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('books', function (Blueprint $table): void {
    // @see https://www.postgresql.org/docs/current/storage-toast.html
    $table->string('summary')->compression('lz4');
});

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::table('users', function (Blueprint $table): void {
    $table->boolean('acl_admin')->initial(false);
    $table->boolean('acl_read')->initial(false)->default(true);
});

Schema::table('users', function (Blueprint $table): void {
    $table->jsonb('email')->using('jsonb_build_array(email)')->change();
});

// @see https://www.postgresql.org/docs/current/arrays.html
$table->integerArray(string $column);

// @see https://www.postgresql.org/docs/current/rangetypes.html
$table->bigIntegerRange(string $column);
$table->bigIntegerMultiRange(string $column);
$table->dateRange(string $column);
$table->dateMultiRange(string $column);
$table->decimalRange(string $column);
$table->decimalMultiRange(string $column);
$table->integerRange(string $column);
$table->integerMultiRange(string $column);
$table->timestampRange(string $column);
$table->timestampMultiRange(string $column);
$table->timestampTzRange(string $column);
$table->timestampTzMultiRange(string $column);

// @see https://www.postgresql.org/docs/current/datatype-bit.html
$table->bit(string $column, int $length = 1);
$table->varbit(string $column, ?int $length = null);

// @see https://www.postgresql.org/docs/current/citext.html
$table->caseInsensitiveText(string $column);

// @see https://www.postgresql.org/docs/10/datatype-textsearch.html
$table->tsvector(string $column);

// @see https://www.postgresql.org/docs/current/datatype-net-types.html
$table->ipNetwork(string $column);

// @see https://www.postgresql.org/docs/current/hstore.html
$table->hstore(string $column);

$table->identity(always: true)->primary();
$table->identity('uniqid');

// @see https://www.postgresql.org/docs/current/isn.html
$table->europeanArticleNumber13(string $column);
$table->internationalStandardBookNumber(string $column);
$table->internationalStandardBookNumber13(string $column);
$table->internationalStandardMusicNumber(string $column);
$table->internationalStandardMusicNumber13(string $column);
$table->internationalStandardSerialNumber(string $column);
$table->internationalStandardSerialNumber13(string $column);
$table->universalProductNumber(string $column);

// @see https://www.postgresql.org/docs/current/ltree.html
$table->labelTree(string $column);

// @see https://github.com/pgvector/pgvector
$table->vector(string $column, int $dimensions = 1536);

// @see https://www.postgresql.org/docs/current/datatype-xml.html
$table->xml(string $column);

DB::table('migrations')->where('batch', 1)->explain()->dd();

// Output:
// array:1 [
//  0 => """
//    Seq Scan on public.migrations  (cost=0.00..11.75 rows=1 width=524)\n
//      Output: id, migration, batch\n
//      Filter: (migrations.batch = 1)\n
//    Settings: search_path = 'public'\n
//    Planning Time: 0.370 ms
//    """
//]

DB::table('migrations')->where('batch', 1)->explain(analyze:true)->dd();

// Output:
// array:1 [
//  0 => """
//    Seq Scan on public.migrations  (cost=0.00..11.75 rows=1 width=524) (actual time=0.014..0.031 rows=1 loops=1)\n
//      Output: id, migration, batch\n
//      Filter: (migrations.batch = 1)\n
//      Buffers: shared hit=1\n
//    Settings: search_path = 'public'\n
//    Planning:\n
//      Buffers: shared hit=61\n
//    Planning Time: 0.282 ms\n
//    Execution Time: 0.100 ms
//    """
//]

Book::whereFullText(['title', 'description'], 'PostgreSQL')->get();

Book::whereFullText(['title', 'description'], 'PostgreSQL', ['language' => 'spanish'])->get();

    Book::whereFullText(['title', 'description'], 'PostgreSQL', ['mode' => 'plain'])->get();
    

    Book::whereFullText(['title', 'description'], 'PostgreSQL database', ['mode' => 'phrase'])->get();
    

    Book::whereFullText(['title', 'description'], '"PostgreSQL database" -MySQL', ['mode' => 'websearch'])->get();
    

Book::whereFullText(['title', 'description'], '"PostgreSQL', ['weight' => ['A', 'B']])->get();

User::select('users.email', 'orders.*')
    ->leftJoinSubLateral(
        Order::whereColumn('orders.user_id', 'users.id')
            ->orderBy('price', 'desc')
            ->limit(3),
        'orders',
    );

use Illuminate\Support\Facades\DB;

$inactiveUsers = DB::table('users')
    ->where('lastlogin_at', '<', now()->subYear())
    ->get();
foreach ($inactiveUsers as $inactiveUser) {
  $inactiveUser->delete();
}
dump('deleted Users', $inactiveUsers);

// do this instead:

$inactiveUsers = DB::table('users')
    ->where('lastlogin_at', '<', now()->subYear())
    ->deleteReturning();
dump('deleted Users', $inactiveUsers);

$query->withExpression($as, $query, $options = []);

$lastLoginQuery = Login::query()
    ->selectRaw('user_id, MAX(created_at) AS last_login_at')
    ->groupBy('user_id');
User::query()
    ->withExpression('users_lastlogin', $lastLoginQuery)
    ->join('users_lastlogin', 'users_lastlogin.user_id', 'users.id')
    ->where('users_lastlogin.created_at', '>=', now()->subHour());

use Illuminate\Support\Facades\DB;

DB::transaction(function() {
    User::lazyByCursor()->each(function (User $user) {
        dump($user);
    });

    // Maximum 500 rows should be loaded into memory for every chunk.
    User::lazyByCursor(500)->each(function (User $user) {
        dump($user);
    });

    // Lazy loading rows also works for the query builder.
    DB::table('users')->where('active', true)->lazyByCursor()->each(function (object $user) {
        dump($user);
    });
});

// instead of:
$query->where('invoice', 'like', 'RV-%')->orWhere('invoice', 'like', 'RZ-%');
$query->where('json', '??', 'key1')->where('json', '??', 'key2');

// you can do:
$query->whereAnyValue('invoice', 'like', ['RV-%', 'RZ-%']);
$query->whereAllValues('json', '??', ['key1', 'key2']);

$query->whereAllValues($column, string $operator, iterable $values);
$query->whereNotAllValues($column, string $operator, iterable $values);
$query->orWhereAllValues($column, string $operator, iterable $values);
$query->orWhereNotAllValues($column, string $operator, iterable $values)
$query->whereAnyValue($column, string $operator, iterable $values);
$query->whereNotAnyValue($column, string $operator, iterable $values);
$query->orWhereAnyValue($column, string $operator, iterable $values);
$query->orWhereNotAnyValue($column, string $operator, iterable $values)

$query->whereBoolean($column, bool $value);
$query->whereNotBoolean($column, bool $value);
$query->orWhereBoolean($column, bool $value);
$query->orWhereNotBoolean($column, bool $value);

$query->whereLike($column, $value, $caseSensitive = false);
$query->orWhereLike($column, $value, $caseSensitive = false);

$min = $request->integer('min');
$max = $request->integer('max');

// before:
$query->whereBetween('price', [min($min, $max), max($min, $max)]);

// now:
$query->whereBetweenSymmetric('price', [$min, $max]);

$query->whereBetweenSymmetric($column, iterable $values);
$query->whereNotBetweenSymmetric($column, iterable $values);
$query->orWhereBetweenSymmetric($column, iterable $values);
$query->orWhereNotBetweenSymmetric($column, iterable $values);

$query->whereIntegerArrayMatches($column, string $query);
$query->orWhereIntegerArrayMatches($column, string $query);

// The tags column should have values 3, 4, 5 or 6 and not 7.
$query->whereIntegerArrayMatches('tags', '3&4&(5|6)&!7');

$query->orderBy($column, string $direction = 'asc'|'desc', string $nulls = 'default'|'first'|'last');
$query->orderByNullsFirst($column, string $direction = 'asc'|'desc', string $nulls = 'default'|'first'|'last');
$query->orderByNullsLast($column, string $direction = 'asc'|'desc', string $nulls = 'default'|'first'|'last');

// Sort the table by the age descending with all NULL values presented last.
$query->orderBy('age', 'desc', nulls: 'last');
$query->orderByNullsLast('age', 'desc');

$query->orderByVectorSimilarity($column, $vector, string $distance = 'cosine'|'l2');

// The five rows with the highest similarity to the provided embeddings.
$query->orderByVectorSimilarity('embeddings', [0.9569, 0.1113, 0.0107])->limit(5);

use Tpetry\PostgresqlEnhanced\Schema\Blueprint;
use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

Schema::create('example', function (Blueprint $table) {
    $table->id();
    $table->string('text');
    $table->string('text_uppercase')->storedAs('UPPER(text)');
});

$example = Example::create(['text' => 'test']);
dump($example); // ['id' => 1, 'text' => 'test']

$example->refresh();
dump($example); // ['id' => 1, 'text' => 'test', 'text_uppercase' => 'TEST']

$example->fill(['text' => 'test2'])->save();
dump($example); // ['id' => 1, 'text' => 'test2']

$example->refresh();
dump($example); // ['id' => 1, 'text' => 'test', 'text_uppercase' => 'TEST2']

use Illuminate\Database\Eloquent\Model;
use Tpetry\PostgresqlEnhanced\Eloquent\Concerns\RefreshDataOnSave;

class Example extends Model
{
    use RefreshDataOnSave;

    // ...
}

$example = Example::create(['text' => 'test']);
dump($example); // ['id' => 1, 'text' => 'test', 'text_uppercase' => 'TEST']

$example->fill(['text' => 'test2'])->save();
dump($example); // ['id' => 1, 'text' => 'test2', 'text_uppercase' => 'TES2T']

use Illuminate\Database\Eloquent\Model;
use Tpetry\PostgresqlEnhanced\Eloquent\Concerns\AutomaticDateFormat;

class Example extends Model
{
    use AutomaticDateFormat;

    // ...
}

use Illuminate\Database\Eloquent\Model;
use Tpetry\PostgresqlEnhanced\Eloquent\Concerns\AutomaticDateFormatWithMilliseconds;

class Example extends Model
{
    use AutomaticDateFormatWithMilliseconds;

    // ...
}

BlogVisit::select([
    'url',
    new TimestampBin('created_at', DateInterval::createFromDateString('5 minutes')),
    new Count('*'),
])->groupBy(
    'url',
    new TimestampBin('created_at', DateInterval::createFromDateString('5 minutes'))
);

use Tpetry\PostgresqlEnhanced\Expressions\Uuid7;

Schema::create('comments', function (Blueprint $table) {
    $table->id();
    $table->uuid()->default(new Uuid7())->unique();
    $table->text('text');
});