Slim 4 - CakePHP Query Builder
source link: https://odan.github.io/2019/12/03/slim4-cakephp-query-builder.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Daniel's Dev Blog
Developer, Trainer, Open Source Contributor
Blog About me DonateSlim 4 - CakePHP Query Builder
03 Dec 2019
Table of contents
Requirements
- PHP 7.1+
- MySQL 5.7+
- Composer
- A Slim 4 application
Introduction
You can use the CakePHP Query Builder to connect your Slim 4 application to a database.
Installation
To add the cakephp/database package to your application, run:
composer require cakephp/database
Configuration
Add the database settings into your configuration file, e.g config/settings.php
:
// Database settings
$settings['db'] = [
'driver' => \Cake\Database\Driver\Mysql::class,
'host' => 'localhost',
'database' => 'database',
'username' => 'root',
'password' => '',
'encoding' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
// Enable identifier quoting
'quoteIdentifiers' => true,
// Set to null to use MySQL servers timezone
'timezone' => null,
// PDO options
'flags' => [
// Turn off persistent connections
PDO::ATTR_PERSISTENT => false,
// Enable exceptions
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
// Emulate prepared statements
PDO::ATTR_EMULATE_PREPARES => true,
// Set default fetch mode to array
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
// Set character set
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
],
];
In your config/container.php
or wherever you add your container definitions:
<?php
use Cake\Database\Connection;
use Psr\Container\ContainerInterface;
use Slim\App;
use Slim\Factory\AppFactory;
return [
// ...
// Database connection
Connection::class => function (ContainerInterface $container) {
return new Connection($container->get('settings')['db']);
},
PDO::class => function (ContainerInterface $container) {
$db = $container->get(Connection::class);
$driver = $db->getDriver();
$driver->connect();
return $driver->getConnection();
},
];
Create a new PHP file: src/Repository/QueryFactory.php
and copy / paste this content:
<?php
namespace App\Repository;
use Cake\Database\Connection;
use Cake\Database\Query;
use UnexpectedValueException;
/**
* Factory.
*/
final class QueryFactory
{
/**
* @var Connection
*/
private $connection;
/**
* Constructor.
*
* @param Connection $connection The database connection
*/
public function __construct(Connection $connection)
{
$this->connection = $connection;
}
/**
* Create a new query.
*
* @return Query The query
*/
public function newQuery(): Query
{
return $this->connection->newQuery();
}
/**
* Create a new 'select' query for the given table.
*
* @param string $table The table name
*
* @throws UnexpectedValueException
*
* @return Query A new select query
*/
public function newSelect(string $table): Query
{
$query = $this->newQuery()->from($table);
if (!$query instanceof Query) {
throw new UnexpectedValueException('Failed to create query');
}
return $query;
}
/**
* Create an 'update' statement for the given table.
*
* @param string $table The table to update rows from
*
* @return Query The new update query
*/
public function newUpdate(string $table): Query
{
return $this->newQuery()->update($table);
}
/**
* Create an 'update' statement for the given table.
*
* @param string $table The table to update rows from
* @param array $data The values to be updated
*
* @return Query The new insert query
*/
public function newInsert(string $table, array $data): Query
{
return $this->newQuery()
->insert(array_keys($data))
->into($table)
->values($data);
}
/**
* Create a 'delete' query for the given table.
*
* @param string $table The table to delete from
*
* @return Query A new delete query
*/
public function newDelete(string $table): Query
{
return $this->newQuery()->delete($table);
}
}
Repository
You can inject the query factory instance into your repository like this:
<?php
namespace App\Domain\User\Repository;
use App\Domain\User\Data\UserData;
use App\Repository\QueryFactory;
use App\Repository\TableName;
use Cake\Database\StatementInterface;
/**
* Repository.
*/
class UserRepository
{
/**
* @var QueryFactory The query factory
*/
private $queryFactory;
/**
* The constructor.
*
* @param QueryFactory $queryFactory The query factory
*/
public function __construct(QueryFactory $queryFactory)
{
$this->queryFactory = $queryFactory;
}
// ...
}
Usage
Once the query factory instance has been injected, you may use it like so:
Select
Query all rows:
$query = $this->queryFactory->newSelect('users')->select('*');
$rows = $query->execute()->fetchAll('assoc');
foreach ($rows as $row) {
print_r($row);
}
Query the table with where:
$query = $this->queryFactory->newSelect('users');
$query->select(['id', 'username']);
$select->andWhere(['id' => 1]);
$rows = $query->execute()->fetchAll('assoc');
foreach ($rows as $row) {
print_r($row);
}
Query the table by id:
$query = $this->queryFactory->newSelect('users')->andWhere(['id' => 1]);
$row = $query->execute()->fetch('assoc');
Aggregate functions
The func() method returns an instance of a functions builder object that can be used for generating arbitrary SQL functions.
$query->select(['counter' => $query->func()->count('invoices.id')]);
$query->select(['amount_sum' => $query->func()->sum('payments.amount')]);
$query->select(['user_score_max' => $query->func()->max('users.score')]);
Custom functions
The newExpr() method returns a new QueryExpression object. This is a handy function when building complex queries using a fluent interface. You can also override this function in subclasses to use a more specialized QueryExpression class if required.
$query->select(
[
'date_of_birth' => $query->newExpr("DATE_FORMAT(users.date_of_birth,'%d.%m.%Y')"),
]
);
Read more: Selecting data
Insert
Insert a record
$values = [
'first_name' => 'john',
'last_name' => 'doe',
'email' => '[email protected]',
];
$this->queryFactory->newInsert('users', $values)->execute();
Insert a record and get the last inserted id:
$newId = (int)$this->queryFactory->newInsert('users', $values)
->execute()
->lastInsertId();
Read more: Inserting data
Update
Update a record:
$values = ['email' => '[email protected]'];
$this->queryFactory->newUpdate('users')
->set($values)
->andWhere(['id' => 1])
->execute();
Read more: Updating data
Delete
Delete a record:
$this->queryFactory->newDelete('users')
->andWhere(['id' => 1])
->execute();
Read more: Deleting data
Handling relationships
You can define relationships directly with a join clause.
In addition to join()
you can use rightJoin()
, leftJoin()
and innerJoin()
to create joins:
$query = $this->queryFactory->newSelect('users');
$query->select(['users.*']);
$query->innerJoin('contacts', 'contacts.user_id = users.id');
$query->leftJoin('orders', 'orders.user_id = users.id');
$rows = $query->execute()->fetchAll('assoc');
Transactions
The transaction handling can be abstracted away with this interface:
<?php
namespace App\Database;
interface TransactionInterface
{
public function begin(): void;
public function commit(): void;
public function rollback(): void;
}
The TransactionInterface
could be implemented as follows:
<?php
namespace App\Database;
use Cake\Database\Connection;
/**
* Transaction handler.
*/
final class Transaction implements TransactionInterface
{
/**
* @var Connection The database connection
*/
private $connection;
public function __construct(Connection $connection)
{
$this->connection = $connection;
}
public function begin(): void
{
$this->connection->begin();
}
public function commit(): void
{
$this->connection->commit();
}
public function rollback(): void
{
$this->connection->rollback();
}
}
Add a container definition for App\Database\TransactionInterface::class
:
<?php
use App\Database\Transaction;
use App\Database\TransactionInterface;
use Cake\Database\Connection;
use Psr\Container\ContainerInterface;
return [
// ...
TransactionInterface::class => function (ContainerInterface $container) {
return new Transaction($container->get(Connection::class);
},
];
Transaction handling example
You should orchestrate all transactions in a service class. Please don’t use the transaction handler directly within a repository.
Example
<?php
namespace App\Domain\User\Service;
use App\Domain\User\Repository\UserCreatorRepository;
use App\Factory\LoggerFactory;
use App\Database\TransactionInterface;
use Exception;
use Psr\Log\LoggerInterface;
final class UserCreator
{
private $repository;
private $transaction;
private $logger;
public function __construct(
UserCreatorRepository $repository,
TransactionInterface $transaction,
LoggerFactory $loggerFactory
) {
$this->repository = $repository;
$this->transaction = $transaction;
$this->logger = $loggerFactory
->addFileHandler('user_creator.log')
->createInstance('user_creator');
}
public function createUser(array $formData): void
{
// Input validation
// ...
$this->transaction->begin();
try {
// ...
$userId = $this->repository->insertUser($userData);
// Do more database operations
// ...
$this->logger->info(sprintf('User created successfully: %s', $userId));
// Commit all changes
$this->transaction->commit();
} catch (Exception $exception) {
// Revert all changes
$this->transaction->rollback();
$this->logger->error($exception->getMessage());
}
}
}
© 2020 Daniel Opitz | Twitter
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK