laravel postgresql enhanced

Laravel Packages: Database laravel postgresql enhanced
Categories:
Tags:

Zero-Downtime Migration

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();
        });
    }
}

Create Views

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'));

Dropping Views

use Tpetry\PostgresqlEnhanced\Support\Facades\Schema;

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

Fulltext Indexes

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']);
});

IP Networks

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

XML

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

Explain

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();

Language

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

Search Mode

Mode Description
plainto All words in the search-term have to exist at least once in the columns
phrase All words in the search-term have to appear in the exact same order in the columns.
websearch Complex search-term which supports quoting values, the or keyword and - to exclude a word but is missing parentheses. 
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();

Weighting

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

Lateral Subquery Joins

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

Lazy By Cursor

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);
    });
});

Where Clauses

Any/All

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

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

$query->whereAll($column, string $operator, iterable $values);
$query->whereNotAll($column, string $operator, iterable $values);
$query->orWhereAll($column, string $operator, iterable $values);
$query->orWhereNotAll($column, string $operator, iterable $values);
$query->whereAny($column, string $operator, iterable $values);
$query->whereNotAny($column, string $operator, iterable $values);
$query->orWhereAny($column, string $operator, iterable $values);
$query->orWhereNotAny($column, string $operator, iterable $values);

Boolean

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

Like

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

Between Symmetric

$min = $request->integer('min');
$min = $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);

Reference