, (*1)
Hydrahon
Hydrahon is a standalone database / SQL query builder written in PHP. It was built to enhance existing frameworks, libraries and applications that handle the database connection on their own. It does not come with a PDO or mysqli wrapper. The naming is heavily inspired by Eloquent and the Kohana Framework Database component., (*2)
What does that mean "Standalone query builder"?, (*3)
Hydrahon only generates a query string and an array of parameters. On its own, it is not able to execute a query., (*4)
, (*5)
Status
- The Hydrahon MySQL query builder is stable and used in production.
- The Hydrahon AQL (Arango Query Language) query builder is currently in development.
- A builder for Elasticsearch is on my mind but not in development.
Installation
Hydrahon follows PSR-4 autoloading and can be installed using composer:, (*6)
$ composer require clancats/hydrahon
Documentation 💡
The full documentation can be found on clancats.io, (*7)
Quick Start (MySQL) ⚡️
Hydrahon is designed to be a pretty generic query builder. So for this quick start, we stick with SQL., (*8)
Create a builder
Again this library is not built as a full database abstraction or ORM, it is only and will always be only a query builder. This means we need to implement the database connection and fetching by ourselves. The Hydrahon constructor therefore requires you to provide a callback function that does this, and returns the results., (*9)
In this example, we are going to use PDO, (*10)
$connection = new PDO('mysql:host=localhost;dbname=my_database;charset=utf8', 'username', 'password');
// create a new mysql query builder
$h = new \ClanCats\Hydrahon\Builder('mysql', function($query, $queryString, $queryParameters) use($connection)
{
$statement = $connection->prepare($queryString);
$statement->execute($queryParameters);
// when the query is fetchable return all results and let hydrahon do the rest
// (there's no results to be fetched for an update-query for example)
if ($query instanceof \ClanCats\Hydrahon\Query\Sql\FetchableInterface)
{
return $statement->fetchAll(\PDO::FETCH_ASSOC);
}
// when the query is a instance of a insert return the last inserted id
elseif($query instanceof \ClanCats\Hydrahon\Query\Sql\Insert)
{
return $connection->lastInsertId();
}
// when the query is not a instance of insert or fetchable then
// return the number os rows affected
else
{
return $statement->rowCount();
}
});
And we are ready and set. The variable $h contains now a MySQL query builder., (*11)
Setup a simple table
To continue with our examples, we need to create a simple MySQL table., (*12)
CREATE TABLE `people` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT '',
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Inserting
Currently, we do not have any data, to fix this let's go and insert some., (*13)
// In our example we are going to execute multiple operations on the same table,
// so instead of loading the table over and over again, we store it in a variable.
$people = $h->table('people');
$people->insert(
[
['name' => 'Ray', 'age' => 25],
['name' => 'John', 'age' => 30],
['name' => 'Ali', 'age' => 22],
])->execute();
Will execute the following query:, (*14)
insert into `people` (`age`, `name`) values (?, ?), (?, ?), (?, ?)
As you can see Hydrahon automatically escapes the parameters., (*15)
However, because we are humans that get confused when there are hundreds of thousands of questions marks, I will continue to always display the runnable query:, (*16)
insert into `people` (`age`, `name`) values (25, Ray), (30, John), (22, Ali)
Updating
Ah snap, time runs so fast, "Ray" is actually already 26., (*17)
$people->update()
->set('age', 26)
->where('name', 'Ray')
->execute();
Generating:, (*18)
update `people` set `age` = 26 where `name` = 'Ray'
Currently, you might think: "Well isn't it much simpler to just write the SQL query? I mean the PHP code is even longer..."., (*19)
You have to understand that these are some very very basic examples the Hydrahon query builder starts to shine when things get more complex. However, a "Quick Start" is just the wrong place for complicated stuff, so throw an eye on the full documentation., (*20)
Deleting
Dammit John, I hate you..., (*21)
$people->delete()
->where('name', 'John')
->execute();
Generating:, (*22)
delete from `people` where `name` = 'John'
Selecting
And finally, fetch the data., (*23)
$people->select()->get();
Generating:, (*24)
select * from `people`
Result:, (*25)
[
{
"id": "1",
"name": "Ray",
"age": "26"
},
{
"id": "3",
"name": "Ali",
"age": "22"
}
]
Notice that we use ->get() to actually fetch data, while we used ->execute() for our previous queries (updates, inserts and deletes). See the full documentation for more information about the Hydrahon runners methods., (*26)
Where conditions
For the next few examples, lets assume a larger dataset so that the queries make sense., (*27)
Chaining where conditions:, (*28)
// select * from `people` where `age` = 21 and `name` like 'J%'
$people->select()
->where('age', 21)
->where('name', 'like', 'J%')
->get();
Notice how omitting the operator in the first condition ->where('age', 21) makes Hydrahon default to =., (*29)
By default all where conditions are defined with the and operator., (*30)
Different where operators:, (*31)
// select * from `people` where `name` like 'J%' or `name` like 'I%'
$people->select()
->where('name', 'like', 'J%')
->orWhere('name', 'like', 'I%')
->get();
Please check the relevant section in the full documentation for more where-functions, like
- whereIn()
- whereNotIn()
- whereNull()
- whereNotNull(), (*32)
Where scopes
Allowing you to group conditions:, (*33)
// select * from `people` where ( `age` > 21 and `age` < 99 ) or `group` = admin
$people->select()
->where(function($q)
{
$q->where('age', '>', 21);
$q->where('age', '<', 99);
})
->orWhere('group', 'admin')
->get();
Joins
Joining tables:, (*34)
// select
// `people`.`name`, `groups`.`name` as `group_name`
// from `people`
// left join `groups` on `groups`.`id` = `people`.`group_id`
$people->select('people.name, groups.name as group_name')
->join('groups', 'groups.id', '=', 'people.group_id')
->get();
Grouping
Grouping data:, (*35)
// select * from `people` group by `age`
$people->select()->groupBy('age')->get();
Ordering
Ordering data:, (*36)
// select * from `people` order by `age` desc
$people->select()->orderBy('age', 'desc')->get();
// select * from `people` order by `age` desc, `name` asc
$people->select()->orderBy(['age' => 'desc', 'name' => 'asc'])->get();
Limiting data
Limit and offset:, (*37)
// select * from `people` limit 0, 10
$people->select()->limit(10)->get();
// select * from `people` limit 100, 10
$people->select()->limit(100, 10)->get();
// select * from `people` limit 100, 10
$people->select()->limit(10)->offset(100)->get();
// select * from `people` limit 150, 30
$people->select()->page(5, 30)->get();
Small reminder this is the quick start, check out the full docs., (*38)
Credits
License
The MIT License (MIT). Please see License File for more information., (*39)
Wallogit.com
