Skip to main content
Version: 5.x

Building SQL queries with the Query Builder

Introduction​

Bow provides an API for building queries. The table method lets you build an SQL query based on the table name and returns an instance of Bow\Database\QueryBuilder::class.

use Bow\Database\Database;

$builder = Database::table('users');
// => Instance \Bow\Database\QueryBuilder::class

You can also use the app_db_table helper:

$builder = app_db_table('users');

On Bow's QueryBuilder instance there are several methods that let you build an SQL query. For example, the toSql method, which lets you display the query you built.

$builder->toSql();
// select * from `users`

Retrieving information​

To retrieve information with the builder, you should use the get method, which returns a collection; first, which returns null or a stdclass object; and last, which behaves like first except that it returns the last element of the query execution result instead.

Example with get​

$builder = Database::table('users');

$users = $builder->get();

foreach ($users as $user) {
  echo $user->name;
}
Column projection

You can pass an array to get that is a list of the columns to project, like this: $builder->get(['name']).

Example with first​

$user = app_db_table('users')->first();

// Empty
is_null($user)
// Ok
echo $user->name;

Example with last​

$user = $builder->last();

Adding restrictions​

Simple restriction​

With the builder, you can add simple restrictions when building the SQL query using the where method.

$users = app_db_table('users')->where('id', 1)->get();

$users = app_db_table('users')->where('id', '!=', 1)->get();

The OR clause​

You can chain restrictions by adding an or to your query. The orWhere method lets you do this:

$users = app_db_table('users')->where('id', 1)->orWhere('name', 'Papac')->get();

You can see the result of building the query with the toSql method.

$sql = app_db_table('users')->where('id', 1)->orWhere('id', 3)->toSql();
// => select * from users where id = ? or id = ?
Placeholders

toSql() returns the query with ? placeholders, not the literal values — the query is executed as a prepared statement with parameter binding, which protects against SQL injection.

whereRaw / orWhereRaw​

For conditions that the builder does not express natively, use the raw variants with parameter binding:

$users = app_db_table('users')
  ->whereRaw('LOWER(name) = ?', ['papac'])
  ->orWhereRaw('created_at >= NOW() - INTERVAL 7 DAY')
  ->get();

Additional clauses​

whereNull / whereNotNull​

The whereNull method checks that the value of the given column is NULL:

$users = app_db_table('users')->whereNull('name')->get();

The whereNotNull method checks that the column's value is not NULL:

$users = app_db_table('users')->whereNotNull('age')->get();

whereIn / whereNotIn​

The whereIn method checks that a given column's value is contained in the given array:

$users = app_db_table('users')->whereIn('age', [27, 30])->get();

The whereNotIn method checks that the given column's value is not contained in the given array:

$users = app_db_table('users')->whereNotIn('age', [27, 30])->get();

whereBetween / whereNotBetween​

The whereBetween method checks that a column's value is between two values:

$users = app_db_table('users')->whereBetween('votes', [1, 100])->get();

The whereNotBetween method checks that a column's value falls outside two values:

$users = app_db_table('users')->whereNotBetween('votes', [1, 100])->get();

Joins​

// INNER JOIN
$rows = app_db_table('orders')
  ->join('users', 'orders.user_id', '=', 'users.id')
  ->get();

// LEFT JOIN
$rows = app_db_table('orders')
  ->leftJoin('users', 'orders.user_id', '=', 'users.id')
  ->get();

// RIGHT JOIN
$rows = app_db_table('orders')
  ->rightJoin('users', 'orders.user_id', '=', 'users.id')
  ->get();

For joins with multiple conditions, chain andOn / orOn after a join:

$rows = app_db_table('orders')
  ->join('users', 'orders.user_id', '=', 'users.id')
  ->andOn('orders.team_id', '=', 'users.team_id')
  ->get();

Ordering, grouping, and limiting​

orderBy​

