PHPackages                             everon/criteria-builder - 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. everon/criteria-builder

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

everon/criteria-builder
=======================

Everon Criteria Builder Component

3.0.0(4y ago)73.3M↓10.3%2[1 PRs](https://github.com/oliwierptak/everon-criteria-builder/pulls)1MITPHPPHP ^8CI passing

Since Dec 14Pushed 3mo ago1 watchersCompare

[ Source](https://github.com/oliwierptak/everon-criteria-builder)[ Packagist](https://packagist.org/packages/everon/criteria-builder)[ RSS](/packages/everon-criteria-builder/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (4)Dependencies (5)Versions (18)Used By (1)

Everon Criteria Builder
=======================

[](#everon-criteria-builder)

Library to generate complete `SQL WHERE` statements, with simple, fluid and intuitive interface.

### Versions

[](#versions)

- Use v1.x and v2.x with PHP 7.2+
- Use v3.x with PHP 8+

Features
--------

[](#features)

- It's not a DQL
- SQL for what's important, fluid interface for boring stuff
- Automatic PDO parameter name escaping and uniqueness, custom parameters
- Fluid interface
- Easily to create multiple conditions
- Almost 20 ready to use Operators
- Easy to extend with Custom Operators
- Intuitive Interface: clear, small and simple API
- Clean code

No boring Sql
-------------

[](#no-boring-sql)

### Focus on what's important

[](#focus-on-whats-important)

You can attach your own `SQL` via `CriteriaBuilder->sql($sql)` and have easy and flexible way of generating fast *sql queries* without dealing with boring string concatenations, code duplication and vast amount of if/else, switch statements or constants required to handle logic related to *LIMIT*, *OFFSET* or *SORT* statements. All those boring parts were eliminated with `CriteriaBuilderInterface`.

### Hammer won't do when you need a screwdriver

[](#hammer-wont-do-when-you-need-a-screwdriver)

Putting boring stuff aside you have full control on how the `SQL` is constructed, which is helpful for highly complex, complicated or very specific queries where using *DQL* makes things actually harder then easier. *DQL* is great for everyday use, however sometimes you need to express yourself in very specific way, and *raw* `SQL` is the best way to get you there.

### Translate request into something database can understand

[](#translate-request-into-something-database-can-understand)

Easy to translate request parameters into something database can understand with `Operators`, `where` statements and methods like `setLimit`, `setOffset`, or `setOrderBy`. Useful for pagination or filtering, for example.

Clear separation between `SQL`, `SQL PARAMETERS`, and applying concepts like *aggregation*, *sort*, or *limit*. Now you can focus only on what's important, the `SQL` part.

Very easy to use with `PDO` thanks to `SqlPartInterface`

```
$sth = $dbh->prepare($SqlPart->getSql());
$sth->execute($SqlPart->getParameters());
```

Examples
--------

[](#examples)

### Simple Query

[](#simple-query)

```
$CriteriaBuilder
    ->where('id', '=', 123)
```

Will be converted into:

```
WHERE (id = :id_843451772)
```

With parameters:

```
array(8) [
  'id_843451772' => integer 123
]
```

### Where, orWhere, andWhere

[](#where-orwhere-andwhere)

Each `where` statement creates new Container with Criteria object. A Criteria object contains set of Criterium objects. A Criterium is a condition.

You can append Criterium by using `andWhere` and `orWhere` methods.

Every time you use `where` statement a new Criteria will be created, ready for new set of conditions.

```
$CriteriaBuilder
    ->where('id', 'IN', [1,2,3])
        ->orWhere('id', 'NOT IN', [4,5,6])
        ->andWhere('name', '=', 'foo');
    ->where('modified', 'IS', null)
        ->andWhere('name', '!=', null)
        ->orWhere('id', '=', 55);
```

Will be converted into:

```
WHERE (
    id IN (:id_843451778,:id_897328169,:id_1377365551)
    OR id NOT IN (:id_1260952006,:id_519145813,:id_1367241593)
    AND name = :name_1178871152
)
AND (
    modified IS NULL
    AND name IS NOT NULL
    OR id = :id_895877163
)
```

With parameters:

```
array(8) [
  'name_1178871152' => string (3) "foo"
  'id_1260952006' => integer 4
  'id_519145813' => integer 5
  'id_1367241593' => integer 6
  'id_843451778' => integer 1
  'id_897328169' => integer 2
  'id_1377365551' => integer 3
  'id_895877163' => integer 55
```

To connect Criteria with `OR` operator use `glueByOr` method.

```
$CriteriaBuilder
    ->where('id', 'IN', [1,2,3])
        ->orWhere('id', 'NOT IN', [4,5,6])
        ->andWhere('name', '=', 'foo');
    ->glueByOr()
    ->where('modified', 'IS', null)
        ->andWhere('name', '!=', null)
        ->orWhere('id', '=', 55);
```

Will be converted into:

```
WHERE (
    id IN (:id_843451778,:id_897328169,:id_1377365551)
    OR id NOT IN (:id_1260952006,:id_519145813,:id_1367241593)
    AND name = :name_1178871152
)
OR (
    modified IS NULL
    AND name IS NOT NULL
    OR id = :id_895877163
)
```

### RAW SQL

[](#raw-sql)

RAW SQL is easy to implement with `whereRaw` methods.

```
$CriteriaBuilder
    ->whereRaw('foo + bar')
    ->andWhereRaw('1=1')
    ->orWhereRaw('foo::bar()');
```

Will be converted into:

```
WHERE (foo + bar AND 1=1 OR foo::bar())
```

### Group By

[](#group-by)

Group By is easily usable with `setGroupBy` method

```
$CriteriaBuilder
    ->where('name', '!=', 'foo')
        ->andWhere('id', '=', 123)
    ->setGroupBy('name,id');
```

Will be converted into:

```
WHERE (name != :name_1178871154 AND id = :id_897328160)
GROUP BY name,id
```

With parameters:

```
array(8) [
  'name_1178871154' => string (3) "foo"
  'id_897328160' => integer 123
]
```

### Limit and Offset

[](#limit-and-offset)

Pretty straightforward with `setLimit` and `setOffset` methods.

```
$CriteriaBuilder
    ->whereRaw('foo + bar')
        ->andWhereRaw('1=1')
        ->orWhereRaw('foo::bar()');
    ->setLimit(10)
    ->setOffset(5);
```

Will be converted into:

```
WHERE (foo + bar AND 1=1 OR foo::bar())
LIMIT 10 OFFSET 5
```

### Order By

[](#order-by)

Order By is implemented using `ASC` and `DESC` keywords, in an associative array with `setOrderBy` method.

```
$CriteriaBuilder
    ->whereRaw('foo + bar')
        ->andWhereRaw('1=1')
        ->orWhereRaw('foo::bar()')
    ->setOrderBy([
        'name' => 'DESC',
        'id' => 'ASC'
    ]);
```

Will be converted into:

```
WHERE (foo + bar AND 1=1 OR foo::bar())
ORDER BY name DESC,id ASC
```

### Custom Gluing

[](#custom-gluing)

Manual Criteria handling is also possible by using the `glue` methods.

```
$CriteriaBuilder
        ->where('id', 'IN', [1,2,3])
        ->orWhere('id', 'NOT IN', [4,5,6])
    ->glueByOr()
        ->where('name', '!=', 'foo')
        ->andWhere('email', '!=', 'foo@bar')
    ->glueByAnd()
        ->where('bar', '=', 'bar')
        ->andWhere('name', '=', 'Doe');

$CriteriaBuilder->setLimit(10);
$CriteriaBuilder->setOffset(5);
$CriteriaBuilder->setGroupBy('name,id');
$CriteriaBuilder->setOrderBy(['name' => 'DESC', 'id' => 'ASC']);
```

Will be converted into:

```
(id IN (:id_1263450107,:id_1088910886,:id_404821955) OR id NOT IN (:id_470739703,:id_562547487,:id_230395754))
OR (name != :name_1409254675 AND email != :name_190021050)
AND (bar = :bar_1337676982 AND name = :name_391340793)
GROUP BY name,id
ORDER BY name DESC,id ASC
LIMIT 10 OFFSET 5
```

With parameters:

```
array(10) [
    'id_470739703' => integer 4
    'id_562547487' => integer 5
    'id_230395754' => integer 6
    'id_1263450107' => integer 1
    'id_1088910886' => integer 2
    'id_404821955' => integer 3
    'name_190021050' => string (3) "foo@bar"
    'name_1409254675' => string (3) "foo"
    'name_391340793' => string (3) "Doe"
    'bar_1337676982' => string (3) "bar"
]
```

### Operators

[](#operators)

There are almost 20 operators ready for use like Equal, NotIn, Between or Is. [Check them all here](https://github.com/oliwierptak/everon-criteria-builder/tree/development/src/Operator).

#### Equal

[](#equal)

```
$CriteriaBuilder->where('foo', '=', 'bar');
```

Will output:

```
WHERE (foo = :foo_1337676681)
```

#### NotIn

[](#notin)

```
$CriteriaBuilder->where('foo', 'NOT IN', ['bar', 'buzz']);
```

Will output:

```
WHERE (foo NOT IN [:foo_1337676681, :foo_1337776681)
```

#### Between

[](#between)

There must be exactly 2 parameters provided or an exception will be thrown.

```
$CriteriaBuilder->where('foo', 'BETWEEN', ['bar', 'buzz']);
```

Will output:

```
WHERE (foo BETWEEN :foo_1337676681 AND :foo_1337776681)
```

There are many more. [See here for more examples](https://github.com/oliwierptak/everon-criteria-builder/tree/development/src/Operator).

### Custom Operators

[](#custom-operators)

You can register your own Operators with:

```
/**
 * @param $sql_type
 * @param $operator_class_name
 *
 * @return void
 */
public static function registerOperator($sql_type, $operator_class_name);
```

For example:

```
class OperatorCustomTypeStub extends AbstractOperator
{
    const TYPE_NAME = 'CustomType';
    const TYPE_AS_SQL = '';
}

Builder::registerOperator(OperatorCustomTypeStub::TYPE_AS_SQL, 'Some\Namespace\OperatorCustomTypeStub');
```

You can use your own operator with `raw` methods.

```
$CriteriaBuilder->whereRaw('bar', null, OperatorCustomTypeStub::TYPE_AS_SQL);
$CriteriaBuilder->andWhereRaw('foo', ['foo' => 'bar'], OperatorCustomTypeStub::TYPE_AS_SQL);
$CriteriaBuilder->orWhereRaw('foo', 'bar', OperatorCustomTypeStub::TYPE_AS_SQL);
```

Will output:

```
WHERE (bar  NULL AND foo  :foo_1337676981
    OR foo  :foo_2137676760 )
```

See  for more examples

### How to use

[](#how-to-use)

Dependency Injection is done with [Everon Factory](https://github.com/oliwierptak/everon-factory).

Initialize with `CriteriaBuilderFactoryWorker->buildCriteriaBuilder()`.

```
use Everon\Component\CriteriaBuilder\CriteriaBuilderFactoryWorkerInterface;
use Everon\Component\Factory\Dependency\Container;
use Everon\Component\Factory\Factory;

include('vendor/autoload.php');

$Container = new Container();
$Factory = new Factory($Container);
$Factory->registerWorkerCallback('CriteriaBuilderFactoryWorker', function() use ($Factory) {
    return $Factory->buildWorker(CriteriaBuilderFactoryWorker::class);
});

$CriteriaBuilderFactoryWorker = $Factory->getWorkerByName('CriteriaBuilderFactoryWorker');
$CriteriaBuilder = $CriteriaBuilderFactoryWorker->buildCriteriaBuilder();
```

Setup your conditions.

```
$CriteriaBuilder
        ->where('sku', 'LIKE', '13%')
        ->orWhere('id', 'IN', [1, 2, 3])
    ->glueByOr()
        ->where('created_at', '>', '2015-12-03 12:27:22');
```

Append criteria string to already existing sql.

```
$sql = 'SELECT * FROM ';
$sql = $sql . (string) $CriteriaBuilder;
$sth = $dbh->prepare($sql);
```

Fetch sample data. After you attached `SQL` to the `CriteriaBuilder`, it's even easier to retrieve *sql query* and its *parameters*, with `SqlPartInterface` and methods like `getSql` and `getParameters`.

```
$dbh = new \PDO('mysql:host=127.0.0.1;dbname=DATABASE', 'root', '');
$SqlPart = $CriteriaBuilder->toSqlPart();
$sth = $dbh->prepare($SqlPart->getSql());
$sth->execute($SqlPart->getParameters());
```

### Putting it all together

[](#putting-it-all-together)

```
$dbh = new \PDO('mysql:host=127.0.0.1;dbname=DATABASE', 'root', '');
$CriteriaBuilder
    ->sql('SELECT * FROM fooTable f LEFT JOIN barTable b ON f.bar_id = b.id AND f.is_active = :is_active')
    ->where('bar', '=', 1)
        ->andWhere('foo', 'NOT IN', [1,2,3])
        ->orWhereRaw('foo::bar() IS NULL')
    ->setParameter('is_active', false)
    ->setLimit(10)
    ->setOffset(20)
    ->setOrderBy(['foo' => 'DESC']);

$SqlPart = $CriteriaBuilder->toSqlPart();
$sth = $dbh->prepare($SqlPart->getSql());
$sth->execute($SqlPart->getParameters());
$data = $sth->fetchAll(PDO::FETCH_ASSOC);
```

### Test Driven

[](#test-driven)

[Check the tests for more examples of usage here](https://github.com/oliwierptak/everon-criteria-builder/tree/master/tests/unit)

###  Health Score

51

—

FairBetter than 96% of packages

Maintenance53

Moderate activity, may be stable

Popularity47

Moderate usage in the ecosystem

Community13

Small or concentrated contributor base

Maturity72

Established project with proven stability

 Bus Factor1

Top contributor holds 99.3% 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 ~153 days

Recently: every ~242 days

Total

14

Last Release

1809d ago

Major Versions

v1.1.6 → 2.0.02020-10-15

2.0.2 → 3.0.02021-06-04

PHP version history (3 changes)1.0.0PHP >=5.6

2.0.0PHP ^7.2

3.0.0PHP ^8

### Community

Maintainers

![](https://www.gravatar.com/avatar/73a4cc056d2b09189dda46d01b76e71161dd9b75a226230bed6bec8f414d1cf8?d=identicon)[oliwierptak](/maintainers/oliwierptak)

---

Top Contributors

[![oliwierptak](https://avatars.githubusercontent.com/u/495101?v=4)](https://github.com/oliwierptak "oliwierptak (150 commits)")[![dereuromark](https://avatars.githubusercontent.com/u/39854?v=4)](https://github.com/dereuromark "dereuromark (1 commits)")

---

Tags

builderdqlfluidfluid-interfacephpsql

###  Code Quality

TestsPHPUnit

### Embed Badge

![Health badge](/badges/everon-criteria-builder/health.svg)

```
[![Health](https://phpackages.com/badges/everon-criteria-builder/health.svg)](https://phpackages.com/packages/everon-criteria-builder)
```

###  Alternatives

[doctrine/orm

Object-Relational-Mapper for PHP

10.2k285.3M6.2k](/packages/doctrine-orm)[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k115.1M102](/packages/jdorn-sql-formatter)[illuminate/database

The Illuminate Database package.

2.8k52.4M9.4k](/packages/illuminate-database)[ramsey/uuid-doctrine

Use ramsey/uuid as a Doctrine field type.

90440.3M211](/packages/ramsey-uuid-doctrine)[reliese/laravel

Reliese Components for Laravel Framework code generation.

1.7k3.4M16](/packages/reliese-laravel)[wildside/userstamps

Laravel Userstamps provides an Eloquent trait which automatically maintains `created\_by` and `updated\_by` columns on your model, populated by the currently authenticated user in your application.

7511.7M13](/packages/wildside-userstamps)

PHPackages © 2026

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