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

#  Status

This library is still in work.

  • [x] SQL query structure
  • [x] SQL select query builder
  • [x] Mysql select query translator
  • [x] SQL insert query builder and translator
  • [x] SQL update query builder and translator
  • [x] SQL delete query builder and translator
  • [ ] Port more selection result helpers
  • [ ] Clean up translation unit tests.

#   Installation

Hydrahon follows PSR-4 autoloading and can be installed using composer:

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

#  Usage

#  Creating hydrahon 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\Select)
    {
        return $statement->fetchAll(\PDO::FETCH_ASSOC);
    }
});

#  SQL query builder

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

 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

Also, the examples do not show the run method, which has to be executed to (obviously) run the query.

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

There are also other runners to cover common use cases.

single result

Instead of retrieving an array of results you can direclty access a single one.

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