tpetry/laravel-query-expressions

数据库无关的查询表达式，作为DB::raw调用的替代品

维护者

详细信息

github.com/tpetry/laravel-query-expressions

主页

源代码

问题

安装次数: 62,462

依赖者: 2

建议者: 1

安全: 0

星标: 308

关注者: 4

分支: 19

开放问题: 1

1.4.0 2024-08-29 09:51 UTC

Requires

Requires (Dev)

MIT 071b55b7e9cdeb4bb1eeb9c1d98734b90427fa9e

  • tpetry <github.woop@tpetry.me>

databasequerylaravelexpression

This package is auto-updated.

Last update: 2024-08-29 10:15:50 UTC

README

Supported PHP Versions Supported Laravel Versions Latest Version on Packagist GitHub Tests Action Status GitHub Static Analysis Action Status GitHub Code Style Action Status

Laravel的数据库实现提供了一种在抽象内部工作方式的同时处理多个数据库的好方法。在使用查询构建器时，您不需要考虑微小的语法差异，或者每个数据库如何以略微不同的方式处理特定操作。

但是，当我们要使用比Laravel提供的更多数据库功能时，我们必须退回到原始SQL表达式并编写特定于数据库的代码。查询表达式包建立在Laravel 10中引入的新功能之上，以解决这个问题。所有提供的实现都抽象了一些SQL功能，这些功能会自动转换为正确的语法，并且对于您使用的数据库引擎具有相同的行为。如果您的版本仍然由Laravel支持但缺少功能，它将通过实现进行模拟。因此，您甚至可以做一些以前不可能的事情。

您可以使您的查询与数据库无关

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

安装

您可以通过composer安装此包

composer require tpetry/laravel-query-expressions

使用方法

此包实现了很多表达式，您可以使用它们选择数据，对行进行更好的过滤或排序。每个表达式都可以像文档中所述那样使用，但您也可以像在之前的示例中那样组合它们。每当一个表达式类需要一个string|Expression参数时，您可以传递一个列名或另一个（深度嵌套的）表达式对象。

注意 用于string|Expression参数的字符串始终用作将被自动引号的列名。

警告 示例中生成的SQL语句仅用于说明目的。实际的语句将根据您的数据库使用适当的引号和特定语法自动调整。

语言

值

如前所述，表达式始终是列名。但如果您想例如进行相等检查，您可能想将某些内容与特定值进行比较。这就是您应该使用Value类的地方。它的值将在查询中自动转义。

use Tpetry\QueryExpressions\Value\Value;

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

注意 独立的Value类并不那么有用。但在接下来的示例中将会更多使用。

别名

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

注意 独立的Alias类并不那么有用，因为Eloquent已经可以这样做。但在接下来的示例中将会更多使用。

类型转换

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

Case-When

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

要在Select中使用case-when，请将其包裹在Alias中

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\Operator\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\Operator\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()

您可以直接使用这些表达式与Laravel的where()方法

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

警告 Uuid4表达式不是所有数据库版本都可用。对于PostgreSQL，至少需要v13，对于MariaDB至少需要v10.10。

时间

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

变更日志

请参阅变更日志了解最近更改的更多信息。

致谢

许可证

MIT许可证（MIT）。请参阅许可证文件了解更多信息。