Query builder¶

While you can manually type your queries, it is not recommended. Not only do queries typically look ugly in code, but writing them yourself creates extra work if you wish to stay secure.

For this reason, Horizon has a complete query builder, equipped with automatic prepared statements and support for virtually every type of query.

Global access¶

There are two global classes in the root namespace called Database and DB which point to Horizon's internal query builder interface. Use these to get started.

Queries¶

To start a query builder instance, you must call the method for the type of query you want.

Database::alter()
Database::create()
Database::delete()
Database::drop()
Database::insert()
Database::select()
Database::show()
Database::update()

To run a query without using the query builder, call the query() method.

Database::query('SELECT * FROM users');

Alter¶

Create¶

Delete¶

Drop¶

Insert¶

Select¶

Show¶

Update¶

Getting results¶

Once you've built your queries using the methods above, you have a few options.

Execution¶

For modification queries (INSERT, UPDATE, ALTER, etc), you should use exec. This will return the number of affected rows if applicable, or a boolean for success.

$query->exec();

Fetch rows¶

To execute the query and get all returned rows as objects (or models if using via ORM), call the get method.

$rows = $query->get();

Compile¶

Compiles the query into a string which you may use for your own purposes.

$query->compile();

Prepared parameters¶

Mainly for internal use, if the compiled query has any ? bindings, this array contains their real values.

$query->getParameters();

Features¶

Enclosures¶

Encloses are parenthesis around conditions to form more advanced statements. And yes, you can put enclosures inside other enclosures.

// SELECT * FROM `table` WHERE ( `id` = ? OR `balance` >= ? )
// OR ( `id` = ? OR `balance` > ? );

$query->enclose(function($builder) {
    $builder->where('id', '=', 10);
    $builder->orWhere('balance', '>=', 1000);
});

$query->orEnclose(function($builder) {
    $builder->where('id', '=', 9);
    $builder->orWhere('balance', '>', 10000);
});

Functions¶

To supply functions in where conditions, such as NOW(), use a string. For functions with parameters, use an array.

// WHERE `time` = NOW()
$query->where('time', '=', 'NOW()');

// WHERE `time` = NOW(10, 20)
$query->where('time', '=', array('NOW()', 10, 20));

Raw references¶

The Horizon\Database\QueryBuilder\RawReference helper class can be used to insert a raw segment into a query. This is a fallback for more complicated cases where the query builder might not handle it properly.

$query->where('time', '=', new RawReference('NOW()'));

The global db_verbatim() helper function builds upon this concept, allowing you to pass multiple values which will be inserted directly into the query, delimited by spaces.

Database::update()->values(array(
    'views' => db_verbatim(db_ref('views'), '+1')
));

Column references¶

The global db_ref() helper function can be used to insert a column name into a query. This is useful when referencing a column in your query.

$query->where('balance', '>=', db_ref('minimum_balance'));

Examples¶

Selecting rows¶

Database::select()
    ->columns('username', 'email')
    ->from('users')
    ->where('id', '=', 5)
    ->andWhere('deleted', '=', false);

Joins¶

Database::select()
    ->columns('table1.a', 'table2.b')
    ->from('table1', 'table2')

Inserting rows¶

Database::insert()
    ->into('table')
    ->values(array(
        'id' => 1,
        'username' => 'john.doe'
    ));

You can also supply an array of arrays into values for multiple inserts at once.

->values(array(
    array('id' => 1, 'username' => 'john.doe'),
    array('id' => 2, 'username' => 'jane.doe')
));

Updating rows¶

Database::update()
    ->table('table')
    ->values(array(
        'email' => 'john@doe.org',
        'username' => 'john.doe'
    ))
    ->where('id', '=', 1);

Deleting rows¶

Database::delete()
    ->from('table')
    ->where('id', '=', 1)
    ->limit(1);