Connects SQL query builders from PHP in a single and understandable interface.
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.
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.
Install Windstorm
via Composer:
$ composer require rougin/windstorm:dev-master
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
Connection
instanceIf 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
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();
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
}
}
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 a INSERT INTO
queryDeleteEntity(integer $id)
- generates a DELETE FROM
queryReturnEntities($limit, $offset)
- generates a SELECT
query with a limit and offsetReturnEntity(integer $id)
- generates a SELECT
query (use first
in ResultInterface
)UpdateEntity($id, array $data)
- generates a UPDATE
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
.
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();