Hydrahon

Hydrahon is a standalone query builder component. It was built to enhance existing frameworks and applications that handle the database connection on their own. It doesn't come with a PDO or mysqli wrapper. The naming is heavily inspired by Eloquent and Kohana Framework Database component.

Status

• The Hydrahon MySQL query builder is stable and used in production.
• The Hydrahon AQL (Arango Query Langauge) 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:

$ composer require 'clancats/hydrahon:dev-master'

Usage MySQL

Create a builder

Again Hydrahon is not built as a database library, it's just a query builder. In this example, I'm going to present you an easy example of a PDO mysql implementation.

$connection = new PDO('mysql:host=localhost;dbname=my_database', 'username', 'password');

$hydrahon = new \ClanCats\Hydrahon\Builder('mysql', function($query, $queryString, $queryParameters) use($connection)
{
    $statement = $connection->prepare($queryString);
    $statement->execute($queryParameters);

    if ($query instanceof \ClanCats\Hydrahon\Query\Sql\FetchableInterface)
    {
        return $statement->fetchAll(\PDO::FETCH_ASSOC);
    }
});

---

Structure

• Basics
• Select
  • Runners
  • Basics
  • Where
  • Order By
  • Join
  • Limit and Offset

---

Note: Please note that in the following examples the variable $h contains a Hydrahon query builder instance.

---

Basics

Lets start with a super basic example:

Inserting:

$h->table('people')->insert(
[
    ['name' => 'Ray', 'age' => 25],
    ['name' => 'John',  'age' => 30],
    ['name' => 'Ali', 'age' => 22],
])->execute();

Updating:

$h->table('people')->update()->set('age', 26)->where('name', 'Ray')->execute();

Deleting:

$h->table('people')->delete()->where('name', 'John')->execute();

Selecting:

$h->table('people')->select()->get();

---

SQL Select

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.

$users = $h->table('users');

Runners

The runner methods execute your query and return a result. There are many diffrent runner methods and each one acts like an helper. This means a runner method can modifiy your query and the result.

"Execute" method

The execute method is an alias of executeResultFetcher, this means the method just forwards the plain data that you return inside your ClanCats\Hydrahon\Builder instance callback.

$users->select()->limit(10)->execute();

"Get" method

The default runner method is the get method which can do some operations on your data.

$users->select(['name'])->where('age', '>', 22)->get();

For example by setting the limit of your query to one, you will also receive just that one single result. (Not an array of results).

$users->select()->get(); // returns: array(array(name: joe))
$users->select()->limit(1)->get(); // returns: array(name: joe)

"One" method

$users->select()->where('name', 'jeffry')->one();

first and last result

Returns the first result of table orderd by the default key id.

$users->select()->first();
// or 
$users->select()->last();

You can also pass a different key.

$users->select()->first('created_at');

count results

This special guy returns you the count of the current query:

$users->select()->where('age', '>', 18)->count();

single column result

Sometimes you just need one value, for that we have the column function

$users->select()->where('name', 'johanna')->column('age');

Basics

Selecting everything

$users->select()

select * from `users`

Select some special fields. Hydrahon parses your input, that allows you to use the query builder the way you are comfortable with.

$users->select(['name', 'age'])
// or
$users->select('name, age')

select `name`, `age` from `users`

Of course you can alias fields, you can define them as array keys or with the as token.

$users->select(['name', 'age', 'created_at' => 'c'])
// or
$users->select(['name', 'age', 'created_at as c'])

select `name`, `age`, `created_at` as `c` from `users`

Sometimes you might have a special case that hydrahon does not cover natively. For such cases you can make use of raw expressions, those will not get parsed or escaped.

$users->select([$users->raw("max('age')")])

select max('age') from `users`

Where

The where statement does not only apply to the select query, but also to update and delete.

$users->select()->where('active', 1)

select * from `users` where `active` = ?

You might wonder why there is an ? in the query. The given 1 gets automatically passed as prepared parameter to avoid sql injection.

Setting multiple where statements will result in an and statement.

$users->select()->where('active', 1)->where('age', '>', 18)

select * from `users` where `active` = ? and `age` > ?

or?

Of course there is also an or where statement.

$users->select()->where('active', 1)->orWhere('admin', 1)

select * from `users` where `active` = ? or `admin` = ?

Scopes

You can scope wheres by using callbacks.

$users->select()
	->where('age', '>', 18)
	->where(function($q) {
		$q->where('active', 1)->orWhere('admin', 1);
	});

select * from `users` where `age` > ? and ( `active` = ? or `admin` = ? )

in array

Arrays can also be passed as where parameters.

$users->select()->where('id', 'in', [213, 32, 53, 43]);

select * from `users` where `id` in (?, ?, ?, ?)

Ordering

$users->select()->orderBy('name');

select * from `users` order by `name` asc

Setting the order direction.

$users->select()->orderBy('name', 'desc');

select * from `users` order by `name` desc

Ordering with multiple keys

Again, there are several ways you can do this, my philosophy is to give you as much freedom as possible.

$users->select()->orderBy('name, created_at');
// or 
$users->select()->orderBy(['name', 'created_at']);
// or 
$users->select()->orderBy('name')->orderBy('created_at');

select * from `users` order by `name` desc, `created_at` asc

When passing an array, you can also define the direction as array value.

$users->select()->orderBy(['name', 'created_at' => 'desc']);

select * from `users` order by `name` asc, `created_at` desc

Joins

The automatic escaping becomes really handy when working with multiple tables.

$users->select(['users.name', 'img.url'])
	->join('user_images as img', 'users.id', '=', 'img.user_id')
	->where('img.active', 1)

select `users`.`name`, `img`.`url` 
	from `users` 
	left join `user_images` as `img` on `users`.`id` = `img`.`user_id` 
	where `img`.`active` = ?

The default join type is left, for every join type there is its own method.

• leftJoin
• rightJoin
• innerJoin
• outterJoin

Limit, Offset and Page

When setting the limit to just one entry, you will receive it as a single result and not as result collection.

$users->select()->limit(1); // returns single result

select * from `users` limit 0, 1

$users->select()->limit(2); // returns an array of results.

select * from `users` limit 0, 2

with offset:

$users->select()->limit( 25, 10 );

select * from `users` limit 25, 10

simple paging:

users->select()->page(0);

select * from `users` limit 0, 25

The default page size is 25 entries.

users->select()->page(3, 15);

select * from `users` limit 45, 15