ClanCats Logo

Hydrahon

Hydrahon

🐉 Fast & standalone PHP MySQL Query Builder library.

Star Watch

Hydrahon

Hydrahon banner

Hydrahon is a query builder, and only a query builder. It does not contain a PDO wrapper or anything. It's built to add query building into existing systems without implementing an entire new Database layer.

Build Status Packagist [Packagist]() GitHub release

#   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

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
...
...
...