Download the PHP package angel-source-labs/laravel-expressions without Composer

Expressions

Enhanced Database Query Expressions for Laravel

What is an Expression?

An Expression is a string of raw sql that can be used in Laravel Query Builder statements. The Laravel documentation presents the concept of Raw Expressions as raw SQL strings that can be created via the DB::raw facade or by using any of the raw methods:

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

Laravel represents these expressions as an Expression object that can be created using the DB::raw method.

This package enhances Expressions with the following features

• Add PDO-style bindings to Expressions
• Create Expression subclasses that are semantically meaningful
• Assign Expressions to Eloquent attributes
• Make any class into an Expression by implementing the IsExpression interface
• ExpressionGrammar: An Expression can produce the appropriate different grammar for each database by using the ExpressionGrammar helper class

Laravel versions

The following Laravel versions are supported:

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

install package

Install the package with composer

package conflicts: artisan expressions:doctor

This package injects new database connection, grammar, and query builder classes, so it potentially conflicts with other packages that inject or override database connections, grammar, or query builder classes.

To test that the installation is working and is not experiencing conflicts from other packages, this package includes an artisan expressions:doctor command that will run tests to verify that the database connections are resolving properly and expressions are building properly.

To run the doctor, type php artisan expressions:doctor at the command line at the base of your Laravel project.

Possible Package Conflicts

The following are examples of packages that possibly conflict with this package.

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

How to Create Expressions

Expression (without bindings)

Create an expression by creating a new instance of AngelSourceLabs\LaravelExpressions\Database\Query\Expression\Expression.

Expression (with bindings)

Create an expression with bindings by creating a new instance of AngelSourceLabs\LaravelExpressions\Database\Query\Expression\Expression. The first parameter is the raw sql expression using ? placeholders for the bindings. The second parameter is an array of binding values.

This produces the SQL 'select * fromauditswhereip= inet_aton(?)' with a PDO binding of [1 => "192.168.0.1"]

Make Expressions Semantically Meaningful

You can create reusable expressions classes with semantic meaning.

Eloquent - Assign Expressions to Model Attributes

Expressions can be stored in Eloquent model attributes and will be used in insert and update statements.

results in the following insert or update statement depending on whether the record is new or already existing:

Make Existing Classes into Expressions: IsExpression interface and ProvidesExpression trait

When building domain classes, a class may already extend from another class and may not always be able to extend from Expression.

You can turn any class into an expression by implementing the IsExpression interface.

You can also use the trait ProvidesExpression to add the default implementation to your class.

In fact the Expression class is implemented using the IsExpression interface and ProvidesExpression trait.

ExpressionGrammar: Provide expressions with grammar differences by database

Sometimes SQL expressions need to provide different grammar for different databases and for different versions of databases.

This package provides an ExpressionGrammar class that will produce the appropriate expression for the database and version in use.

For example, when working with ST_GeomFromText() between MySQL 8.0 vs MySQL 5.7 and Postgres, the order of latitude and longitude is different, and when switching between databases you might want your code base to work the same without changes. MySQL 8.0 provides an option for ST_GeomFromText() to change the axis order. So while the grammar for Postgres will look like ST_GeomFromText(?, ?), the grammar for MySql 8.0 will look like ST_GeomFromText(?, ?, 'axis-order=long-lat').

Creating an Expression with an ExpressionGrammar to support these three different grammars would look like this:

This will resolve to the following expressions for the specified databases and versions:

Available Methods

The ExpressionGrammar class provides a fluent interface for adding grammar expressions and has methods for each built-in Laravel driver as well as a generic grammar method that allows specifying a driver string for other databases.

ExpressionGrammar::make()

Creates a new Grammar instance and provides a fluent interface for adding grammar expressions.

ExpressionGrammar->mySql($string, $version (optional))

Add an expression for MySQL grammar.

ExpressionGrammar->postgres($string, $version (optional))

Add an expression for Postgres grammar.

ExpressionGrammar->sqLite($string, $version (optional))

Add an expression for SQLite grammar.

ExpressionGrammar->sqlServer($string, $version (optional))

Add an expression for SqlServer grammar.

ExpressionGrammar->grammar($driver, $string, $version (optional))

Add an expression for grammar for other database drivers. $driver should match the driver string used by the Laravel query builder driver. For example $grammar->postgres("ST_GeomFromText(?, ?)") is equivalent to $grammar->grammar("pgsql", "ST_GeomFromText(?, ?)").

The $version parameter is optional. When not specified, the grammar applies as the default. When specified, the grammar applies to the specified version of the database or greater.

ExpressionGrammar will throw a GrammarNotDefinedForDatabaseException if the Query Builder attempts to resolve an Expression for a Grammar that has not been defined for that database driver.

Example: Point with ExpressionGrammar

Revisiting the Point example from above using the ExpressionGrammar class to create appropriate grammar for MySql 5.7, MySql 8.0, and Postgres:

which will evaluate as an expression and result in the following SQL

Supported Query Builder Statements

select

Example:

result:

selectRaw

Example 1:

result:

Example 2:

result:

whereRaw / orWhereRaw

Example 1:

result:

Example 2:

result:

havingRaw / orHavingRaw

Example 1:

result:

Example 2:

result:

orderByRaw

Example 1:

result:

Example 2:

result:

groupByRaw

Example 1:

result:

Example 2:

result:

where / orWhere

Example:

result:

Supported Cases

Basic Where Clauses

Basic Where Clauses

Or Where Clauses

Or Where Clauses

Additional Where Clauses

Additional Where Clauses

Currently Unimplemented / Untested Cases

These cases are currently not supported (or at least not tested) but likely could be added.

Array of Conditions (currently unimplemented / untested)

Where Clauses

Logical Grouping (currently unimplemented / untested)

Logical Grouping

Where Exists Clauses (currently unimplemented / untested)

Where Exists Clauses

Subquery Where Clauses (currently unimplemented / untested)

Subquery Where Clauses Case 1: Compare the results of subquery to a value:

Case 2: Compare a column to the results of a subquery

JSON Where Clauses (currently unimplemented / untested)

JSON Where Clauses

Run tests

The following tests are run with this pacakge's ExpressionsServiceProvider loaded:

• Unit tests from vendor/laravel/framework/tests/Database
• Integration tests from vendor/laravel/framework/tests/Integration/Database
• Unit tests from tests/Unit (this package)

License

Excel Seeder for Laravel is open-sourced software licensed under the MIT license.