, (*1)
Where
The simplest fluent SQL query builder ever., (*2)
Built in PHP7.1 with immutability in mind., (*3)
Features
- Framework agnostic, connection agnostic (you just render a string and an array of values)
- Natural language: where, and, or, ...
- Support named and numeric placeholders
- Build complex, nested WHERE conditions
- Helpers for building
SELECT, INSERT, UPDATE, DELETE, REPLACE queries
- Helpers for SQL functions like
IN, BETWEEN, IS NULL, CASE ... WHEN
Why?
In most cases simple SQL queries are fine., (*4)
But if your application logic is designed in a way that several classes / methods can modify an SQL query (like the Visitor pattern), then you'll probably need query builder
(you can define LIMIT / OFFSET before WHERE for instance, and the query will be rendered in the correct order)., (*5)
Conditions builder
Where allows you to build your conditions with Expressions. Expressions are objects that can be:, (*6)
- Simple expressions:
date_added = CURRENT_DATE
- Composite expressions:
date_added = CURRENT_DATE OR date_added = SUBDATE(CURRENT_DATE, INTERVAL 1 DAY)
- Group expressions:
(country = 'UK' OR country = 'BE')
- Negated expressions:
NOT date_added = CURRENT_DATE
An Expression object can also contain an array of parameters to bind (to avoid SQL injections)., (*7)
You don't need to instanciate them. Just rely on the powerful functions the library offers:, (*8)
require_once __DIR__ . '/vendor/autoload.php';
use function BenTools\Where\group;
use function BenTools\Where\not;
use function BenTools\Where\where;
$where = where('country IN (?, ?)', ['FRA', 'UK'])
->and(
not(
group(
where('continent = ?', 'Europe')
->or('population < ?', 100000)
)
)
);
print((string) $where);
print_r($where->getValues());
print_r($where->preview()); // For debugging purposes
Outputs:, (*9)
country IN (?, ?) AND NOT (continent = ? OR population < ?)
```php
Array
(
[0] => FRA
[1] => UK
[2] => Europe
[3] => 100000
), (*10)
```mysql
country IN ('FRA', 'UK') AND NOT (continent = 'Europe' OR population < 100000)
Every function where(), group(), not() accepts either an already instanciated Expression object, or a string and some optionnal parameters., (*11)
$where = where('date > NOW()'); // valid
$where = where($where); // valid
$where = where(group($where)); // valid
$where = where(not($where)); // valid
$where = where('date = ?', date('Y-m-d')); // valid
$where = where('date BETWEEN ? AND ?', date('Y-m-d'), date('Y-m-d')); // valid
$where = where('date BETWEEN ? AND ?', [date('Y-m-d'), date('Y-m-d')]); // valid
$where = where('date BETWEEN :start AND :end', ['start' => date('Y-m-d'), 'end' => date('Y-m-d')]); // valid
$where = where('date BETWEEN :start AND :end', ['start' => date('Y-m-d')], ['end' => date('Y-m-d')]); // not valid
$where = where($where, date('Y-m-d'), date('Y-m-d')); // not valid (parameters already bound)
Thanks to the fluent interface, let your IDE guide you for the rest. Don't forget Where is always immutable: reassign $where everytime you do some changes., (*12)
Select Query Builder
Now you've learnt how to build conditions, you'll see how building a whole select query is a piece of cake:, (*13)
require_once __DIR__ . '/vendor/autoload.php';
use function BenTools\Where\group;
use function BenTools\Where\not;
use function BenTools\Where\select;
use function BenTools\Where\where;
$select = select('b.id', 'b.name AS book_name', 'a.name AS author_name')
->from('books as b')
->innerJoin('authors as a', 'a.id = b.author_id')
->limit(10)
->orderBy('YEAR(b.published_at) DESC', 'MONTH(b.published_at) DESC', 'b.name')
->where(
group(
where('b.series = ?', 'Harry Potter')->or('b.series IN (?, ?)', ['A Song of Ice and Fire', 'Game of Thrones'])
)
->and('b.published_at >= ?', new \DateTime('2010-01-01'))
->and(
not('b.reviewed_at BETWEEN ? AND ?', new \DateTime('2016-01-01'), new \DateTime('2016-01-31 23:59:59'))
)
);
print_r((string) $select); // The SQL string
print_r($select->preview()); // For debugging purposes
SELECT b.id, b.name AS book_name, a.name AS author_name
FROM books as b
INNER JOIN authors as a ON a.id = b.author_id
WHERE (b.series = ? OR b.series IN (?, ?))
AND b.published_at >= ?
AND NOT b.reviewed_at BETWEEN ? AND ?
ORDER BY YEAR(b.published_at) DESC, MONTH(b.published_at) DESC, b.name
LIMIT 10;
SELECT b.id, b.name AS book_name, a.name AS author_name
FROM books as b
INNER JOIN authors as a ON a.id = b.author_id
WHERE (b.series = 'Harry Potter' OR b.series IN ('A Song of Ice and Fire', 'Game of Thrones'))
AND b.published_at >= '2010-01-01 00:00:00'
AND NOT b.reviewed_at BETWEEN '2016-01-01 00:00:00' AND '2016-01-31 23:59:59'
ORDER BY YEAR(b.published_at) DESC, MONTH(b.published_at) DESC, b.name
LIMIT 10;
Let your favorite IDE do the rest with autocompletion., (*14)
RulerZ
Where is mostly compatible with the awesome RulerZ DSL., (*15)
Be sure to group your statements to avoid downside effects., (*16)
Example:, (*17)
use function BenTools\Where\group;
use function BenTools\Where\not;
$fruits = [
'banana',
'apple',
];
$colors = [
'yellow',
'red',
];
$condition = group('fruit IN :fruits', ['fruits' => $fruits])->and(group('color IN :colors', ['colors' => $colors]));
$condition = not($condition);
var_dump($rulerz->satisfies(['fruit' => 'strawberry', 'color' => 'red'], (string) $condition, $condition->getValues())); // true
var_dump($rulerz->satisfies(['fruit' => 'apple', 'color' => 'yellow'], (string) $condition, $condition->getValues())); // false
Installation
composer require bentools/where, (*18)
Tests
./vendor/bin/phpunit, (*19)
See also
bentools/simple-dbal - A PHP 7.1+ wrapper for PDO & Mysqli. Can bind DateTime parameters., (*20)
bentools/pager - A PHP 7.1+ pager., (*21)
bentools/flatten-iterator - Flattens multiple array or Traversable into one iterator., (*22)
bentools/etl - A PHP7.1 ETL pattern implementation., (*23)
latitude/latitude - Another SQL Query builder Where was inspired of., (*24)
Wallogit.com