The orderBy method lets you sort the query result based on a given column. The first argument to the orderBy method should be the column you want to sort by, while the second argument controls the sort direction and can be asc or desc:

$users = app_db_table('users')->orderBy('name', 'desc')->get();

groupBy and having​

The groupBy and having methods can be used to group the query results. The signature of the having method is similar to that of the where method:

$users = app_db_table('orders')
  ->groupBy('price')
  ->having('price', '>', 100)
  ->get();

jump and take​

To limit the number of results returned by the query or to skip a given number of results in the query, you can use the jump (to skip) and take (to return a number) methods:

use Bow\Database\Database;

$users = Database::table('users')->jump(10)->take(5)->get();

Aggregates​

Aggregation methods

The query builder also provides a variety of aggregation methods such as count, max, min, avg, and sum.

You can call any of these methods after building your query.

$users = app_db_table('users')->count();

$price = app_db_table('orders')->max('price');

$avg = app_db_table('orders')->avg('price');

Checking whether records exist​

Instead of using the count method to determine whether any records match your query's constraints, you can use the exists method:

$exists = app_db_table('users')->where('id', 1)->exists();

Specifying a select clause​

Of course, you may not always want to select all the columns of a database table. Using the select method, you can specify a custom select clause for the query:

$price = app_db_table('orders')->select('price')->get();
// Or select multiple columns
$price = app_db_table('orders')->select(['id', 'price'])->get();

Inserting information​

Inserting data

The query builder provides an insert method to insert records into the database table.

The insert method accepts an array of column names and values:

app_db_table('users')->insert(
  ['email' => 'exemple@gmail.com', 'age' => 27]
);

You can insert multiple records in a single call by passing an array of arrays:

app_db_table('users')->insert([
  ['email' => 'a@example.com', 'age' => 27],
  ['email' => 'b@example.com', 'age' => 31],
  ['email' => 'c@example.com', 'age' => 24],
]);

Retrieving the last inserted ID​

To insert a record and retrieve the auto-incremented value of the new record in a single operation, use insertAndGetLastId:

$id = app_db_table('users')->insertAndGetLastId(
  ['email' => 'exemple@gmail.com', 'age' => 27]
);

Updating​

Of course, in addition to inserting records into the database, the query builder can also update existing records using the update method. The update method, like the insert method, accepts an array of column-and-value pairs containing the columns to update. You can constrain the update query using where clauses:

app_db_table('users')->where('id', 1)->update(
  ['email' => 'exemple@gmail.com', 'age' => 27]
);

Deleting a record​

Deleting data

The query builder can also be used to delete records from the table via the delete method.

You can constrain delete statements by adding where clauses before calling the delete method:

app_db_table('users')->delete();

app_db_table('users')->where('age', '>', 27)->delete();

If you want to empty the entire table, which will delete all rows and reset the auto-increment ID to zero, you can use the truncate method:

app_db_table('pets')->truncate();

Increment / decrement​

The increment and decrement methods atomically modify the value of a numeric column without loading the row first. The step ($step) is optional and defaults to 1:

// votes = votes + 1
app_db_table('users')->where('id', 1)->increment('votes');

// stock = stock - 3
app_db_table('products')->where('id', 42)->decrement('stock', 3);

DISTINCT​

To return only the distinct values of a column:

$categories = app_db_table('products')->distinct('category')->get();

Row locking​

Within a transaction, you can add a lock to prevent concurrent modifications:

use Bow\Database\Database;

Database::transaction(function () {
  // Exclusive lock (SELECT ... FOR UPDATE)
  $user = app_db_table('users')->where('id', 1)->lockForUpdate()->first();

  // Shared lock (SELECT ... LOCK IN SHARE MODE)
  $order = app_db_table('orders')->where('id', 42)->sharedLock()->first();

  // ... operations on the locked rows
});

Is something missing?

If you run into problems with the documentation or have suggestions to improve the documentation or the project in general, please open an issue for us, or send a tweet mentioning the Twitter account @bowframework or directly on github.