This version is outdated. You should upgrade your project to Mako 9.1 or Mako 10.0!
master
10.0 9.1
show older versions 9.0 8.1 8.0 7.3 7.2 7.1 7.0 6.3 6.2 6.1 6.0 5.7 5.6 5.5 5.4 5.3 5.2 5.1 5.0 4.5 3.6
Databases (SQL)

Query builder

The query builder allows you to programmatically build SQL queries.

The query builder currently supports the following dialects:

  • DB2
  • Firebird
  • MariaDB
  • MySQL
  • NuoDB
  • Oracle
  • PostgreSQL
  • SQLite
  • SQLServer

All queries executed by the query builder use prepared statements, thus mitigating the risk of SQL injections.

Getting a query builder instance

You can create a query builder instance using the Connection::builder() method.

$query = $connection->builder();

You can also skip the call to the Connection::builder() method using the Connection::table() method.

$query = $conncetion->table('foobar');

Fetching data

Fetching all rows is done using the all method.

$persons = $query->table('persons')->all();

// You can also specify which columns you want to include in the result set

$persons = $query->table('persons')->select(['name', 'email'])->all();

// To make a distinct selection use the distinct method

$persons = $query->table('persons')->select(['name', 'email'])->distinct()->all();

Selecting from the results of a subquery is also possible.

$persons = $query->table(function($query)
{
	$query->table('persons')->select(['name'])->distinct();
})
->where('name', '!=', 'John Doe')
->all();

You can also use the Subquery class instead of a closure if you need a specific table alias.

$persons = $query->table
(
	new Subquery
	(
		$connection->builder()->table('persons')->select(['name'])->distinct(), 'distinct_names'
	)
)
->where('name', '!=', 'John Doe')
->all();

Advanced column selections can also be made using raw SQL and subqueries.

$persons = $query->table('persons')->select
(
	[
		'name',
		'email',
		new Raw("CASE gender WHEN 'm' THEN 'male' ELSE 'female' END AS gender"),
		new Subquery
		(
			$connection->builder()->table('persons')->select([new Raw('AVG(age)'])), 'average_age'
		)
	]
)->all();

Make sure not to create SQL injection vectors when using raw sql in your query builder queries!

If you only want to retrieve a single row you can use the first method.

$person = $query->table('persons')->where('id', '=', 1)->first();

Fetching the value of a single column is done using the column method.

$email = $query->table('persons')->select(['email'])->where('id', '=', 1)->column();

// You can also use the following syntax

$email = $query->table('persons')->where('id', '=', 1)->column('email');

If you need to process large ammounts of data then the batch method will help you limit the memory usage of your application. The default batch size is a 1000 records but you can override this using the optional second parameter.

You can also set the offset starting point and offset end point using the optional third and fourth parameters respectively. This is useful if you have parallel workers processing data.

$query->table('persons')->ascending('id')->batch(function($batch)
{
	// Process the batch here
});

Inserting data

Inserting data is done using the insert method.

$query->table('foobars')->insert(['field1' => 'foo', 'field2' => new DateTime()]);

You can also insert data using the insertAndGetId method. It will create the record and return the generated auto increment id.

$query->table('foobars')->insertAndGetId(['field1' => 'foo', 'field2' => new DateTime()]);

When working with PostgreSQL the insertAndGetId method assumes that the sequence follows the default naming convention (<table_name>_<primary_key_name>_seq) You can override the default primary key name (id) by using the optional second parameter.

Updating data

Updating data is done using the update method.

$query->table('foobars')
->where('id', '=', 10)
->update(['field1' => 'foo', 'field2' => 'bar', 'field3' => time()]);

There are also shortcuts for incrementing and decrementing column values:

$query->table('articles')->where('id', '=', 1)->increment('views');

$query->table('articles')->where('id', '=', 1)->increment('views', 10);

$query->table('shows')->where('id', '=', 1)->decrement('tickets')

$query->table('shows')->where('id', '=', 1)->decrement('tickets', 50);

Deleting data

Deleting data is done using the delete method.

$query->table('articles')->where('id', '=', 10)->delete();

Aggregates

The query builder also includes a few handy shortcuts to the most common aggregate functions:

// Counting

$count = $query->table('persons')->count();

$count = $query->table('persons')->where('age', '>', 25)->count();

// Average value

$height = $query->table('persons')->avg('height');

$height = $query->table('persons')->where('age', '>', 25)->avg('height');

// Largest value

$height = $query->table('persons')->max('height');

$height = $query->table('persons')->where('age', '>', 25)->max('height');

// Smallest value

$height = $query->table('persons')->min('height');

$height = $query->table('persons')->where('age', '>', 25)->min('height');

// Sum

$height = $query->table('persons')->sum('height');

$height = $query->table('persons')->where('age', '>', 25)->sum('height');

WHERE clauses

where(), whereRaw(), orWhere(), orWhereRaw()

// SELECT * FROM `persons` WHERE `age` > 25

$persons = $query->table('persons')->where('age', '>', 25)->all();

// SELECT * FROM `persons` WHERE `age` > 25 OR `age` < 20

$persons = $query->table('persons')->where('age', '>', 25)->orWhere('age', '<', 20)->all();

