PHP code example of tpetry / laravel-query-expressions

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

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

    

tpetry / laravel-query-expressions example snippets

// Instead of:
User::query()
    ->when(isPostgreSQL(), fn ($query) => $query->selectRaw('coalesce("user", "admin") AS "value"'))
    ->when(isMySQL(), fn ($query) => $query->selectRaw('coalesce(`user`, `admin`) AS `value`'))

// You can use:
User::select(new Alias(new Coalesce(['user', 'admin']), 'count'));

// Aggregate multiple statistics with one query for dashboards:
Movie::select([
    new CountFilter(new Equal('released', new Value(2021))),
    new CountFilter(new Equal('released', new Value(2022))),
    new CountFilter(new Equal('genre', new Value('Drama'))),
    new CountFilter(new Equal('genre', new Value('Comedy'))),
])->where('streamingservice', 'netflix');

use Tpetry\QueryExpressions\Value\Value;

new Value(42);
new Value("Robert'); DROP TABLE students;--");

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Language\Alias;
use Tpetry\QueryExpressions\Value\Value;

new Alias(string|Expression $expression, string $name)

User::select([
    new Alias('last_modified_at', 'modification_date'),
    new Alias(new Value(21), 'min_age_threshold'),
])->get();

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Language\Alias;
use Tpetry\QueryExpressions\Language\Cast;

new Cast(string|Expression $expression, 'int'|'bigint'|'float'|'double' $type)

Invoice::select([
    new Alias(new Cast('invoice_number', 'int')),
])->get();

use Tpetry\QueryExpressions\Language\{
    CaseGroup, CaseRule,
};

new CaseGroup(CaseRule[] $when, string|Expression|null $else = null)

// ALTER TABLE users ADD COLUMN "status" varchar(255) NOT NULL GENERATED ALWAYS AS (
//   CASE
//     WHEN ("reward_points" > 500000) THEN 'gold'
//     WHEN ("reward_points" > 100000) THEN 'silver'
//     WHEN ("reward_points" > 50000) THEN 'bronze'
//     ELSE 'none'
//   END
// ) STORED
Schema::table('users', function (Blueprint $table) {
    $statusByRewardPoints = new CaseGroup(
        when: [
            new CaseRule(new Value('gold'), new GreaterThan('reward_points', new Value(500_000))),
            new CaseRule(new Value('silver'), new GreaterThan('reward_points', new Value(100_000))),
            new CaseRule(new Value('bronze'), new GreaterThan('reward_points', new Value(50_000))),
        ],
        else: new Value('none'),
    );

    $table->string('status')->storedAs($statusByRewardPoints);
});

use Tpetry\QueryExpressions\Language\{CaseGroup, CaseRule};
use Tpetry\QueryExpressions\Language\Alias;
use Tpetry\QueryExpressions\Operator\Comparison\{Equal};
use Tpetry\QueryExpressions\Value\Value;
use App\Models\User;

User::query()
  ->select([
    "id",
    new Alias(
      new CaseGroup(
        when: [
          new CaseRule(new Value("Admin"), new Equal("role", new Value(3))),
          new CaseRule(new Value("Editor"), new Equal("role", new Value(2))),
          new CaseRule(new Value("Viewer"), new Equal("role", new Value(1)))
        ],
        else: new Value("Unknown Role")
      ),
      "role_name"
    )
  ])
  ->get();

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Operator\Arithmetic\{
    Add, Divide, Modulo, Multiply, Power, Subtract,
};
use Tpetry\QueryExpressions\Value\Value;

new Add(string|Expression $value1, string|Expression $value2, string|Expression ...$values);
new Divide(string|Expression $value1, string|Expression $value2, string|Expression ...$values);
new Modulo(string|Expression $value1, string|Expression $value2, string|Expression ...$values);
new Multiply(string|Expression $value1, string|Expression $value2, string|Expression ...$values);
new Power(string|Expression $value1, string|Expression $value2, string|Expression ...$values);
new Subtract(string|Expression $value1, string|Expression $value2, string|Expression ...$values);

// UPDATE user_quotas SET credits = credits - 15 WHERE id = 1985
$quota->update([
    'credits' => new Subtract('credits', new Value(15)),
]);

// SELECT id, name, (price - discount) * 0.2 AS vat FROM products
Product::select([
    'id',
    'name',
    new Alias(new Multiply(new Subtract('price', 'discount'), new Value(0.2)), 'vat')
])->get();

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Operator\Bitwise\{
    BitAnd, BitNot, BitOr, BitXor, ShiftLeft, ShiftRight,
};
use Tpetry\QueryExpressions\Value\Value;

new BitAnd(string|Expression $value1, string|Expression $value2);
new BitNot(string|Expression $value);
new BitOr(string|Expression $value1, string|Expression $value2);
new BitXor(string|Expression $value1, string|Expression $value2);
new ShiftLeft(string|Expression $value, string|Expression $times);
new ShiftRight(string|Expression $value, string|Expression $times);

