Query Builder

VelvetCMS Core includes a lightweight, fluent Query Builder that provides a convenient interface for creating and running database queries. It uses PDO parameter binding to protect your application against SQL injection attacks.

Retrieving Results #

Retrieving All Rows #

You can use the table method on the DB facade to begin a query. The get method returns a Collection containing the results:

use VelvetCMS\Facades\DB;

$users = DB::table('users')->get();

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

Retrieving A Single Row #

If you just need to retrieve a single row from the database table, you may use the first method:

$user = DB::table('users')->where('name', 'John')->first();

echo $user->email;

Retrieving A Single Value #

To retrieve a single value from a record, use the value method:

$email = DB::table('users')->where('name', 'John')->value('email');

Selects #

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

$users = DB::table('users')
            ->select('name', 'email as user_email')
            ->get();

Where Clauses #

You may use the where method to add "where" clauses to the query. The most basic call to where requires three arguments: the name of the column, the operator, and the value to compare against.

$users = DB::table('users')
                ->where('votes', '=', 100)
                ->where('age', '>', 35)
                ->get();

If you want to verify that a column is equal to a given value, you may pass the value as the second argument:

$users = DB::table('users')->where('votes', 100)->get();

Or Statements #

You may chain where constraints together as well as add "or" clauses to the query. The orWhere method accepts the same arguments as the where method:

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

Ordering, Grouping, Limit & Offset #

Ordering #

The orderBy method allows you to sort the result of the query by a given column. The first argument should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc:

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

Limit & Offset #

To limit the number of results returned from the query, or to skip a given number of results in the query, you may use the skip and take methods:

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

Alternatively, you may use the limit and offset methods:

$users = DB::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

Inserts #

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:

DB::table('users')->insert([
    'email' => 'john@example.com',
    'votes' => 0
]);

Updates #

In addition to inserting records into the database, the query builder can also update existing records using the update method. The update method accepts an array of column and value pairs indicating the columns to be updated:

$affected = DB::table('users')
              ->where('id', 1)
              ->update(['votes' => 1]);

Deletes #

The query builder may also be used to delete records from the table via the delete method:

$deleted = DB::table('users')->delete();

$deleted = DB::table('users')->where('votes', '<', 100)->delete();

Caching Queries #

The Query Builder integrates seamlessly with the Cache system. You can cache the results of any query using the cache() method:

// Cache the result for 60 seconds
$users = DB::table('users')->cache(60)->get();

// Cache with a custom key
$users = DB::table('users')->cache(60, 'all_users')->get();