2. Builder - TerminusStudio/ezDB GitHub Wiki
Builder classes can be used to build SQL queries without writing SQL. The functions of the builder can also be extended by extending the class.
A builder can be instantiated by passing a Connection instance.
$builder = new Builder($connection)
Each builder instance is only useful for executing one SQL statement. The builder class uses prepared statement.
Method Chaining
Most of the builder class methods supports the method chaining.
$result = $builder->table('test_table')
->where(function ($q) {
$q->where('name', 'John')
->where('age', 21);
})
->orWhere(function ($q) {
$q->where('age', 20)
->where('name', '<>', 'John);
})
->get();
Table
The table that needs to be accessed can be set by using the table method. The method accepts on parameter which is the table name.
$builder->table('table1');
The method also supports usage of table alias. Just provide the alias like shown below.
$builder->table('table1 as t');
When processing the table name, the above will be directly processed as `table` AS `t`.
Multiple table's can also be set by just calling the method again.
$builder->table('table1')->table('table2');
Warning: Some methods will throw an error if more than one table was set. It is recommended to use join statements.
Insert
Data can be inserted into tables by using the insert method. The data needs to be formatted into an associative array containing the keys as column names. Multiple rows can be inserted using the same method as well.
To insert a single row use the command below,
$data = ['name' => 'John', 'age' => 21]
$builder->insert($data);
To insert multiple rows, the array can be formatted as shown below,
$data = [ ['name' => 'John', 'age' => 21], ['name' => 'Jane', 'age' => 22] ];
$builder->insert($data);
The order of keys within the associative array does not matter as they are sorted automatically.
The method returns the number of rows affected (on in this case inserted).
Update
To update rows in the database you can use the update method. The method optionally accepts an associative array containing column names and values or you can use the set method to set the column names and values.
$builder->where('age', 24)
->set('age', 25)
->update();
or
$builder->where('age', 24)
->update(['age' => 25]);
Both the examples above produce the same result. The update() method returns the number of rows affected by the query.
Join
The join function can be used to join as many tables as you need on as many conditions as you need. Below is an example join that joins based off on a single condition. The method accepts five arguments, table name, condition 1, operator, condition 2 and the join type (defaults to Inner Join).
$builder->join('contact', 'user.id', '=', 'contact.user_id')
Multiple on conditions can also be made by passing a closure as the second argument instead of a condition.
NOTE: The Closure only has access to methods from JoinBuilder class which contains the on and orOn methods. to filter records use where function outside the clousre.
$builder->join('contact', function($q) {
$q->on('user.id', '=', 'contact.user_id')
->on('user.email', '=', 'contact.email')
});
Similar to where statements, the on conditions can be nested to make conditions by just passing a closure to the first argument of the on function.
Alias for table names can also be used.
$builder->join('contact as c', 'user.id', '=', 'c.user_id')
Where
The where function can be used to filter records. It accepts four arguments, the column name, operator, value and the boolean condition.
$builder->where('email', '=', '[email protected]', 'AND')
The where function can also be shortened by just passing the column name and value.
$builder->where('email', '[email protected]')
orWhere method can also be used to filter records.
$builder->orWhere('name', '=', 'John', 'AND')
$builder->orWhere('name', 'John')
Multiple where conditions can be passed in a single call by passing in an array to the first argument,
$builder->where([ ['first_name', '=', 'John', 'AND'], ['last_name', '=', 'Doe', 'AND'] ])
The boolean condition is used for the current where condition, so the argument is always ignored for the first where binding.
The or where method
Where Null
The whereNull method allow filtering fields based on null values. It accepts three arguments, the column name, boolean condition and a bool value which can be set to true filter records which are not null.
whereNull($column, $boolean = 'AND', $not = false)
The whereNotNull method can be accessed for selecting records which are not null as well,
whereNotNull($column, $boolean = 'AND')
Where Between
The whereBetween method can be accessed to filter records which are between a specified range. The method accepts four arguments, the column name, an array of size 2 containing the values, the boolean condition, and a bool value which can be set true to filter records that are not in between the given values.
whereBetween($column, array $value, $boolean = 'AND', $not = false)
The whereNotBetween method can be used to filter records which are not between the specified range.
whereNotBetween($column, array $value = null, $boolean = 'AND')
Where In
The whereIn method accepts four arguments, the column name, an array of values, the boolean condition and finally a bool value which can be set to true for filtering whereNotIn values.
whereIn($column, $values, $boolean = 'AND', $not = false)
The whereNotIn function can be used to filter records which are not in the specified values.
whereNotIn($column, $values, $boolean = 'AND')
Where Raw
The whereRaw method allows you to provide a raw statement that can be directly executed on the server. This can be used to run queries that are not directly supported by ezDB Builder.
whereRaw accepts 3 parameters.
/**
* Execute raw where statements.
*
* @param string|Raw $raw The raw statement. Can be a string or can be an instance of Raw
* @param array $values Any values that needs to be binded.
* @param string $boolean The joining condition for the where statement.
* @return $this
* @throws QueryException
*/
public function whereRaw($raw, $values = [], $boolean = 'AND')
Nested Where
ezDB also supports nested where conditions so it allows you build queries such as 1 or (2 and 3) and (1 and 2) or (2 and (3 or 4)). There is no limitation on the number of levels you can nest.
CAUTION: The Closure only supports where statement even though it has access to all the methods of the Builder class. If you include any join or limit functions inside the closure, it gets ignored.
1 and (2 or 3)
$builder->where('first_name', 'John')
->where(function ($q) {
$q->where('last_name', 'Doe')
->orWhere('last_name', 'Snow');
});
Order By
The order by function can be used to order results. It accepts two arguments, the column name and the direction (ASC or DESC).
orderBy($column, $direction = 'asc')
Limit and Offset
The limit function can be called to set the limit and offset. The function accepts two arguments, limit and optionally the offset.
limit($limit, $offset = null)
The offset can alternatively be set using the offset function. The offset only works if a limit is also specified.
offset($offset)