bentools / where
PHP7.1 Fluent, immutable SQL query builder. Connectionless, framework-agnostic, no dependency.
Installs: 5 221
Dependents: 2
Suggesters: 0
Security: 0
Stars: 12
Watchers: 3
Forks: 1
Open Issues: 0
Requires
- php: >=7.1
Requires (Dev)
- php-coveralls/php-coveralls: ^2.1
- phpunit/phpunit: ^7.0
- squizlabs/php_codesniffer: ^3.2
- symfony/var-dumper: ^4.0
Suggests
- bentools/etl: PHP7.1 ETL pattern implementation
- bentools/pager: A simple OOP pager.
- bentools/simple-dbal: A PDO / MySqli wrapper handling lazy loading, parallel and asynchronous queries.
This package is auto-updated.
Last update: 2024-10-22 22:41:12 UTC
README
Where
The simplest fluent SQL query builder ever.
Built in PHP7.1 with immutability in mind.
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.
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).
Conditions builder
Where allows you to build your conditions with Expressions. Expressions are objects that can be:
- 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).
You don't need to instanciate them. Just rely on the powerful functions the library offers:
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:
country IN (?, ?) AND NOT (continent = ? OR population < ?)
Array ( [0] => FRA [1] => UK [2] => Europe [3] => 100000 )
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.
$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.
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:
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.
RulerZ
Where is mostly compatible with the awesome RulerZ DSL.
Be sure to group
your statements to avoid downside effects.
Example:
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
Tests
./vendor/bin/phpunit
See also
bentools/simple-dbal - A PHP 7.1+ wrapper for PDO & Mysqli. Can bind DateTime
parameters.
bentools/pager - A PHP 7.1+ pager.
bentools/flatten-iterator - Flattens multiple array
or Traversable
into one iterator.
bentools/etl - A PHP7.1 ETL pattern implementation.
latitude/latitude - Another SQL Query builder Where was inspired of.