rougin / windstorm
A chainable, expressive, and interoperable SQL query builder.
Requires
- php: >=5.3.0
- doctrine/dbal: ~2.5
Requires (Dev)
- doctrine/orm: ~2.4
- illuminate/database: >=4.1
- phpunit/phpunit: ~4.2|~5.7
- scrutinizer/ocular: ~1.1
This package is auto-updated.
Last update: 2024-04-25 07:33:53 UTC
README
Windstorm is an expressive SQL query builder based intially on top of Doctrine's Database Abstraction Layer (DBAL). It has the same functionalities from DBAL's query builder but the difference is it does not requires a Doctrine\DBAL\Connection
instance. Its goal is to be a single interface for handling SQL query builders and object-relational mappers. Windstorm currently has query implementations for Doctrine (through DBAL) and Eloquent.
Why
I tried to unify Doctrine
and Eloquent
into a single interface for them to be swappable. Unfortunately the implementation is not possible because of the different core design patterns (data mapper for Doctrine while active record for Eloquent). I realized later that the one thing common for both is their query builder and it was also common on all existing ORM packages and SQL query builders.
Installation
Install Windstorm
via Composer:
$ composer require rougin/windstorm:dev-master
Basic Usage
Configuration
Since the query builder does not require Doctrine\DBAL\Connection
by default, it needs to have a specified platform defined:
use Doctrine\DBAL\Platforms\AbstractPlatform; use Rougin\Windstorm\Doctrine\Builder; use Rougin\Windstorm\Doctrine\Query; // $platform instanceof AbstractPlatform $query = new Query(new Builder($platform));
List of supported platforms for the Doctrine DBAL: https://www.doctrine-project.org/projects/doctrine-dbal/en/2.8/reference/platforms.html
Using a Connection
instance
If the platform needs to came from a database connection, use the Connection::createQueryBuilder
method instead:
use Doctrine\DBAL\Connection; use Rougin\Windstorm\Doctrine\Query; // $connection instanceof Connection $query = new Query($connection->createQueryBuilder());
For a documentation on how to get a connection: https://www.doctrine-project.org/projects/doctrine-dbal/en/2.8/reference/configuration.html
Query Builder
The query builder syntax is similar when writing SQL queries:
// $query instanceof Rougin\Windstorm\QueryInterface $query = $query ->select(array('u.id', 'u.name')) ->from('users', 'u') ->where('name')->like('%winds%') ->orderBy('created_at')->descending(); // SELECT u.id, u.name FROM users u WHERE u.name LIKE :u_name ORDER BY u.created_at DESC $sql = $query->sql(); // array(':u_name' => '%winds%') $bindings = $query->bindings();
Returning results
To return the results from a defined query, an instance must be implemented in ResultInterface
.
// $connection instanceof Doctrine\DBAL\Connection // $query instanceof Rougin\Windstorm\QueryInterface use Rougin\Windstorm\Doctrine\Result; $result = new Result($connection); $query = $query->select(array('u.*')); $query = $query->from('users'); $result = $result->execute($query); var_dump((array) $result->items());
array(3) { [0] => array(4) { 'id' => string(1) "1" 'name' => string(9) "Windstorm" 'created_at' => string(19) "2018-10-15 23:06:28" 'updated_at' => NULL } [1] => array(4) { 'id' => string(1) "2" 'name' => string(11) "SQL Builder" 'created_at' => string(19) "2018-10-15 23:09:47" 'updated_at' => NULL } [2] => array(4) { 'id' => string(1) "3" 'name' => string(12) "Rougin Gutib" 'created_at' => string(19) "2018-10-15 23:14:45" 'updated_at' => NULL } }
QueryRepository and mutators
The QueryRepository
instance is a special class that will mutate the QueryInterface
through the use of mutators (implemented in MutatorInterface
). Using this approach will seperate conditions into classes instead of defining it as methods inside a repository.
namespace Acme\Mutators; use Rougin\Windstorm\Mutators\ReturnEntity; class ReturnUser extends ReturnEntity { protected $table = 'users'; }
Available mutators that can be extended:
CreateEntity(array $data)
- generates aINSERT INTO
queryDeleteEntity(integer $id)
- generates aDELETE FROM
queryReturnEntities($limit, $offset)
- generates aSELECT
query with a limit and offsetReturnEntity(integer $id)
- generates aSELECT
query (usefirst
inResultInterface
)UpdateEntity($id, array $data)
- generates aUPDATE
query
// $query instanceof Rougin\Windstorm\QueryInterface; // $result instanceof Rougin\Windstorm\ResultInterface; use Acme\Mutators\ReturnUser; $query = $query->select(['*'])->from('users'); $query = new QueryRepository($query, $result); $query = $query->mutate(new ReturnUser(1)); var_dump($query->first());
array(4) { 'id' => string(1) "1" 'name' => string(9) "Windstorm" 'created_at' => string(19) "2018-10-15 23:06:28" 'updated_at' => NULL }
To map the result into a class, implement a mapper into a MapperInterface
:
namespace Acme\Mappers; use Acme\Models\User; class UserMapper implements MapperInterface { public function map($data) { return new User($data['id'], $data['name']); } }
// $query instanceof Rougin\Windstorm\QueryRepository; use Acme\Mappers\UserMapper; $query->mapper(new UserMapper); var_dump($query->first());
class Acme\Models\User#11 (2) { protected $id => string(1) "1" protected $name => string(6) "Windstorm" }
Not implementing a class based from the MapperInterface
will return the data as it is from from ResultInterface
.
Mixed queries
In executing SQL queries, only one QueryInterface
is allowed to be executed in ResultInterface
. But there can be scenarios wherein you need to execute a query instance then execute another query instance with the result returned from the former query. An attempt to solve this is to implement a MixedInterface
which is still a QueryInterface
but can be able to add child queries (implemented in ChildInterface
).
// $users instanceof \Rougin\Windstorm\QueryInterface // $posts instanceof \Rougin\Windstorm\QueryInterface use Rougin\Windstorm\Relation\Mixed; use Rougin\Windstorm\Relation\Child; $mixed = new Mixed($users, 'id'); $child = new Child($child, 'id', 'user_id'); $mixed->add($child, 'posts'); // SELECT u.id, u.name FROM users u echo $mixed->sql(); $child = current($mixed->all()); // SELECT p.id, p.title, p.body, p.user_id FROM posts p echo $child->sql();
Credits
License
The MIT License (MIT). Please see LICENSE for more information.