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:
ORBy using dependency, the same instance is used throughout.
<?php
use Qubus\Expressive\Database;
final class UserClassDb
{
public __construct(protected Database $dfdb)
{
}
}
SQL Injection
To protect against SQL injection, all raw queries should be prepared with the Database::prepare() method.
<?php
$dfdb->prepare(string $query, mixed ...$params): string;
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.
*
* @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->prefix}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.
*
* @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->basePrefix}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.
*
* @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)
*
* @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->prefix}product", Database::JSON_OBJECT);
foreach(json_decode($products) as $product) {
echo $product->product_title;
}
Fluent Query Builder
The fluent query feature of the query builder allows you to write simple queries without having to write SQL.
<?php
$posts = $dfdb->table($dfdb->prefix . 'content);
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" => "Devflow CMF",
"content_body" => "Devflow CMF is for developers",
"content_author" => '01JC6YZ4BHE06TN1MTSG9KZJ7Y',
"content_published" => $dfdb::now()
]);
Multiple Entries
<?php
$massPosts = $posts->insert([
[
"content_title" => "Domain Driven Frameworks",
"content" => "Domain Driven frameworks started...",
"content_author" => '01JC6YZ4BHE06TN1MTSG9KZJ7Y',
"content_published" => $dfdb::now()
],
[
"content_title" => "PHP 8.2",
"content" => "The new features in 8.2 are...",
"content_author" => '01JC6YZ4BHE06TN1MTSG9KZJ7Y',
"content_published" => $dfdb::now()
],
[
"content_title" => "Event Sourcing",
"content" => "Event sourcing is great for...",
"content_author" => '01JC6YZ4BHE06TN1MTSG9KZJ7Y',
"content_published" => $dfdb::now()
],
]);
Update
There are two ways to update a record, by using the active record pattern or by using the where clause.
<?php
$post->update([
"content_title" => "PHP 8.4"
]);
The above can also be written as:
<?php
$post->title = "PHP 8.4";
$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.4')->update();
For multiple entries using set(array $data) and where($key, $value):
<?php
$post->set([
"content_body" => "PHP 8.4 is the greatest because..."
])
->where("content_title", "PHP 8.4")
->update();
Save
save() is a shortcut to insert() or update().
Insert
<?php
$post = $orm->posts();
$post->title = "PHP 8.4";
$post->content = "PHP 8.4 is the greatest because...";
$post->save();
Update
<?php
$post = $posts->findOne('01JD9907ZFE2ARZ3RP9ATF0P1Y');
$post->title = "PHP 8.4 Update";
$post->save();
Delete
Single Entry
<?php
$post = $posts->reset()->findOne('01JD9907ZFE2ARZ3RP9ATF0P1Y');
$post->delete();
Multiple Entries
<?php
$posts->where("content_title", "PHP 8.4")->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::now();
$post->save();
}
Find
Find returns an ArrayIterator of rows found, otherwise it will return false.
<?php
$allPosts = $posts->where(condition: 'content_title', parameters: 'PHP 8.4')
->find();
foreach ($allPosts as $post) {
echo "{$post->content_body}";
// On a retrieved entry you can perform update and delete
$post->last_viewed = $dfdb::now();
$post->save();
}
Find also accepts a closure ( find(Closure $callback) ) to perform data manipulation.
<?php
$posts->where(condition: 'content_title', parameters: 'PHP 8.4');
$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 set up 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.4");
$posts->where(condition: "content_author > ?", parameters: '01JC6YZ4BHE06TN1MTSG9KZJ7Y');
$posts->where(condition: "content_title" in (?, ?, ?)", parameters: "PHP 8.4", "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'
);