Slim 4 - CakePHP Query Builder

Daniel Opitz
Daniel Opitz
03 Dec 2019

Table of contents

Requirements

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' => 'john.doe@example.com',
];

$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' => 'new@example.com'];

$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());
        }
    }
}