// SELECT * FROM `persons` WHERE (`age` > 25 AND `height` > 180) AND `email` IS NOT NULL

$persons = $query->table('persons')
->where(function($query)
{
	$query->where('age', '>', 25);
	$query->where('height', '>', 180);
})
->notNull('email')
->all();

Make sure not to create SQL injection vectors when using raw sql in your query builder queries!

WHERE BETWEEN clauses

between(), orBetween(), notBetween(), orNotBetween()

// SELECT * FROM `persons` WHERE `age` BETWEEN 20 AND 25

$persons = $query->table('persons')->between('age', 20, 25)->all();

// SELECT * FROM `persons` WHERE `age` BETWEEN 20 AND 25 OR `age` BETWEEN 30 AND 35

$persons = $query->table('persons')->between('age', 20, 25)->orBetween('age', 30, 35)->all();

WHERE IN clauses

in(), orIn(), notIn(), orNotIn()

// SELECT * FROM `persons` WHERE `id` IN (1, 2, 3, 4, 5)

$persons = $query->table('persons')->in('id', [1, 2, 3, 4, 5])->all();

// SELECT * FROM `persons` WHERE `id` IN (SELECT `id` FROM `persons` WHERE `id` != 1)

$persons = $query->table('persons')
->in('id', function($query)
{
	$query->table('persons')->select(['id'])->where('id', '!=', 1);
})
->all();

WHERE IS NULL clauses

null(), orNull(), notNull(), orNotNull()

// SELECT * FROM `persons` WHERE `address` IS NULL

$persons = $query->table('persons')->null('address')->all();

WHERE EXISTS clauses

exists(), orExists(), notExists(), orNotExists()

// SELECT * FROM `persons` WHERE EXISTS (SELECT * FROM `cars` WHERE `cars`.`person_id` = `persons`.`id`)

$persons = $query->table('persons')
->exists(function($query)
{
	$query->table('cars')->whereRaw('cars.person_id', '=', 'persons.id');
})
->all();

JOIN clauses

join(), joinRaw(), leftJoin(), leftJoinRaw()

// SELECT * FROM `persons` INNER JOIN `phones` ON `persons`.`id` = `phones`.`user_id`

$persons = $query->table('persons')->join('phones', 'persons.id', '=', 'phones.user_id')->all();

// SELECT * FROM `persons` AS `u` INNER JOIN `phones` AS `p` ON
// `u`.`id` = `p`.`user_id` OR `u`.`phone_number` = `p`.`number`

$persons = $query->table('persons as u')
->join('phones as p', function($join)
{
	$join->on('u.id', '=', 'p.user_id');
	$join->orOn('u.phone_number', '=', 'p.number');
})
->all();

Make sure not to create SQL injection vectors when using raw sql in your query builder queries!

GROUP BY clauses

groupBy()

// SELECT `customer`, `order_date`, SUM(`order_price`) as `sum` FROM `orders` GROUP BY `customer`

$customers = $query->table('orders')
->select(['customer', new Raw('SUM(price) as sum')])
->groupBy('customer')
->all();

// SELECT `customer`, `order_date`, SUM(`order_price`) as `sum` FROM `orders` GROUP BY `customer`, `order_date`

$customers = $query->table('orders')
->select(['customer', 'order_date', new Raw('SUM(price) as sum')])
->groupBy(['customer', 'order_date'])
->all();

HAVING clauses

having(), orHaving()

// SELECT `customer`, SUM(`price`) AS `sum FROM `orders` GROUP BY `customer` HAVING SUM(`price`) < 2000

$customers = $query->table('orders')
->select(['customer', new Raw('SUM(price) as sum')])
->groupBy('customer')
->having(new Raw('SUM(price)'), '<', 2000)
->all();

ORDER BY clauses

orderBy(), orderByRaw(), descending(), descendingRaw(), ascending(), ascendingRaw()

// SELECT * FROM `persons` ORDER BY `name` ASC

$persons = $query->table('persons')->orderBy('name', 'asc')->all();

// SELECT * FROM `persons` ORDER BY `name` ASC

$persons = $query->table('persons')->ascending('name')->all();

// SELECT * FROM `persons` ORDER BY `name` DESC

$persons = $query->table('persons')->descending('name')->all();

// SELECT * FROM `persons` ORDER BY `name` ASC, `age` DESC

$persons = $query->table('persons')->orderBy('name', 'asc')->orderBy('age', 'desc')->all();

// SELECT * FROM `persons` ORDER BY `name`, `age` ASC

$persons = $query->table('persons')->orderBy(['name', 'age'], 'asc')->all();

Make sure not to create SQL injection vectors when using raw sql in your query builder queries!

LIMIT and OFFSET clauses

limit(), offset(), paginate()

// SELECT * FROM `persons` LIMIT 10

$persons = $query->table('persons')->limit(10)->all();

// SELECT * FROM `persons` LIMIT 10 OFFSET 10

$persons = $query->table('persons')->limit(10)->offset(10)->all();

You can also use a pagination instance to limit your results.

// SELECT * FROM `persons` LIMIT 10 OFFSET 0

$persons = $query->table('persons')->paginate($pagination)->all();