Skip to main content
Version: 4.x

SQL Builder

Introduction​

Bow provides an API for building queries. The table method allows you to build a 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 table helper:

$builder = table('users');

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

$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.

Example with get​

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

$users = $builder->get();

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

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

Example with first​

$user = 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 to the SQL query construction using the where method.

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

$users = 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 = table('users')->where('id', 1)->orWhere('name', 'Papac')->get();

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

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

Additional clauses​

whereNull / whereNotNull​

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

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

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

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

whereIn / whereNotIn​

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

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

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

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

whereBetween / whereNotBetween​

The whereBetween method checks that the value of a column is between two values:

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

The whereNotBetween method checks that the value of a column lies outside two values:

$users = table('users')->whereNotBetween('votes', [1, 100])->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 may be asc or desc:

$users = 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 = 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:

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

Aggregates​

The query builder also provides a variety of aggregate methods such as count, max, min, avg, and sum. You can call any of these methods after building your query.

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

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

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

Determining if records exist​

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

$exists = 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 = table('orders')->select('price')->get();
// Or select multiple columns
$price = table('orders')->select(['id', 'price'])->get();

Inserting information​

The query builder also provides an insert method for inserting records into the database table. The insert method accepts an array of column names and values:

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

You can even insert several records into the table with a single call to insert by passing an array of arrays. Each array represents a row to be inserted into the table:

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 be updated. You can constrain the update query using where clauses:

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

Deleting a record​

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:

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

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:

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

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.