Query Builder

An object oriented Query Builder

[Since 0.6.0]

Introduction

Pletfix provides an easy to use and powerful QueryBuilder for

  • MySQL
  • PostgreSQL
  • SQLite and
  • Microsoft SQL Server.

Pletfix has studied these libraries, picked up ideas from them, and partly adopted codes:

Create A Builder Instance

You may use the builder method of a Database instance to create a new builder:

$builder = database()->builder();

In most cases, the query will refer to a table. Therefore, Database also provides the table method to create a builder:

$builder = database()->table('books');

You could also define an alias for your table if you prefer it:

$builder = database()->table('books', 't1');

Note, the table method is just a shortcut for this:

$builder = database()->builder()->from('books', 't1');

Select Query Language

General Usage

The QueryBuilder provides you with many methods to create an SQL statement. You can use the methods in any order and fluency. Some methods may also be called several times.

$sql = database()->builder()
    ->select('b.id, b.title, a.name AS author')   
    ->from('author', 'a)
    ->join('books', 'a.id = b.author_id', 'b')
    ->whereCondition('a.id = ?', [4711]);

Bindings

The most methods for specify a SQL clause pass binding values at the last argument.

$users = $builder->whereCondition('role = ?', ['guest']);

Instead of using ? to represent your parameter bindings, you may use named bindings:

$users = $builder->whereCondition('role = :role', ['role => 'guest]);

No matter how, it is highly recommended to use parameter binding because its provides protection against SQL injection.

SQL Clauses

Method Listing

select()

The select method adds columns to the query.

Multiple calls to select() will append to the list of columns, not overwrite the previous columns.

For computed columns, you should only use standard SQL operators and functions, so that the database drivers can translate the expression correctly.

Examples:

// as comma-separated string list:
$builder->select('column1, t1.column2 AS c2')

// as array:
$builder->select(['column1', 't1.column2 AS c2', 'c3' => 't1.column3'])

// with calculated columns:
$builder->select('COUNT(*) AS c1')
$builder->select(['c1' => 'COUNT(*)'])

// with subquery:
$builder->select(['c1' => 'SELECT MAX(i) FROM table2'])
$builder->select(['c1' => database()->createBuilder()->from('table2')->select('MAX(i)')])
$builder->select(['c1' => function(Builder $builder2) { return $builder2->from('table2')->select('MAX(i)'); }])

// with placeholders:
$builder->select(['c1' => 'column2 * ?'], [$foo])

Note, that subqueries are not quoted, because the Builder of the subquery should do this work.

distinct()

The distinct method makes the select DISTINCT.

$builder->distinct();

from()

The from method adds a FROM clause to the query.

Multiple calls to from() will append to the list of sources, not overwrite the previous sources.

Examples:

// from table
$builder->from('table1')

// with alias:
$builder->from('table1', 't1')

// from subquery:
$builder->from('SELECT * FROM table1', 't1')
$builder->from(database()->createBuilder()->from('table1'), 't1')
$builder->from(function($builder2) { return $builder2->from('table1'); }, 't1')

// from subquery with placeholders:
$builder->from($builder2->from('table1')->whereCondition('column1 > ?'), 't1', [$foo])

Note, that subqueries are not quoted, because the Builder of the subquery should do this work.

join()

The join method adds a INNER JOIN clause to the query.

You should only use standard SQL operators and functions for the ON clause, so that the database drivers can translate the expression correctly.

Examples:

// from table:
$builder->join('table2', 'table1.id = table2.table1_id')

// with alias:
$builder->join('table2', 't1.id = t2.table1_id', 't2')

// with subquery:
$builder->join('SELECT * FROM table2', 't1.id = t2.table1_id', 't2')
$builder->join(database()->createBuilder()->from('table2'), 't1.id = t2.table1_id', 't2')
$builder->join(function(Builder $builder2) { return $builder2->from('table2'); }, 't1.id = t2.table1_id', 't2')

// from subquery with placeholders:
$builder->join($builder2->from('table2')->whereCondition('column1 > ?'), 't1.id = t2.table1_id', 't2', [$foo])

Note, that subqueries are not quoted, because the Builder of the subquery should do this work.

leftJoin()

The leftJoin method adds a LEFT JOIN clause to the query.

$builder->leftJoin($source, $on, $alias, $bindings);

See the join method for details and examples.

rightJoin()

The rightJoin method adds a RIGHT JOIN clause to the query.

$builder->rightJoin($source, $on, $alias, $bindings);

See the join method for details and examples.

where()

The where method adds a comparison operation into the WHERE clause.

The operator is the third argument and could be one of this: '=', '<', '>', '<=', '>=', '<>', '!=', 'IN', 'NOT IN', 'LIKE' or 'NOT LIKE'. The default is '='.

$builder->where('column1', 4711, '>')

orWhere()

The orWhere method adds a comparison operation into the WHERE clause by OR.

$builder->orWhere($column, $value, $operator);

See the where method for details and examples.

whereCondition()

The whereCondition method adds a WHERE condition to the query.

You should only use standard SQL operators and functions, so that the database drivers can translate the expression correctly.

Examples:

$builder->whereCondition('column1 = ? OR t1.column2 LIKE "%?%"', [$foo, $bar])
$builder->whereCondition('column1 = (SELECT MAX(i) FROM table2 WHERE c1 = ?)', [$foo])
$builder->whereCondition(function(Builder $builder2) { return $builder2->whereCondition('c1 = ?')->orWhereCondition('c2 = ?'); }, [$foo, $bar])

Note, that subqueries are not quoted, because the Builder of the subquery should do this work.

orWhereCondition()

The orWhereCondition method adds a WHERE condition to the query by OR.

$builder->orWhereCondition($condition, $bindings);

See the whereCondition method for details and examples.

whereSubQuery()

The whereSubQuery method adds a subquery into the WHERE clause.

The operator is the third argument and could be one of this: '=', '<', '>', '<=', '>=', '<>', '!=', 'IN', 'NOT IN', 'LIKE' or 'NOT LIKE'. The default is '='.

Examples:

$builder->whereSubQuery('column1', 'SELECT MAX(i) FROM table2 WHERE c1 = ?', '<=', [$foo])
$builder->whereSubQuery('column1', database()->createBuilder()->select('MAX(i)')->from('table2')->whereCondition('c1 = ?'), [$foo])
$builder->whereSubQuery('column1', function(Builder $builder2) { return $builder2->select('MAX(i)')->from('table2')->whereCondition('c1 = ?'); }, [$foo])

Note, that subqueries are not quoted, because the Builder of the subquery should do this work.

orWhereSubQuery()

The orWhereSubQuery method adds a subquery into the WHERE clause by OR.

$builder->orWhereSubQuery($column, $query, $operator, $bindings);

See the whereSubQuery method for details and examples.

whereExists()

The whereExists method adds "WHERE EXISTS( SELECT... )" to the query.

Examples:

$builder->whereSubQuery('column1', 'SELECT MAX(i) FROM table2 WHERE c1 = ?', [$foo])
$builder->whereSubQuery('column1', database()->createBuilder()->select('MAX(i)')->from('table2')->whereCondition('c1 = ?'), [$foo])
$builder->whereSubQuery('column1', function(Builder $builder) { return $builder->select('MAX(i)')->from('table2')->whereCondition('c1 = ?'); }, [$foo])

Note, that subqueries are not quoted, because the Builder of the subquery should do this work.

orWhereExists()

The orWhereExists method adds "WHERE EXISTS( SELECT... )" to the query by OR.

$builder->orWhereExists($query, $bindings);

See the whereExists method for details and examples.

whereNotExists()

The whereNotExists method add "WHERE NOT EXISTS( SELECT... )" to the query.

$builder->whereNotExists($query, $bindings);

See the whereExists method for details and examples.

orWhereNotExists()

The orWhereNotExists method adds "WHERE NOT EXISTS( SELECT... )" to the query by OR.

$builder->orWhereNotExists($query, $bindings);

See the whereExists method for details and examples.

whereIn()

The whereIn method adds "WHERE column IN (?,?,...)" to the query.

$builder->whereIn('column1', [1, 2, 3])

orWhereIn()

The orWhereIn method adds "WHERE column IN (?,?,...)" to the query by OR.

$builder->orWhereIn($column, $values);

See the whereIn method for an example.

whereNotIn()

The whereNotIn method adds "WHERE column NOT IN (?,?,...)" to the query.

$builder->whereNotIn($column, array $values, $or = false);

See the whereIn method for an example.

orWhereNotIn()

The orWhereNotIn method adds "WHERE column NOT IN (?,?,...)" to the query by OR.

$builder->orWhereNotIn($column, array $values);

See the whereIn method for an example.

whereBetween()

The whereBetween method adds "WHERE column BETWEEN ? AND ?" to the query.

$builder->whereBetween('column1', 123, 789)

orWhereBetween()

The orWhereBetween method adds "WHERE column BETWEEN ? AND ?" to the query by OR.

$builder->orWhereBetween($column, $lowest, $highest);

See the whereBetween method for an example.

whereNotBetween()

The whereNotBetween method adds "WHERE column NOT BETWEEN ? AND ?" to the query.

$builder->whereNotBetween($column, $lowest, $highest);

See the whereBetween method for an example.

orWhereNotBetween()

The orWhereNotBetween method adds "WHERE column NOT BETWEEN ? AND ?" to the query by OR.

$builder->orWhereNotBetween($column, $lowest, $highest);

See the whereBetween method for an example.

whereNull()

The whereNull method adds "WHERE column IS NULL to the query.

$builder->whereNull($column);

orWhereNull()

The orWhereNull method adds "WHERE column IS NULL to the query by OR.

$builder->orWhereNull($column);

whereNotNull()

The whereNotNull method adds "WHERE column IS NOT NULL to the query.

$builder->whereNotNull($column);

orWhereNotNull()

The orWhereNotNull method adds "WHERE column IS NOT NULL to the query by OR.

$builder->orWhereNotNull($column);

groupBy()

The groupBy method adds GROUP BY clause to the SELECT query.

Examples:

// as comma-separated string list:
$builder->groupBy('column1, t2.column2')

// as array:
$builder->groupBy(['column1', 't2.column2'])

having()

The having method adds a HAVING condition to the SELECT query.

You should only use standard SQL operators and functions, so that the database drivers can translate the expression correctly.

Examples:

$builder->having('column1 = ? OR t1.column2 LIKE "%?%"', [$foo, $bar])
$builder->having('column1 = (SELECT MAX(i) FROM table2 WHERE c1 = ?)', [$foo])
$builder->having(function(Builder $builder2) { return $builder2->having('c1 = ?')->orHaving('c2 = ?'); }, [$foo, $bar])

Note, that subqueries are not quoted, because the Builder of the subquery should do this work.

orHaving()

The orHaving method adds a HAVING condition to the SELECT query by OR.

$builder->orHaving($condition, $bindings);

See the having method for details and examples.

orderBy()

The orderBy method adds a ORDER BY clause to the SELECT query.

Examples:

// as comma-separated string list:
$builder->orderBy('column1, column2 ASC, t1.column3 DESC')

// as array:
$builder->orderBy(['column1', 'column2 ASC', 't1.column3 DESC'])

limit()

The limit method sets a limit count for the result set of the SELECT query.

$builder->limit($limit);

Note, that LIMIT does not see the query, it is just see the result set. Therefore, LIMIT has no effect on the calculation of aggregate functions such like MIN or MAX.

offset()

The offset method sets a limit offset for the result set of the SELECT query.

$builder->offset($offset);

Note, that OFFSET does not see the query, it is just see the result set. Therefore, OFFSET has no effect on the calculation of aggregate functions such like MIN or MAX.

Execute Query

Method Listing

dump()

The dump method binds the given values to the query and print the SQL statement out without executing.

$builder->dump();

If the argument is used and set to true, dump() will return the variable representation instead of outputing it:

$sql = $builder->dump(true);

find()

The find method find a single record by the primary key of the table.

$result = $builder->find($id);

You could enter the name of the primary key as second argument (the default is 'id'):

$result = $builder->find($id, $key = 'id');

all()

The all method executes the query as a "select" statement and returns the result as a Collection.

$result = $builder->all();

cursor()

This method is useful to handle big data.

The cursor method executes the query as a "SELECT" statement and returns a generator.

With the cursor you could iterate the rows (via foreach) without fetch all the data at one time.

foreach ($builder->cursor() as $row) {
    // ...
};

Note, this method ignores the "with" clause, because the data could not be eager load.

first()

The first method executes the query as a "SELECT" statement and return the first record.

$result = $builder->first();

value()

The value method execute the query as a "SELECT" statement and return a single column's value from the first record.

$value = $builder->value();

Note, this method ignores the "with" clause, because the data could not be eager load.

Aggregates Functions

Method Listing

count()

The count method calculates the number of records.

$count = $builder->count();

avg()

The avg method calculates the average value of a given column.

$avg = $builder->select('price')->avg();

$avg = $builder->avg('price');

max()

The max method calculates the maximum value of a given column.

$max = $builder->select('price')->max();

$max = $builder->max('price');

min()

The min method calculates the minimum value of a given column.

$min = $builder->select('price')->min();

$min = $builder->min('price');

sum()

The sum method calculates the total value of a given column.

$sum = $builder->select('price')->sum();

$sum = $builder->sum('price');

SQL Operators and Functions

TODO

@see https://www.w3schools.com/sql/sql_operators.asp Standard SQL Operators @see https://www.w3schools.com/sql/sql_functions.asp Standard SQL Aggregate Functions @see https://www.w3schools.com/sql/sql_isnull.asp Standard SQL NULL Functions

Data Manipulation Language

Method Listing

insert()

The insert method inserts one or more records to the table and returns the inserted autoincrement sequence value.

$id = database()
    ->table('users')
    ->insert['firstname' => 'Stephen', 'lastname' => 'Hawking']);

