README

增强Laravel的数据库查询表达式

什么是表达式？

表达式是一串原始SQL，可以用在Laravel查询构建器语句中。Laravel文档将原始表达式介绍为可以通过DB::raw门面或使用原始方法创建的原始SQL字符串

• selectRaw
• whereRaw / orWhereRaw
• havingRaw / orHavingRaw
• orderByRaw
• groupByRaw

Laravel将这些表达式表示为Expression对象，可以使用DB::raw方法创建。

此包通过以下功能增强了表达式：

• 向表达式添加PDO风格的绑定
• 创建具有语义意义的Expression子类
• 将表达式分配给Eloquent属性
• 通过实现IsExpression接口将任何类转换为Expression
• ExpressionGrammar：表达式可以通过使用ExpressionGrammar辅助类为每个数据库生成适当的语法

Laravel版本

以下Laravel版本受到支持：

• Laravel 6.x
• Laravel 7.x
• Laravel 8.x
• Laravel 9.x
• Laravel 10.x

安装包

使用composer安装此包

composer require angel-source-labs/laravel-expressions

包冲突：artisan expressions:doctor

此包注入了新的数据库连接、语法和查询构建器类，因此可能与注入或覆盖数据库连接、语法或查询构建器类的其他包发生冲突。

为了测试安装是否正常工作，并且没有与其他包发生冲突，此包包含一个artisan expressions:doctor命令，该命令将运行测试以验证数据库连接是否正确解析并且表达式是否正确构建。

要运行doctor，请在Laravel项目的根目录下通过命令行输入php artisan expressions:doctor。

可能的包冲突

以下是可能与此包冲突的包的示例。

• grimzy/laravel-mysql-spatial
• fico7489/laravel-pivot
• chelout/laravel-relationship-events
• spatie/laravel-query-builder
• dwightwatson/rememberable
• kalnoy/nestedset
• genealabs/laravel-model-caching

如何创建表达式

表达式（不带绑定）

通过创建AngelSourceLabs\LaravelExpressions\Database\Query\Expression\Expression的新实例来创建表达式。

    public function testSelectRawUsingExpression()
    {
        $expression = new Expression("price as price_before_tax");
        $sql = DB::table('orders')->selectRaw($expression)->toSql();
        $this->assertEquals('select price as price_before_tax from `orders`', $sql);
    }

表达式（带绑定）

通过创建AngelSourceLabs\LaravelExpressions\Database\Query\Expression\Expression的新实例并使用?占位符来创建带有绑定的表达式。第一个参数是使用?占位符的原始SQL表达式。第二个参数是绑定值的数组。

        $expression = new Expression("inet_aton(?)", ["192.168.0.1"]);
        DB::table('audits')->where('ip', $expression)->get();

这将生成SQL 'select * from auditswhereip = inet_aton(?)'，带有PDO绑定[1 => "192.168.0.1"]。

使表达式具有语义意义

您可以使用语义意义创建可重用的表达式类。

public class InetAtoN extends Expression
{
    public function __construct($address)
    {
        parent::__construct("inet_aton(?)", $address);
    }
}

DB::table('audits')->where('ip', new InetAtoN("192.168.0.1"))->get();

Eloquent - 将表达式分配给模型属性

表达式可以存储在Eloquent模型属性中，并在插入和更新语句中使用。

public class Point extends Expression
{
    public function __construct($lat, $lng)
    {
        parent::__construct("ST_GeomFromText(?, ?)", [$lng, $lat]);
    }
}

$model->point = new Point(44.9561062,-93.1041534);
$model->save();

根据记录是新记录还是已存在的记录，将生成以下插入或更新语句。

-- example insert statement result
insert into "test_models" ("point") values (ST_GeomFromText(?, ?)) returning "id";

-- example update statement result
update "test_models" set "point" = ST_GeomFromText(?, ?) where "id" = ?;

将现有类转换为表达式：实现IsExpression接口和ProvidesExpression特性

在构建领域类时，一个类可能已经继承自另一个类，并且可能无法始终从Expression扩展。

您可以通过实现IsExpression接口将任何类转换为表达式。

您还可以使用ProvidesExpression特性向您的类添加默认实现。

class ClassIsExpression implements IsExpression
{
    use ProvidesExpression;
}

function testSelectRawUsingExpression()
{
    $expression = new ClassIsExpression("price as price_before_tax");
    $sql = DB::table('orders')->selectRaw($expression)->toSql();
    $this->assertEquals('select price as price_before_tax from `orders`', $sql);
}

实际上，Expression类是通过实现IsExpression接口和ProvidesExpression特性来实现的。

use Illuminate\Database\Query\Expression as BaseExpression;

class Expression extends BaseExpression implements IsExpression
{
    use ProvidesExpression;
}

ExpressionGrammar：根据数据库提供不同的语法

有时SQL表达式需要为不同的数据库和不同版本的数据库提供不同的语法。

