5

Slim 4 - Cycle Query Builder

 2 years ago
source link: https://odan.github.io/2021/09/12/slim-cycle-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.
Cycle Query Builder

Daniel Opitz - Blog

Developer, Trainer, Open Source Contributor

Blog About me Donate

Slim 4 - Cycle Query Builder

Daniel Opitz

Daniel Opitz

12 Sep 2021

Table of contents

Requirements

Introduction

The Cycle query builder provides flexible configuration options, a powerful query builder.

For this reason, I want to take a closer look at this exciting library and show how you can integrate it into a Slim Framework project.

Installation

To add the cycle/database package to your application, run:

composer require cycle/database

Configuration

In order to operate, the component requires a proper database connection to be set. All database connections are managed using the DatabaseManager service provided. You can configure your first database connection to be initiated on demand using the following configuration in your configuration file:

// Database settings
$settings['db'] = [
    'default' => 'default',
    'databases' => [
        'default' => ['connection' => 'default']
    ],
    'connections' => [
        'default' => [
            'driver' => \Cycle\Database\Driver\MySQL\MySQLDriver::class,
            'connection' => 'mysql:host=127.0.0.1;dbname=test',
            'username' => '',
            'password' => '',
        ]
    ]
];

Next, add a DI container definitions for the DatabaseManager and DatabaseInterface:

<?php

use Cycle\Database\Config\DatabaseConfig;
use Cycle\Database\DatabaseInterface;
use Cycle\Database\DatabaseManager;
use Psr\Container\ContainerInterface;
// ...

return [

    // ...
    
    DatabaseManager::class => function (ContainerInterface $container) {
        $dbConfig = $container->get('settings')['db'];

        return new DatabaseManager(new DatabaseConfig($dbConfig));
    },

    DatabaseInterface::class => function (ContainerInterface $container) {
        return $container->get(DatabaseManager::class)->database('default');
    },
];

The next step is optional. To fetch the PDO instance from the default connection you could add this container definition:

<?php

use Cycle\Database\DatabaseManager;
use Psr\Container\ContainerInterface;
// ...

return [
    // ...
    PDO::class => function (ContainerInterface $container) {
        $driver = $container->get(DatabaseManager::class)->driver('default');
        $class = new ReflectionClass($driver);
        $method = $class->getMethod('getPDO');
        $method->setAccessible(true);

        return $method->invoke($driver);
    },
];

Please note: I had to use Reflection to get access to the PDO object. If you know a better way, please let me know.

Accessing Query Builder

You can get access to the query builder by declaring the DatabaseInterface in the class constructor.

<?php

namespace App\Domain\User\Repository;

use Cycle\Database\DatabaseInterface;

final class UserRepository
{
    private DatabaseInterface $database;

    public function __construct(DatabaseInterface $database)
    {
        $this->database = $database;
    }

    // ...
}

Usage

Select

Query all rows:

$select = $this->database->select()->from('users');
$select->columns(['id', 'username']);

$rows = $select->fetchAll();

foreach ($rows as $row) {
    print_r($row);
}

Simple conditions

$select->where('status', 'active');

Select a single row by id:

$select = $this->database->select()
    ->from('users');
    ->columns(['id', 'username']);
    ->where('id', 1);

$row = $select->run()->fetch();

Read more: SelectQuery Builder

Insert

Insert a record

$insert = $this->database->insert('users');

$values = [
    'first_name' => 'john',
    'last_name' => 'doe',
    'email' => '[email protected]',
];

$insert->values($values)->run();

Insert a record and get the last inserted id:

$newId = (int)$insert->values($values)->run();

Read more: Insert Builder

Update

Update a record:

$values = ['email' => '[email protected]'];

$update = $this->database->table('users')->update($values);
$update->where('id', '=', 1);
$update->run();

Read more: UpdateQuery Builder

Delete

Delete a record:

$this->database->table('users')->delete()
    ->where('id', '=', 1)
    ->run();

Read more: DeleteQuery Builders

Handling relationships

ou can join any desired table to your query using leftJoin, join, rightJoin, fullJoin and innerJoin methods:

$select = $this->database->select()->from('posts');

$select->columns(
    [
        'posts.id',
        'posts.title',
        'users.id as user_id'
    ]
);

$select->innerJoin('users')->on('users.id', 'posts.user_id');

$rows = $select->fetchAll();

Read more: Joins

Transactions

Transactions should be handles in a server, but the implementation should happen in the infrastructure layer.

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 Cycle\Database\DatabaseInterface;

final class Transaction implements TransactionInterface
{
    private $database;

    public function __construct(DatabaseInterface $database)
    {
        $this->database = $database;
    }

    public function begin(): void
    {
        $this->database->begin();
    }

    public function commit(): void
    {
        $this->database->commit();
    }

    public function rollback(): void
    {
        $this->database->rollback();
    }
}

Add a container definition for App\Database\TransactionInterface::class:

<?php

use App\Database\Transaction;
use App\Database\TransactionInterface;
use Psr\Container\ContainerInterface;

return [
    TransactionInterface::class => function (ContainerInterface $container) {
        return $container->get(Transaction::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;

final class UserCreator
{
    private UserCreatorRepository $repository;
    private TransactionInterface $transaction;

    public function __construct(
        UserCreatorRepository $repository,
        TransactionInterface $transaction
    ) {
        $this->repository = $repository;
        $this->transaction = $transaction;
    }

    public function createUser(array $formData): void
    {
        // Input validation
        // ...

        // Start database transaction
        $this->transaction->begin();

        try {
            // Execute multiple commands
            $userId = $this->repository->insertUser($userData);
            
            // Do more operations
            // ...    
    
            // Commit the active database transaction
            $this->transaction->commit();
        } catch (Exception $exception) {
            // Rollback the active database transaction
            $this->transaction->rollback();
        }
    }
}

Read more

© 2021 Daniel Opitz | Twitter


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK