Dreamfork
Database Query Builder
- Introduction
- Configuration
- Running SQL Queries
- Running Database Queries
- Select Statements
- Raw Expressions
- Joins
- Unions
- Basic Where Clauses
- Ordering, Grouping, Limit & Offset
- Insert Statements
- Update Statements
- Delete Statements
- Debugging
Introduction
Dreamfork currently supports MySQL as its primary database system. Dreamfork simplifies and secures database interactions by supporting raw SQL statements, query builders, and ORM.
Configuration
Dreamfork's database settings are located in /config/database.php. Default connection data, like host and username, are in the .env file. Adjust them there to match your database needs. For more tweaks, refer to /config/database.php. Dreamfork simplifies configuration for efficient, tailored performance.
Running SQL Queries
After configuring your database connection, you can execute queries using the DB facade. This facade offers dedicated methods for various query types, including select, insert, update and delete.
Running A Select Query
To run a basic SELECT query, you may use the select method on the DB facade:
use Framework\Support\Facades\DB;
$users = DB::select('select * from users where id = ?', [1]);
In the select method, the first argument represents the SQL query, and the second argument includes any parameter bindings essential for the query. Usually, these bindings correspond to the values of the where clause constraints. This approach of parameter binding ensures security against SQL injection.
The select method consistently returns an array of results. Each result in the array is represented by a PHP stdClass object, depicting a record from the database:
Using Named Bindings
Rather than using ?, you have the option to execute a query using named bindings for your parameter values:
$users = DB::select('select * from users where id = :id', ['id' => 1]);
Running An Insert Statement
To execute an insert statement, utilize the insert method on the DB facade. Similar to the select method, the insert method takes the SQL query as its first argument and bindings as its second argument:
DB::insert('insert into users (name, email) values (?, ?)', ['John', 'john@mail.com']);
Running An Update Statement
For updating existing records in the database, employ the update method. This method not only updates records but also returns the count of rows affected by the statement:
DB::update('update users set active = 1 where id = ?', [1]);
Running A Delete Statement
To delete records from the database, utilize the delete method. Similar to the update method, the delete method returns the count of rows affected by the operation:
DB::delete('delete from users where id = ?', [1]);
Running Database Queries
Retrieving All Rows From A Table
Initiate a query by utilizing the table method offered by the DB facade. The table method yields a fluent query builder instance specific to the provided table. This enables you to sequentially apply additional constraints to the query. Ultimately, retrieve the query results using the get method:
$users = DB::table('users')->get();
The get method returns a Collection instance from the Framework\Support\Collections namespace, containing the query results. Each result in the collection is represented as a PHP stdClass object. Access each column's value by treating the column as a property of the object:
$users = DB::table('users')->get();
foreach ($users as $user) {
echo $user->name;
}
Retrieving A Single Row / Column From A Table
To fetch a single row from a database table, employ the DB facade's first method. This method returns a single stdClass object:
$user = DB::table('users')->where('id', 1)->first();
return $user->name;
If you only require a specific value from a record, utilize the value method. This method directly returns the value of the specified column:
$name = DB::table('users')->where('id', 1)->value('name');
For retrieving a single row based on the id column value, use the find method:
$user = DB::table('users')->find(1);
Aggregates
The query builder in Dreamfork offers various methods for retrieving aggregate values such as count, max, min, avg and sum. You can invoke any of these methods after constructing your query:
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
Combine these methods with other clauses to precisely define how your aggregate value is calculated:
$users = DB::table('users')->where('active', 1)->count();
Determining If Records Exist
To determine if any records exist that match your query's constraints, you can use the exists and doesntExist methods:
if ($users = DB::table('users')->where('id', 1)->exists()) {
// ...
}
if ($users = DB::table('users')->where('id', 1)->doesntExist()) {
// ...
}
Select Statements
In Dreamfork, you can customize the "select" clause for a query using the select method, allowing you to choose specific columns from a database table:
$users = DB::table('users')->select('name', 'email as user_email')->get();
To obtain distinct results, use the distinct method:
$users = DB::table('users')->distinct()->get();
If you already have a query builder instance and want to add a column to its existing select clause, use the addSelect method:
$query = DB::table('users')->select('name');
$users = $query->addSelect('email')->get();
Raw Expressions
When you need to insert an arbitrary string into a query, you can create a raw string expression using the raw method provided by the DB facade:
$users = DB::table('users')->select(DB::raw('count(*) as user_count, active'))->groupBy('active')->get();
Be cautious when using raw statements, as they are injected into the query as strings. To avoid creating SQL injection vulnerabilities, exercise extreme care.
Raw Methods
In Dreamfork, you have alternative methods to insert raw expressions into different parts of your query. Keep in mind that while these methods provide flexibility, Dreamfork cannot guarantee protection against SQL injection vulnerabilities.
selectRaw
The selectRaw method can be used in place of select(DB::raw(/* ... */)). This method accepts an optional array of bindings as its second argument:
$orders = DB::table('orders')->selectRaw('price * ? as price_with_tax', [1.23])->get();
whereRaw / orWhereRaw
The whereRaw and orWhereRaw method can be used to inject a raw "where" clause into your query. These methods accept an optional array of bindings as their second argument:
$orders = DB::table('orders')->whereRaw('price > ?', [150])->get();
havingRaw / orHavingRaw
The havingRaw and orHavingRaw method may be used to provide a raw string as the value of the "having" clause. These methods accept an optional array of bindings as their second argument:
$orders = DB::table('orders')->select('client_id', DB::raw('SUM(price) as total_sales'))
->groupBy('client_id')->havingRaw('SUM(price) > ?', [1000])->get();
orderByRaw
The orderByRaw method may be used to provide a raw string as the value of the "order by" clause:
$orders = DB::table('orders')->orderByRaw('updated_at - created_at DESC')->get();
groupbyRaw
The groupbyRaw method may be used to provide a raw string as the value of the group by clause:
$orders = DB::table('orders')->select('city', 'state')->groupByRaw('city, state')->get();
Joins
Inner Join Clause
The query builder allows you to incorporate join clauses into your queries. For a basic "inner join," utilize the join method on a query builder instance. The first argument for the join method is the name of the table you want to join, followed by the column constraints for the join. You can join multiple tables within a single query:
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
Left Join / Right Join Clause
For a "left join" or "right join" instead of an "inner join," leverage the leftJoin or rightJoin methods, maintaining the same signature as the join method:
$users = DB::table('users')->leftJoin('posts', 'users.id', '=', 'posts.user_id')->get();
$users = DB::table('users')->rightJoin('posts', 'users.id', '=', 'posts.user_id')->get();
Unions
The query builder also offers a convenient method to "union" two or more queries together. To illustrate, you can create an initial query and use the union method to combine it with additional queries:
$first = DB::table('users')->whereNull('first_name');
$users = DB::table('users')->whereNull('last_name')->union($first)->get();
In addition to the union method, the query builder provides a unionAll method. When using unionAll, duplicate results from combined queries are not removed. The unionAll method has the same method signature as the union method.
Basic Where Clauses
Where Clauses
In Dreamfork's query builder, use the where method to add "where" clauses to your query. The most basic call to the where method involves three arguments: the column name, an operator, and the value for comparison.
For instance, the following query retrieves users where the id column is greater than 5, and the age column is equal 20:
$users = DB::table('users')->where('id', '>', 5)->where('age', '=', 20)->get();
For convenience, if you want to verify that a column is equal to a given value, pass the value as the second argument, and Dreamfork will assume the use of the = operator:
$users = DB::table('users')->where('age', 20)->get();
You can use any operator supported by your database system:
$users = DB::table('users')->where('name', 'like', 'John')->where('age', '<>', 20)->get();
PDO does not support binding column names. Therefore, you should never allow user input to dictate the column names referenced by your queries, including "order by" columns.
Or Where Clauses
When chaining calls to the query builder's where method, the "where" clauses are joined using the AND operator. However, you can utilize the orWhere method to append a clause to the query using the OR operator. The orWhere method accepts the same arguments as the where method:
$users = DB::table('users')->where('name', 'like', 'John')->orWhere('name', 'like', 'Tom')->get();
Where Not Clauses
The whereNot and orWhereNot methods can be used to negate a specific constraint.
$users = DB::table('users')->whereNot('id', 1)->get();
Where In Clauses
The whereIn and whereNotIn methods check whether a given column's value is contained or not within the provided array:
$users = DB::table('users')->whereIn('id', [1,2,3])->get();
$users = DB::table('users')->whereNotIn('id', [1,2,3])->get();
Where Null Clauses
The whereNull and whereNotNull methods validate whether the value of the specified column is NULL or NOT NULL:
$users = DB::table('users')->whereNull('updated_at')->get();
$users = DB::table('users')->whereNotIn('email')->get();
Ordering, Grouping, Limit & Offset
Ordering
The orderBy method enables you to sort the query results based on a specified column. The first argument for the orderBy method should be the column for sorting, and the second argument defines the sort direction, which can be either asc or desc:
$users = DB::table('users')->orderBy('name', 'desc)->get();
For sorting by multiple columns, you can use the orderBy method multiple times:
The Latest & Oldest Methods
The latest and oldest methods simplify ordering results by date. By default, the result is ordered by the table's created_at column. Alternatively, you can specify the column name for sorting:
$users = DB::table('users')->latest()->first();
$users = DB::table('users')->oldest()->get();
Random Ordering
For random ordering, use the inRandomOrder method. For instance, to retrieve a random user:
$users = DB::table('users')->inRandomOrder()->get();
Grouping
As anticipated, the groupBy and having methods are employed to group the query results. The having method shares a signature similar to that of the where method:
$users = DB::table('users')->groupBy('role_id')->having('role_id', '>', 1)->get();
You may pass multiple arguments to the groupBy method to group by multiple columns:
$users = DB::table('users')->groupBy('first_name', 'status')->having('role_id', '>', 1)->get();
Limit & Offset
You may use the limit and offset methods to limit the number of results returned from the query or to skip a given number of results in the query:
$users = DB::table('users')->offset(10)->limit(5)->get();
Insert Statements
The query builder offers an insert method for inserting records into a database table. The insert method takes an array of column names and corresponding values:
DB::table('users')->insert(['name' => 'Terry', 'email' => 'terry@example.com']);
Retrieving the Last Inserted ID
You can utilize the getLastInsertId method on the DB facade to retrieve the ID of the last record inserted into the database.
DB::table('users')->insert(['name' => 'Terry', 'email' => 'terry@example.com']);
$id = DB::getLastInsertId();
Update Statements
In addition to inserting records into the database, the query builder can update existing records using the update method. Similar to the insert method, the update method takes an array of column and value pairs, specifying the columns to be updated. The update method returns the number of affected rows. You can apply constraints to the update query using where clauses:
$affected = DB::table('users')->where('id', 1)->update(['age' => 21]);
Delete Statements
The delete method in the query builder is employed to delete records from the table. This method returns the number of affected rows. You can impose constraints on delete statements by adding "where" clauses before invoking the delete method:
$deleted = DB::table('users')->where('age', '<', 18)->delete();
Debugging
If you find yourself uncertain about how your SQL statement is being executed or if it has been composed correctly, you can utilize the debug method for insights. This method will display information about the prepared statement, its bindings, and the resulting raw statement after binding:
DB::table('users')->select('name')->where('id','<>','1')->latest()->debug();