Database
Devflow comes with a full-featured abstracted database class that supports both traditional structures and Query Builder patterns.
Instantiate
There are a few ways to quickly get a database object:
<?php
use App\Application\Devflow;
$dfdb = Devflow::inst()->dfdb();
To limit the number of connections, this is an alternative that uses the same instance throughout the application:
<?php
use function App\Shared\Helpers\dfdb;
$dfdb = dfdb();
The last way is to use dependency injection which also uses the same instance throughout the application.
<?php
use App\Infrastructure\Persistence\Database;
final class UserClassDb
{
public __construct(protected Database $dfdb)
{
}
}
Caching
Data from User, Content, and Product models are always cached, but caching is also available for raw queries. Caching for raw queries is disabled by default. You can set the cache variable to true in config/database.php to enable caching for raw queries.
SQL Injection
To protect against SQL injection, all raw queries should be prepared with the Database::prepare() method.
<?php
$dfdb->prepare(string $query, $params);
Raw Queries
There are 5 raw query methods that are used quite often in Devflow.
Select A Variable
The getVar method returns a single variable from the database. Returns null if no result is found.
<?php
/**
* Retrieve one variable from the database.
*
* Executes a SQL query and returns the value from the SQL result.
* If the SQL result contains more than one column and/or more than one
* row, this function returns the value in the column and row specified.
* If $query is null, this function returns the value in the specified
* column and row from the previous SQL result.
*
* @param string|null $query Optional. SQL query. Defaults to null, use the result from the previous query.
* @param int $x Optional. Column of value to return. Indexed from 0.
* @param int $y Optional. Row of value to return. Indexed from 0.
* @return string|int|null Database query result (as string), or null on failure
*/
$dfdb->getVar(string $query = null, int $x = 0, int $y = 0): string|null|int;
Example
Retrieve and display the number of products.
<?php
$productCount = $dfdb->getVar("SELECT COUNT(*) FROM $dfdb->product");
echo sprintf('You have %s products.', $productCount);
Select A Row
The getRow method returns an entire row from a query. This method can return the row as an object, an associative array, or as a numerically indexed array. Returns null if no result is found.
<?php
/**
* Retrieve one row from the database.
*
* Executes an SQL query and returns the row from the SQL result.
*
* @param string|null $query SQL query.
* @param string $output Optional. The required return type. One of OBJECT, ARRAY_A, or ARRAY_N, which
* correspond to an stdClass object, an associative array, or a numeric array,
* respectively. Default self::OBJECT.
* @param int $y Optional. Row to return. Indexed from 0.
* @return array|object|null Database query result in format specified by $output or null on failure.
* @throws PDOException
*/
$dfdb->getRow(string $query = null, string $output = Database::OBJECT, int $y = 0): object|array|null;
$query (string) The query to run.
$output (string) One of three pre-defined constants. Defaults to Database::OBJECT
Database::OBJECT– result will output as an object.Database::ARRAY_A- result will output as an associative array.Database::ARRAY_N- result will output as a numerically indexed array.
$y (int) - Row of value to return. Indexed from 0.
Examples
Get all the information about user with id 01JC6YZ4BHE06TN1MTSG9KZJ7Y.
<?php
$prepare = $dfdb->prepare(
'SELECT * FROM $dfdb->user WHERE user_id = ?',
['01JC6YZ4BHE06TN1MTSG9KZJ7Y']
);
$user = $dfdb->getRow($prepare);
echo $user->user_login;
$user = $dfdb->getRow($prepare, Database::ARRAY_A);
echo $user['user_login'];
$user = $dfdb->getRow($prepare, Database::ARRAY_N);
echo $user[1];
Select A Column
The getCol method outputs a one dimensional array. If more than one column is returned by the query, only the specified column will be returned. Returns an empty array if no result is found.
<?php
/**
* Retrieve one column from the database.
*
* Executes an SQL query and returns the column from the SQL result.
* If the SQL result contains more than one column, this function returns the column specified.
* If $query is null, this function returns the specified column from the previous SQL result.
*
* @param string|null $query Optional. SQL query. Defaults to previous query.
* @param int $x Optional. Column to return. Indexed from 0.
* @return array|null Database query result. Array indexed from 0 by SQL result row number.
*/
$dfdb->getCol(string $query = null, int $x = 0): ?array;
$query (string) The query to run.
$x (int) - Column of value to return. Indexed from 0.
Select Results
The getResults method returns the entire query result as an array. Each element of this array corresponds to one row of the query result and, like getRow, can be an object, an associative array, or a numbered array. If no matching rows are found, or if there is a database error, the return value will be an empty array.
<?php
/**
* Retrieve an entire SQL result set from the database (i.e. many rows)
*
* Executes an SQL query and returns the entire SQL result.
*
* @param string|null $query SQL query.
* @param string $output Optional. Any of Database::ARRAY_A | Database::ARRAY_N | Database::OBJECT | Database::JSON_OBJECT
* constants. With one of the first three, return an array of rows indexed from 0 by SQL
* result row number. Each row is an associative array (column => value, ...), a numerically
* indexed array (0 => value, ...), or an object. ( ->column = value ), respectively.
* With Database::JSON_OBJECT, return a JSON array string representative of each row requested.
* Duplicate keys are discarded.
* @return false|string|array Database query results
*/
$dfdb->getResults(string $query = null, string $output = Database::OBJECT): false|string|array;
$query (string) The query to run.
$output (string) One of four pre-defined constants. Defaults to Database::OBJECT
Database::OBJECT– result will output as an object.Database::JSON_OBJECT– result will output as a JSON array object.Database::ARRAY_A- result will output as an associative array.Database::ARRAY_N- result will output as a numerically indexed array.
Example
<?php
$products = $dfdb->getResults("SELECT * FROM $dfdb->product", Database::JSON_OBJECT);
foreach(json_encode($products) as $product) {
echo $product->product_title;
}
Fluent Query Builder
Instead of utilizing raw queries, you can utilize the query builder method qb(). The fluent query feature of the query builder allows you to write simple queries without having to write SQL.
<?php
$posts = $dfdb->qb();
Insert
When calling the insert(array $data) method, $data can be passed as one dimensional array to insert one new record or multiple arrays to insert multiple records.
Single Entry
<?php
$post = $posts->insert([
"content_title" => "CodefyPHP Framework",
"content_body" => "The CodefyPHP framework is for domain driven development.",
"content_author" => '01JC6YZ4BHE06TN1MTSG9KZJ7Y',
"content_published" => $dfdb->qb()->now()
]);
Multiple Entries
<?php
$massPosts = $posts->insert([
[
"content_title" => "Domain Driven Frameworks",
"content" => "Domain Driven frameworks started...",
"content_author" => '01JC6YZ4BHE06TN1MTSG9KZJ7Y',
"content_published" => $dfdb->qb()->now()
],
[
"content_title" => "PHP 8.2",
"content" => "The new features in 8.2 are...",
"content_author" => '01JC6YZ4BHE06TN1MTSG9KZJ7Y',
"content_published" => $dfdb->qb()->now()
],
[
"content_title" => "Event Sourcing",
"content" => "Event sourcing is great for...",
"content_author" => '01JC6YZ4BHE06TN1MTSG9KZJ7Y',
"content_published" => $dfdb->qb()->now()
],
]);
Update
There are two ways to update a record, by using the active record pattern or by or by using the where clause.
<?php
$post->update([
"content_title" => "PHP 8.3"
]);
The above can also be written as:
<?php
$post->title = "PHP 8.3";
$post->update();
You can use the alternative save() instead of update().
Or you can use the set(array $data) or set($key, $value):
<?php
$post->set('content_title','PHP 8.3')->update();
For multiple entries using set(array $data) and where($key, $value):
<?php
$post->set([
"content_body" => "PHP 8.3 is the greatest because..."
])
->where("content_title", "PHP 8.3")
->update();
Save
save() is a shortcut to insert() or update().
Insert
<?php
$post = $orm->posts();
$post->title = "PHP 8.3";
$post->content = "PHP 8.3 is the greatest because...";
$post->save();
Update
<?php
$post = $posts->findOne('01JD9907ZFE2ARZ3RP9ATF0P1Y');
$post->title = "PHP 8.3 Update";
$post->save();
Delete
Single Entry
<?php
$post = $posts->reset()->findOne('01JD9907ZFE2ARZ3RP9ATF0P1Y');
$post->delete();
Multiple Entries
<?php
$posts->where("content_title", "PHP 8.3")->delete();
Count
Count all the entries based on where() filter:
<?php
$allPosts = $posts->count();
$count = $posts->where($x, $y)->count();
Use count for a specific column name:
<?php
$count = $posts->where($x, $y)->count('columnName');
Max
Max based on where() filter:
<?php
$max = $posts->where($x, $y)->max('columnName');
Min
Min based on where() filter:
<?php
$min = $posts->where($x, $y)->min('columnName');
Sum
Sum based on where() filter:
<?php
$sum = $posts->where($x, $y)->sum('columnName');
Avg
Avg based on where() filter:
<?php
$avg = $posts->where($x, $y)->avg('columnName');
Aggregate
<?php
$agg = $posts->where($x, $y)->aggregate('GROUP_CONCAT columnName');
FindOne
Returns a single record is found otherwise it will return false.
<?php
$post = $posts->where(condition: 'content_id', parameters: '01JD98W11XE9JR66TP42HD0F7C')
->findOne();
You can achieve the same above by using only the primary key and dropping the where filter.
<?php
$post = $posts->findOne('01JD98W11XE9JR66TP42HD0F7C');
Retrieving the entry:
<?php
if ($post) {
echo " $post->content_title";
// On a retrieved entry you can perform update and delete
$post->last_viewed = $dfdb->qb()->now();
$post->save();
}
Find
Find returns and ArrayIterator of rows found, otherwise it will return false.
<?php
$allPosts = $posts->where(condition: 'content_title', parameters: 'PHP 8.3')
->find();
foreach ($allPosts as $post) {
echo "{$post->content_body}";
// On a retrieved entry you can perform update and delete
$post->last_viewed = $dfdb->qb()->now();
$post->save();
}
Find also accepts a closure ( find(Closure $callback) ) to perform data manipulation.
<?php
$posts->where(condition: 'content_title', parameters: 'PHP 8.3');
$results = $posts->find(function ($data) {
$newResults = [];
foreach ($data as $d) {
$d["content_title"] = "{$data["content_title"]}";
$newResults[] = $d;
}
return $newResults;
});
Select All
<?php
$posts->select()
Select Columns
<?php
$posts->select(columns: "content_title, content_body")
->select(columns: "last_viewed");
Where
Where can be used to setup the where clauses and they work with find(), findOne(), update(), and delete().
This is the same for the where aliases as well. Repetitive call to where and it’s aliases will append to each other
using the AND operator. Use or__() to mimic the OR operator.
Examples:
<?php
$posts->where(condition: "content_title", parameters: "PHP 8.3");
$posts->where(condition: "content_author > ?", parameters: '01JC6YZ4BHE06TN1MTSG9KZJ7Y');
$posts->where(condition: "content_title" in (?, ?, ?)", parameters: "PHP 8.3", "HTML 5", "ROR");
$posts->where(condition: "(field1, field2)", parameters: [[1, 2], [3, 4]]);
Not Equal To:
<?php
$posts->whereNot('content_id', '01JD98W11XE9JR66TP42HD0F7C');
Like:
<?php
$posts->whereLike('content_title', 'PH%');
Not Like:
<?php
$posts->whereNotLike('content_title', 'PH%');
Greater Than:
<?php
$posts->whereGt('content_published', 2014-09-14);
Greater Than Equal To:
<?php
$posts->whereGte('content_published', 2014-09-14);
Less Than:
<?php
$posts->whereLt('content_published', 2014-09-14);
Less Than Equal To:
<?php
$posts->whereLte('content_published', 2014-09-14);
Where In:
<?php
$posts->whereIn('content_author', ['01JC6YZ4BHE06TN1MTSG9KZJ7Y', '01JD98FD8NE6QSA0AYKH29JETA']);
Where Not In:
<?php
$posts->whereNotIn('content_author', ['01JC6YZ4BHE06TN1MTSG9KZJ7Y', '01JD98FD8NE6QSA0AYKH29JETA']);
Where Null:
<?php
$posts->whereNull('content_body');
Where Not Null:
<?php
$posts->whereNotNull('content_published');
Where with OR and AND
Use and__() / or__() chained to any where clauses:
<?php
$posts->where(condition: "content_author", parameters: '01JC6YZ4BHE06TN1MTSG9KZJ7Y')
->and__()
->whereGte(columnName: "content_published", value: '2014-09-14');
$posts->where(condition: "content_author", parameters: '01JC6YZ4BHE06TN1MTSG9KZJ7Y')
->or__()
->where(condition: "content_status", parameters: "draft");
Order, Group, Limit, Offset
<?php
$posts->orderBy(columnName: 'content_id', ordering: 'DESC');
$posts->groupBy(columnName: 'content_type');
$posts->limit(limit: 10);
$posts->offset(offset: 10);
Joins
<?php
/**
* Defaults to LEFT JOIN, for others, use INNER, RIGHT, etc. as the
* $joinOperator
*
* join( $tablename, $constraint, $tableAlias , $joinOperator )
*/
$posts->join(
tableName: $dfdb->contenttype,
constraint: "ct.content_type_slug = {$dfdb->content}.content_type",
tableAlias: 'ct'
);