Bulk Mode:

database()->table('users')->insert[
    ['firstname' => 'Stephen', 'lastname' => 'Hawking']
    ['firstname' => 'Albert', 'lastname' => 'Einstein'],
]);

If you insert multiple rows, the method returns dependency of the driver the first or last inserted id!

The method returns FALSE if the operation was canceled by a hook. You may disable this behavior via the disableHooks method.

update()

The update method updates all records of the query result with the given data and returns the number of affected rows.

database()
    ->table('users')
    ->whereCondition('role = ?', ['guest'])
    ->update(['lastname' => 'Hawking']);

The method returns FALSE, if the operation was canceled by a hook. You may disable this behavior via the disableHooks method.

delete()

The delete method deletes all records of the query result and returns the number of affected rows.

database()
    ->table('users')
    ->whereCondition('id = ?', [4711])
    ->delete();

The method returns FALSE, if the operation was canceled by a hook. You may disable this behavior via the disableHooks method.

Miscellanea Functions

Method Listing

asClass()

The asClass method sets the name of the class where the data are mapped to.

If null is passed, the data will be returned as an array (the default).

$builder->asClass($class);

If the class implements the Hookable contract, the QueryBuilder bind the hooks that are provided by this class. You may disable this behavior via the disableHooks method.

See also getClass method.

bindings()

The bindings method get the bindings of the query.

$bindings = $builder->bindings();

copy()

The copy method gets a copy of the instance.

$builder = $builder->copy();

disableHooks()

The disableHooks method disables hooks. You may use this method if you have a class with hooks, but the hooks should be ignored.

$sql = $builder->disableHooks();

See also the enableHooks, asClass and hooks methods.

enableHooks()

The enableHooks method enables hooks. The hooks of the class you specified using method asClass will be invoke, presupposed that the class implements the Hookable contract.

$sql = $builder->enableHooks();

Note, that hooks are enabled by default.

See also the disableHooks, asClass and hooks methods.

getClass()

The getClass method gets the Class.

$lcass = $builder->getClass();

See also asClass method.

getTable()

The getTable method gets the name of the table if specify.

$builder->getTable();

reset()

The reset method resets the query.

$builder->reset();

toSql()

The toSql method get the SQL representation of the query.

$sql = $builder->toSql();

with()

The with method gets the entities of given relationship via eager load.

$builder
    ->asClass('App\Models\Author')
    ->with('books');

Note, the class, that provides the given relationship, must be specified by the asClass method.

See the Model class to learn how you could define the relationship method.


(edit on GitHub)