此包提供了一个将生成适当表达式用于使用数据库和版本的ExpressionGrammar类。

例如，当在MySQL 8.0与MySQL 5.7和Postgres之间使用ST_GeomFromText()时，纬度和经度的顺序不同，当在数据库之间切换时，您可能希望代码库无需更改就能以相同的方式工作。MySQL 8.0为ST_GeomFromText()提供了一个选项来更改轴顺序。因此，虽然Postgres的语法看起来像ST_GeomFromText(?, ?)，但MySql 8.0的语法看起来像ST_GeomFromText(?, ?, 'axis-order=long-lat')。

使用ExpressionGrammar创建一个表达式以支持这三种不同的语法，如下所示

$grammar = ExpressionGrammar::make()
        ->mySql("ST_GeomFromText(?, ?)")
        ->mySql("ST_GeomFromText(?, ?, 'axis-order=long-lat')", "8.0")
        ->postgres("ST_GeomFromText(?, ?)");
$expression = new Expression($grammar, [$lon, $lat]);

这将解析为以下针对指定数据库和版本的语句

可用方法

ExpressionGrammar类提供了一个流畅的接口用于添加语法表达式，并为每个内置Laravel驱动程序提供了方法，以及一个允许指定其他数据库驱动程序字符串的通用grammar方法。

#ExpressionGrammar::make()

创建一个新的Grammar实例，并提供添加语法表达式的流畅接口。

#ExpressionGrammar->mySql($string, $version (可选))

添加MySQL语法的表达式。

#ExpressionGrammar->postgres($string, $version (可选))

添加Postgres语法的表达式。

#ExpressionGrammar->sqLite($string, $version (可选))

添加SQLite语法的表达式。

#ExpressionGrammar->sqlServer($string, $version (可选))

添加SqlServer语法的表达式。

#ExpressionGrammar->grammar($driver, $string, $version (可选))

为其他数据库驱动程序添加语法表达式。参数$driver应与Laravel查询构建器驱动程序使用的驱动程序字符串相匹配。例如，$grammar->postgres("ST_GeomFromText(?, ?)")与$grammar->grammar("pgsql", "ST_GeomFromText(?, ?)")等效。

参数$version是可选的。未指定时，语法作为默认语法应用。指定时，语法应用于数据库的指定版本或更高版本。

如果查询构建器尝试解析尚未为该数据库驱动程序定义语法的语法的表达式，则ExpressionGrammar将抛出GrammarNotDefinedForDatabaseException。

示例：使用ExpressionGrammar的Point

回顾上面使用ExpressionGrammar类为MySql 5.7，MySql 8.0和Postgres创建适当的语法的Point示例

public class Point extends Expression
{
    public function __construct($lat, $lng)
    {
        parent::__construct(ExpressionGrammar::make()
            ->mySql("ST_GeomFromText(?, ?)")
            ->mySql("ST_GeomFromText(?, ?, 'axis-order=long-lat')", "8.0")
            ->postgres("ST_GeomFromText(?, ?)"), 
        [$lng, $lat]);
    }
}

$model->point = new Point(44.9561062,-93.1041534);
$model->save();

这将评估为表达式，并产生以下SQL

-- example insert statement result
insert into "test_models" ("point") values (ST_GeomFromText(?, ?)) returning "id";  # MySQL 5.7, postgis
insert into "test_models" ("point") values (ST_GeomFromText(?, ?, 'axis-order=long-lat')) returning "id";  # MySQL 8.0 and greater

-- example update statement result
update "test_models" set "point" = ST_GeomFromText(?, ?) where "id" = ?; # MySQL 5.7, postgis
update "test_models" set "point" = ST_GeomFromText(?, ?, 'axis-order=long-lat') where "id" = ?; # MySQL 8.0 and greater

支持的查询构建器语句

选择

示例

    $expression = new Expression("price * ? as price_with_tax", [1.0825]);
    DB::table('orders')->select($expression)->get();

结果

    select price * ? as price_with_tax from `orders`; # bindings = [1 => 1.0825]

selectRaw

示例1

    $expression = new Expression("price * ? as price_with_tax", [1.0825]);
    DB::table('orders')->selectRaw($expression)->get();

结果

    select price * ? as price_with_tax from `orders`; # bindings = [1 => 1.0825]

示例2

    $expression = new Expression("price * ? as price_with_tax, price * ? as profit", [1.0825]);
    DB::table('orders')->selectRaw($expression, [.20])->get();

结果

    select price * ? as price_with_tax, price * ? as profit from `orders`; # bindings = [1 => 1.0825, 2 => 0.20]

whereRaw / orWhereRaw

示例1

    $expression = new Expression('price > IF(state = "TX", ?, 100)', [200]);
    DB::table('orders')->whereRaw($expression)->get();

结果

    select * from `orders` where price > IF(state = "TX", ?, 100); # bindings = [1 => 200]