// SELECT * FROM users WHERE (acl & 0x8000) = 0x8000
User::where(new BitAnd('acl', new Value(0x8000)), 0x8000)
    ->get();

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Operator\Comparison\{
    Between, DistinctFrom, Equal, IsNull, GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual,
    NotDistinctFrom, NotEqual, NotIsNull
};
use Tpetry\QueryExpressions\Function\Comparison\{
    StrListContains
};
use Tpetry\QueryExpressions\Operator\Logical\{
    CondAnd, CondNot, CondOr, CondXor
};

new Between(string|Expression $value, string|Expression $min, string|Expression $max);
new DistinctFrom(string|Expression $value1, string|Expression $value2);
new Equal(string|Expression $value1, string|Expression $value2);
new IsNull(string|Expression $value);
new GreaterThan(string|Expression $value1, string|Expression $value2);
new GreaterThanOrEqual(string|Expression $value1, string|Expression $value2);
new LessThan(string|Expression $value1, string|Expression $value2);
new LessThanOrEqual(string|Expression $value1, string|Expression $value2);
new NotDistinctFrom(string|Expression $value1, string|Expression $value2);
new NotEqual(string|Expression $value1, string|Expression $value2);
new NotIsNull(string|Expression $value);


new StrListContains(string|Expression $strList, string|Expression $str);

new CondAnd(string|Expression $value1, string|Expression $value2);
new CondNot(string|Expression $value);
new CondOr(string|Expression $value1, string|Expression $value2);
new CondXor(string|Expression $value1, string|Expression $value2);

// Examples in Aggregates::countFilter()

BlogVistis::where(new Equal('url', new Value('/exam\'ple1')))->get()

use Illuminate\Contracts\Database\Query\Expression;
use Tpetry\QueryExpressions\Function\Aggregate\{
    Avg, Count, CountFilter, Max, Min, Sum, SumFilter,
};
use Tpetry\QueryExpressions\Value\Value;

new Avg(string|Expression $value);
new Count(string|Expression $value, bool $distinct = false);
new CountFilter(string|Expression $filter);
new Max(string|Expression $value);
new Min(string|Expression $value);
new Sum(string|Expression $value);
new SumFilter(string|Expression $value, string|Expression $filter);

// SELECT COUNT(*) AS visits, AVG(duration) AS duration FROM blog_visits WHERE ...
BlogVisit::select([
    new Alias(new Count('*'), 'visits'),
    new Alias(new Avg('duration'), 'duration'),
])
->whereDay('created_at', now())
->get();

// SELECT
//   COUNT(*) FILTER (WHERE (released = 2021)) AS released_2021,
//   COUNT(*) FILTER (WHERE (released = 2022)) AS released_2022,
//   COUNT(*) FILTER (WHERE (genre = 'Drama')) AS genre_drama,
//   COUNT(*) FILTER (WHERE (genre = 'Comedy')) AS genre_comedy
// FROM movies
// WHERE streamingservice = 'netflix'
Movie::select([
    new Alias(new CountFilter(new Equal('released', new Value(2021))), 'released_2021'),
    new Alias(new CountFilter(new Equal('released', new Value(2022))), 'released_2022'),
    new Alias(new CountFilter(new Equal('genre', new Value('Drama'))), 'genre_drama'),
    new Alias(new CountFilter(new Equal('genre', new Value('Comedy'))), 'genre_comedy'),
])
    ->where('streamingservice', 'netflix')
    ->get();

use Tpetry\QueryExpressions\Function\Conditional\{
    Coalesce, Greatest, Least
};
use Tpetry\QueryExpressions\Language\Alias;

new Coalesce(array $expressions);
new Greatest(array $expressions);
new Least(array $expressions);

// SELECT GREATEST(published_at, updated_at, created_at) AS last_modification FROM blog_articles
BlogArticle::select([
    new Alias(new Greatest('published_at', 'updated_at', 'created_at'), 'last_modification')
])
->get();

use Tpetry\QueryExpressions\Function\Math\{
    Abs,
};

new Abs(string|Expression $expression);

use Tpetry\QueryExpressions\Function\String\{
    Concat, Lower, Upper, Uuid4
};

new Concat(array $expressions);
new Lower(string|Expression $expression);
new Upper(string|Expression $expression);
new Uuid4();

Schema::table('users', function (Blueprint $table): void {
    $table->uuid()->default(new Uuid4())->unique();
});

use Tpetry\QueryExpressions\Function\Time\Now;
use Tpetry\QueryExpressions\Function\Time\TimestampBin;

new Now();
new TimestampBin(string|Expression $expression, DateInterval $step, ?DateTimeInterface $origin = null);

BlogVisit::select([
    'url',
    new TimestampBin('created_at', DateInterval::createFromDateString('5 minutes')),
    new Count('*'),
])->groupBy(
    'url',
    new TimestampBin('created_at', DateInterval::createFromDateString('5 minutes'))
)->get();
// | url       | timestamp           | count |
// |-----------|---------------------|-------|
// | /example1 | 2023-05-16 09:50:00 | 2     |
// | /example1 | 2023-05-16 09:55:00 | 1     |
// | /example1 | 2023-05-16 09:50:00 | 1     |

Schema::table('users', function (Blueprint $table): void {
    $table->uuid()->default(new Uuid4())->unique();
});