This version is outdated. You should upgrade your project to Mako 11.0!
master
11.0 10.0
show older versions 9.1 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

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.

Fetching data

Fetching all rows is done using the all method.

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

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

$persons = $connection->table('persons')->all(array('name', 'email'));

// To make a distinct selection use the distinct method

$persons = $connection->table('persons')->distinct()->all(array('name', 'email'));

// Selecting from the results of a subquery is also possible

$persons = $connection->table
(
	Database::subquery($connection->table('persons')->distinct()->columns(array('name')), 'distinct_names')
)
->where('name', '!=', 'John Doe')
->all();

// You can also make advanced column selections with raw SQL and subqueries

$persons = $connection->table('persons')->all(array
(
	'name',
	'email',
	Database::raw("CASE gender WHEN 'm' THEN 'male' ELSE 'female' END AS gender"),
	Database::subquery($connection->table('persons')->columns(array(Database::raw('AVG(age)'))), 'average_age')
));

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

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

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

$person = $connection->table('persons')->where('id', '=', 1)->first(array('name', 'email'));

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

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

Inserting data

Inserting data is done using the insert method.

$connection->table('table')
->insert(array('field1' => 'foo', 'field2' => 'bar', 'field3' => time()));

Updating data

Updating data is done using the update method.

$connection->table('table')
->where('id', '=', 10)
->update(array('field1' => 'foo', 'field2' => 'bar', 'field3' => time()));

There are also shortcuts for incrementing and decrementing column values:

$connection->table('article')->increment('views');

$connection->table('article')->increment('views', 10);

$connection->table('show')->decrement('tickets')

$connection->table('show')->decrement('tickets', 50);

Deleting data

Deleting data is done using the delete method.

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

Aggregates

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

// Counting

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

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

// Average value

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

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

// Largest value

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

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

// Smallest value

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

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

// Sum

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

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

WHERE clauses

where(), orWhere()

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

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

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

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

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

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

WHERE BETWEEN clauses

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

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

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

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

$persons = $connection->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 = $connection->table('persons')
->in('id', array(1, 2, 3, 4, 5))
->all();

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

$persons = $connection->table('persons')
->in('id', Database::subquery($connection->table('persons')->where('id', '!=', 1)->columns(array('id'))))
->all();

WHERE IS NULL clauses

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

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

$persons = $connection->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 = $connection->table('persons')
->exists
(
	Database::subquery($connection->table('cars')->where('cars.person_id', '=', Database::raw('persons.id')))
)
->all();

JOIN clauses

join(), leftJoin()

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

$persons = $connection->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 = $connection->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 = $connection->table('orders')
->groupBy('customer')
->all(array('customer', Database::raw('SUM(price) as sum')));

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

$customers = $connection->table('orders')
->groupBy(array('customer', 'order_date'))
->all(array('customer', 'order_date', Database::raw('SUM(price) as sum')));

HAVING clauses

having(), orHaving()

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

$customers = $connection->table('orders')
->groupBy('customer')
->having(Database::raw('SUM(price)'), '<', 2000)
->all(array('customer', Database::raw('SUM(price) as sum')));

ORDER BY clauses

orderBy(), descending(), ascending()

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

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

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

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

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

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

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

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

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

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

LIMIT and OFFSET clauses

limit(), offset()

// SELECT * FROM `persons` LIMIT 10

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

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

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