示例2

    $expression = new Expression('price > IF(state = "TX", ?, ?)', [200]);
    DB::table('orders')->whereRaw($expression, [100])->get();

结果

    select * from `orders` where price > IF(state = "TX", ?, ?); # bindings = [1 => 200, 2 => 100]

havingRaw / orHavingRaw

示例1

    $expression = new Expression('SUM(price) > ?', [2500]);
    $sql = DB::table('orders')
        ->select('department', DB::raw('SUM(price) as total_sales'))
        ->groupBy('department')
        ->havingRaw($expression)
        ->get();

结果

    select `department`, SUM(price) as total_sales from `orders` group by `department` having SUM(price) > ?; # bindings = [1 => 2500]

示例2

        $expression = new Expression('SUM(price) > ? and AVG(price) > ?', [2500]);
        $sql = DB::table('orders')
            ->select('department', DB::raw('SUM(price) as total_sales'))
            ->groupBy('department')
            ->havingRaw($expression, [100])
            ->get();

结果

    select `department`, SUM(price) as total_sales from `orders` group by `department` having SUM(price) > ? and AVG(price) > ?; # bindings = [1 => 2500, 2 => 100]

orderByRaw

示例1

    $ids = [12,23,34,45];
    $expression = new Expression('field(id, ?, ?, ?, ?)', $ids);
    DB::table('orders')
        ->whereIn('id', $ids)
        ->orderByRaw($expression)
        ->get();

结果

    select * from `orders` where `id` in (?, ?, ?, ?) order by field(id, ?, ?, ?, ?); # bindings = [1 => 12, 2 => 23, 3 => 34, 4 => 45, 5 => 12, 6 => 23, 7 => 34, 8 => 45]

示例2

    $ids = [12,23,34,45];
    $expression = new Expression('field(id, ?, ?, ?, ?)', [12,23]);
    DB::table('orders')
        ->whereIn('id', $ids)
        ->orderByRaw($expression,[34,45])
        ->get();

结果

    select * from `orders` where `id` in (?, ?, ?, ?) order by field(id, ?, ?, ?, ?); # bindings = [1 => 12, 2 => 23, 3 => 34, 4 => 45, 5 => 12, 6 => 23, 7 => 34, 8 => 45]

groupByRaw

示例1

    $expression = new Expression('price > ?', [100]);
    DB::table('orders')
        ->select('department', 'price')
        ->groupByRaw($expression)
        ->get();

结果

    select `department`, `price` from `orders` group by price > ?; # bindings = [1 => 100]

示例2

        $expression = new Expression('price > ?, department > ?', [100]);
        DB::table('orders')
            ->select('department', 'price')
            ->groupByRaw($expression, [1560])
            ->get();

结果

    select `department`, `price` from `orders` group by price > ?, department > ?; # bindings = [1 => 100, 2=> 1560]

where / orWhere

示例

    $expression = new Expression("inet_aton(?)", ["192.168.0.1"]);
    DB::table('audits')->where('ip', $expression)->get();

结果

    select * from `audits` where `ip` = inet_aton(?); # bindings = [1 => "192.168.0.1"]

支持的情况

基本where子句

基本where子句

$users = DB::table('users')
                ->where('votes', '=', $expression)
                ->where('age', '>', $expression)
                ->get();

$users = DB::table('users')->where('votes', $expression)->get();

Or where子句

Or where子句

其他where子句

其他where子句

当前未实现/未测试的情况

这些情况目前不受支持（或至少未测试），但可能可以添加。

条件数组（当前未实现/未测试）

Where子句

$users = DB::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

逻辑分组（目前未实现/未测试）

逻辑分组

$users = DB::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

Where Exists子句（目前未实现/未测试）

Where Exists子句

$users = DB::table('users')
           ->whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('orders')
                     ->whereColumn('orders.user_id', 'users.id');
           })
           ->get();

子查询Where子句（目前未实现/未测试）

子查询Where子句 情况1：比较子查询结果与一个值

use App\Models\User;

$users = User::where(function ($query) {
    $query->select('type')
        ->from('membership')
        ->whereColumn('membership.user_id', 'users.id')
        ->orderByDesc('membership.start_date')
        ->limit(1);
}, 'Pro')->get();

情况2：将列与子查询结果比较

use App\Models\Income;

$incomes = Income::where('amount', '<', function ($query) {
    $query->selectRaw('avg(i.amount)')->from('incomes as i');
})->get();

JSON Where子句（目前未实现/未测试）

JSON Where子句

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

运行测试

以下测试是在加载此包的ExpressionsServiceProvider的情况下运行的

• 来自vendor/laravel/framework/tests/Database的单元测试
• 来自vendor/laravel/framework/tests/Integration/Database的集成测试
• 来自tests/Unit（此包）的单元测试

composer test

许可证

Excel Seeder for Laravel是开源软件，许可协议为MIT许可。