PHPackages                             tpetry/laravel-query-expressions - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. tpetry/laravel-query-expressions

ActiveLibrary[Database & ORM](/categories/database)

tpetry/laravel-query-expressions
================================

Database-independent Query Expressions as a replacement to DB::raw calls

1.6.0(2mo ago)357436.5k↑20.2%29[1 issues](https://github.com/tpetry/laravel-query-expressions/issues)[1 PRs](https://github.com/tpetry/laravel-query-expressions/pulls)2MITPHPPHP ^8.1CI passing

Since Mar 16Pushed 2mo ago2 watchersCompare

[ Source](https://github.com/tpetry/laravel-query-expressions)[ Packagist](https://packagist.org/packages/tpetry/laravel-query-expressions)[ Docs](https://github.com/tpetry/laravel-query-expressions)[ RSS](/packages/tpetry-laravel-query-expressions/feed)WikiDiscussions main Synced 1mo ago

READMEChangelogDependencies (21)Versions (20)Used By (2)

Laravel Query Expressions to replace DB::raw() calls
====================================================

[](#laravel-query-expressions-to-replace-dbraw-calls)

[![Supported PHP Versions](https://camo.githubusercontent.com/074222e0a3638b5a49cfb83132a87bf74c33c88485955358673cd65d9b754036/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048502d382e312532422d626c75653f7374796c653d666c61742d737175617265)](https://camo.githubusercontent.com/074222e0a3638b5a49cfb83132a87bf74c33c88485955358673cd65d9b754036/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f5048502d382e312532422d626c75653f7374796c653d666c61742d737175617265)[![Supported Laravel Versions](https://camo.githubusercontent.com/0bd7e62aa9071e9825ea553a65ac3a80cae8af1d8a4d1d1018faf26da5993cf3/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f4c61726176656c2d31302532422d626c75653f7374796c653d666c61742d737175617265)](https://camo.githubusercontent.com/0bd7e62aa9071e9825ea553a65ac3a80cae8af1d8a4d1d1018faf26da5993cf3/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f4c61726176656c2d31302532422d626c75653f7374796c653d666c61742d737175617265)[![Latest Version on Packagist](https://camo.githubusercontent.com/d52703ceddfe8137c0970db8d9739835d641f4df1a5884367e2bc164d35670b2/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7470657472792f6c61726176656c2d71756572792d65787072657373696f6e732e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/tpetry/laravel-query-expressions)[![GitHub Tests Action Status](https://camo.githubusercontent.com/2db4481fb163fa087bc8888ee94d72545837cf7dbc522255a2ddb603684457f9/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7470657472792f6c61726176656c2d71756572792d65787072657373696f6e732f74657374732e796d6c3f6272616e63683d6d61696e266c6162656c3d7465737473267374796c653d666c61742d737175617265)](https://github.com/tpetry/laravel-query-expressions/actions/workflows/tests.yml?query=workflow%3Atests+branch%3Amain)[![GitHub Static Analysis Action Status](https://camo.githubusercontent.com/06411390d737593b665c8840eebd774a160a036ba87449b415690950ef4b6310/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7470657472792f6c61726176656c2d71756572792d65787072657373696f6e732f7374617469632d616e616c797369732e796d6c3f6272616e63683d6d61696e266c6162656c3d737461746963253230616e616c79736973267374796c653d666c61742d737175617265)](https://github.com/tpetry/laravel-query-expressions/actions/workflows/static-analysis.yml?query=workflow%3Atests+branch%3Amain)[![GitHub Code Style Action Status](https://camo.githubusercontent.com/b24201854577c7e34f2ae582e4f3b5c0e4da2139f7c6f17406784a494f8714d0/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f616374696f6e732f776f726b666c6f772f7374617475732f7470657472792f6c61726176656c2d71756572792d65787072657373696f6e732f636f64652d7374796c652e796d6c3f6272616e63683d6d61696e266c6162656c3d636f64652532307374796c65267374796c653d666c61742d737175617265)](https://github.com/tpetry/laravel-query-expressions/actions/workflows/code-style.yml?query=workflow%3Atests+branch%3Amain)

Laravel's database implementation provides a good way of working with multiple databases while abstracting away their inner workings. You don't have to consider minor syntax differences when using a query builder or how each database handles specific operations slightly differently.

However, when we want to use more database functionality than Laravel provides, we must fall back to raw SQL expressions and write database-specific code. The Query Expressions package builds on new features introduced in Laravel 10 to solve that problem. All provided implementations abstract some SQL functionality that is automatically transformed to the correct syntax with the same behaviour for your used database engine. And if your version is still supported by Laravel but is missing a feature, it is emulated by the implementations. So you can even do things that were not possible before.

You can make your queries database independent:

```
// 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']), 'value'));
```

And you can also create new powerful queries:

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

Installation
------------

[](#installation)

You can install the package via composer:

```
composer require tpetry/laravel-query-expressions
```

Usage
-----

[](#usage)

This package implements a lot of expressions you can use for selecting data, do better filtering or ordering of rows. Every expression can be used exactly as stated by the documentation, but you can also combine them as shared in the example before. Whenever an expression class needs a `string|Expression` parameter, you can pass a column name or another (deeply nested) expression object.

> **Note**A string passed for a `string|Expression` parameter is always used as a column name that will be automatically quoted.

> **Warning**The generated SQL statements of the examples are only for explanatory purposes. The real ones will be automatically tailored to your database using proper quoting and its specific syntax.

### Language

[](#language)

#### Values

[](#values)

As stated before, an expression is always a column name. But if you want to e.g. do an equality check, you may want to compare something to a specific value. That's where you should use the `Value` class. Its values will always be automatically escaped within the query.

```
use Tpetry\QueryExpressions\Value\Value;

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

> **Note**The `Value` class in isolation is not that usefull. But it will be used more in the next examples.

#### Alias

[](#alias)

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

> **Note**The `Alias` class in isolation is not that usefull because Eloquent can already do this. But it will be used more in the next examples.

#### Cast

[](#cast)

```
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

[](#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);
});
```

To use the case-when in a `Select` wrap it in an `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();
```

### Operators

[](#operators)

#### Arithmetic Operators

[](#arithmetic-operators)

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

#### Bitwise Operators

[](#bitwise-operators)

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

#### Comparison & Logical Operators

[](#comparison--logical-operators)

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

You can use these expressions directly with Laravel's `where()` method:

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

### Functions

[](#functions)

#### Aggregates

[](#aggregates)

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

#### Conditional

[](#conditional)

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

#### Math

[](#math)

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

new Abs(string|Expression $expression);
```

#### String

[](#string)

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

> **Warning**The `Uuid4` expression is not available for all database versions. With PostgreSQL you need at least v13 and with MariaDB at least v10.10.

#### Time

[](#time)

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

new ExtractDatePart(string|Expression $column, string $part);
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();
});
```

Changelog
---------

[](#changelog)

Please see [CHANGELOG](CHANGELOG.md) for more information on what has changed recently.

Credits
-------

[](#credits)

- [tpetry](https://github.com/tpetry)
- [All Contributors](../../contributors)

License
-------

[](#license)

The MIT License (MIT). Please see [License File](LICENSE.md) for more information.

###  Health Score

61

—

FairBetter than 99% of packages

Maintenance86

Actively maintained with recent releases

Popularity57

Moderate usage in the ecosystem

Community26

Small or concentrated contributor base

Maturity63

Established project with proven stability

 Bus Factor1

Top contributor holds 78.1% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~64 days

Recently: every ~182 days

Total

18

Last Release

66d ago

Major Versions

0.9.0 → 1.0.02024-01-30

### Community

Maintainers

![](https://www.gravatar.com/avatar/ccc334e20491a79e2d81037803a4c96fe45dc2b0f721c631ab23e390242383b0?d=identicon)[tpetry](/maintainers/tpetry)

---

Top Contributors

[![tpetry](https://avatars.githubusercontent.com/u/315686?v=4)](https://github.com/tpetry "tpetry (50 commits)")[![morloderex](https://avatars.githubusercontent.com/u/5677808?v=4)](https://github.com/morloderex "morloderex (2 commits)")[![MohannadNaj](https://avatars.githubusercontent.com/u/8743700?v=4)](https://github.com/MohannadNaj "MohannadNaj (2 commits)")[![Foysal50x](https://avatars.githubusercontent.com/u/24409515?v=4)](https://github.com/Foysal50x "Foysal50x (1 commits)")[![gdebrauwer](https://avatars.githubusercontent.com/u/22586858?v=4)](https://github.com/gdebrauwer "gdebrauwer (1 commits)")[![gisostallenberg](https://avatars.githubusercontent.com/u/11526270?v=4)](https://github.com/gisostallenberg "gisostallenberg (1 commits)")[![authanram](https://avatars.githubusercontent.com/u/1874088?v=4)](https://github.com/authanram "authanram (1 commits)")[![jaspertey](https://avatars.githubusercontent.com/u/1280844?v=4)](https://github.com/jaspertey "jaspertey (1 commits)")[![lorenzolosa](https://avatars.githubusercontent.com/u/11164571?v=4)](https://github.com/lorenzolosa "lorenzolosa (1 commits)")[![tontonsb](https://avatars.githubusercontent.com/u/16481303?v=4)](https://github.com/tontonsb "tontonsb (1 commits)")[![hungthai1401](https://avatars.githubusercontent.com/u/22017922?v=4)](https://github.com/hungthai1401 "hungthai1401 (1 commits)")[![coclav](https://avatars.githubusercontent.com/u/1837434?v=4)](https://github.com/coclav "coclav (1 commits)")[![elcapo](https://avatars.githubusercontent.com/u/1746692?v=4)](https://github.com/elcapo "elcapo (1 commits)")

---

Tags

laraveldatabasequeryexpression

###  Code Quality

TestsPest

Static AnalysisPHPStan

Code StyleLaravel Pint

Type Coverage Yes

### Embed Badge

![Health badge](/badges/tpetry-laravel-query-expressions/health.svg)

```
[![Health](https://phpackages.com/badges/tpetry-laravel-query-expressions/health.svg)](https://phpackages.com/packages/tpetry-laravel-query-expressions)
```

###  Alternatives

[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[tucker-eric/eloquentfilter

An Eloquent way to filter Eloquent Models

1.8k4.8M26](/packages/tucker-eric-eloquentfilter)[watson/validating

Eloquent model validating trait.

9723.3M47](/packages/watson-validating)[reedware/laravel-relation-joins

Adds the ability to join on a relationship by name.

2121.2M13](/packages/reedware-laravel-relation-joins)[clickbar/laravel-magellan

This package provides functionality for working with the postgis extension in Laravel.

423715.4k1](/packages/clickbar-laravel-magellan)[cybercog/laravel-clickhouse

ClickHouse migrations for Laravel

163166.8k](/packages/cybercog-laravel-clickhouse)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)