This version is outdated. You should upgrade your project to Mako 9.1 or Mako 10.0!

Getting started

Routing and controllers

Command line

Databases (SQL)

Databases (NoSQL)

Redis

Security

Packages

Packages

Learn more

Official packages

REPL
Toolbar

Query builder

Getting a query builder instance
Fetching data
Inserting data
Updating data
Deleting data
JSON data
Aggregates
WHERE clauses
WHERE BETWEEN clauses
WHERE IN clauses
WHERE IS NULL clauses
WHERE EXISTS clauses
JOIN clauses
GROUP BY clauses
HAVING clauses
ORDER BY clauses
LIMIT and OFFSET clauses
Set operations
Row-level locking

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. However you have to make sure that you don't create SQL injection vectors if you're using using raw SQL in your query builder queries!

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 = $connection->table('foobar');

Fetching data

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

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

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();

Note that the all method returns a result set. So you'll need to use the isEmpty method to check if it's empty.

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(function($query)
	{
		$query->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(function($query)
		{
			$query->table('persons')->select([new Raw('AVG(age)']));
		}, 'average_age')
	]
)->all();

If you need to process a large dataset and don't want to put the entire result set in memory then you can use the yield method. It returns a generator that lets you iterate over the result set.

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

foreach($persons as $person)
{
	// Only a single row is kept in memory at a time
}

In addition to using the yield method to process large amounts of data you can also use the batch method. 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
});

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');

It is also possible to fetch an array containing the values of a single column using the columns method.

 $emails = $query->table('persons')->select(['email'])->columns();

 // You can also use the following syntax

 $emails = $query->table('persons')->columns('email');

The pairs method allows you to fetch an array where the first column is used as the array keys and the second is used as the array values.

$pairs = $query->table->('users')->pairs('id', 'email');

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();

JSON data

The query builder features a unified syntax for querying JSON data and it currently supports MySQL, Oracle, PostgreSQL, SQLServer and SQLite.

$foos = $query->table('articles')->select(['meta->foo as foo'])->where('meta->bar', '=', 1)->all();

You can also use the unified syntax to update JSON values. This feature currently supports MySQL, PostgreSQL, SQLServer and SQLite.

$query->table('articles')->update(['meta->bar' => json_encode(0)]);

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();

// Distinct counting

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

$count = $query->table('persons')->countDistinct(['age', 'height']);

// 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);
})
->isNotNull('email')
->all();

The wereRaw and orWhereRaw methods allow you to set a "raw" parameter value or to write an entire sql expression.

// SELECT * FROM `persons` WHERE `age` > AVG(`age`)

$persons = $query->table('persons')->whereRaw('age', '>', 'AVG(`age`)')->all();

// SELECT * FROM `persons` WHERE MATCH(`name`) AGAINST ('foobar' IN BOOLEAN MODE)

$persons = $query->table('persons')->whereRaw('MATCH(`name`) AGAINST (? IN BOOLEAN MODE)', ['foobar']);

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

isNull(), orIsNull(), isNotNull(), orIsNotNull()

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

$persons = $query->table('persons')->isNull('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();

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(), havingRaw(), orHaving(), orHavingRaw()

// 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')
->havingRaw('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();

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 the paginate method to limit your results.

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

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

Set operations

union(), unionAll(), intersect(), intersectAll(), except(), exceptAll()

You can also combine the results of multiple queries into a single result set using set operations.

// SELECT * FROM `sales2015` UNION ALL SELECT * FROM `sales2016`

$sales2015 = $connection->builder()->table('sales2015');

$combinedSales = $connection->builder()->unionAll($sales2015)->table('sales2016')->all();

You can also use the closure syntax when adding set operations to your queries.

// SELECT * FROM `sales2015` UNION ALL SELECT * FROM `sales2016`

$combinedSales = $query->unionAll(function($query)
{
	$query->table('sales2015');
})
->table('sales2016')->all();

Row-level locking

lock(), sharedLock()

The lock() method can be used to enable row-level locking during database transactions.

// SELECT * FROM `persons` WHERE `age` =  30 FOR UPDATE

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

It will use an exclusive lock by default but you can enable shared locking by passing false to the lock() method or by using the sharedLock() method.

// SELECT * FROM `persons` WHERE `age` =  30 LOCK IN SHARE MODE

$persons = $query->table('persons')->where('age', '=', 30)->lock(false)->all();

It is also possible to provide a custom locking clause.

// SELECT * FROM `persons` WHERE `age` =  30 CUSTOM LOCK

$persons = $query->table('persons')->where('age', '=', 30)->lock('CUSTOM LOCK')->all();

Here's an overview of the locking clauses generated for the different RDBMSes that support row-level locking.

RDBMS	Exclusive lock	Shared lock
DB2	FOR UPDATE WITH RS	FOR READ ONLY WITH RS
Firebird	FOR UPDATE WITH LOCK	WITH LOCK
MySQL	FOR UPDATE	LOCK IN SHARE MODE
NuoDB	FOR UPDATE	LOCK IN SHARE MODE
Oracle	FOR UPDATE	FOR UPDATE
PostgreSQL	FOR UPDATE	FOR SHARE
SQLServer	WITH (UPDLOCK, ROWLOCK)	WITH (HOLDLOCK, ROWLOCK)

Row-level locking will gracefully degrade for any RDBMS that doesn't support the feature.