clancats/hydrahon

Fast and simple Query Builder without extra bacon, written in PHP.

v1.1.7 2017-04-15 10:20 UTC

README

Hydrahon banner

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.

Build Status Packagist Packagist GitHub release

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

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 different runner methods and each one acts like a helper. This means a runner method can modify 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 ordered 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 a 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