PHPackages                             gowork/dqo - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. gowork/dqo

ActiveLibrary[Database & ORM](/categories/database)

gowork/dqo
==========

Database Query Objects

0.5.7(2mo ago)527.9k—4.2%2MITPHPPHP >=8.2CI passing

Since Oct 11Pushed 2mo ago4 watchersCompare

[ Source](https://github.com/gowork/dqo)[ Packagist](https://packagist.org/packages/gowork/dqo)[ RSS](/packages/gowork-dqo/feed)WikiDiscussions master Synced today

READMEChangelog (10)Dependencies (28)Versions (45)Used By (0)

Database Query Objects
======================

[](#database-query-objects)

Introduction
------------

[](#introduction)

DQO provides an object representation of SQL database table, row and select query.

#### Features:

[](#features)

- Each database table can be described as `Table` class
- Enables column name completion in IDE while writing queries
- Provides a table columns enumeration as constants
- Each row returned from SELECT query can be described as `Row` class
- Table specific deserialization recipes can be added to corresponding `Row` class
- `Table` and `Row` classes code can be generated with Symfony console command
- Provides immutable `DatabaseSelectBuilder` for building SELECT queries

DQO is based on Doctrine DBAL and uses Doctrine Types for data deserialization and `Doctrine\DBAL\Connection` for query execution.

### `Table` definition

[](#table-definition)

Classes representing specific database tables. It contains enumeration of table columns as constants and simplifies field aliasing. Multiple instances can be created with different aliases.

```
final class UserTable extends GW\DQO\Table
{
    public const ID = 'id';
    public const EMAIL = 'email';
    public const NAME = 'name';

    public function id(): string
    {
        return $this->fieldPath(self::ID);
    }

    public function email(): string
    {
        return $this->fieldPath(self::EMAIL);
    }

    public function name(): string
    {
        return $this->fieldPath(self::NAME);
    }

    public function createRow(array $raw): UserRow
    {
        return new UserRow($raw, $this);
    }
}

$userTable = new UserTable('user_alias');
$userTable->table(); // "user"
$userTable->alias(); // "user_alias"
$userTable->id(); // "user_alias.id"
$userTable->selectField(UserTable::ID); // "user_alias.id as user_alias_id"
```

### `TableRow` definition

[](#tablerow-definition)

Classes that can be created to unify data extracting and deserializing from corresponding table.

```
final class UserRow extends ClientRow
{
    public function id(): UserId
    {
        return $this->getThroughType('UserId', UserTable::ID);
    }

    public function name(): string
    {
        return $this->getString(UserTable::NAME);
    }

    public function email(): Email
    {
        return Email::fromString($this->getString(UserTable::EMAIL));
    }

    public function optionalSecondEmail(): ?Email
    {
        return $this->getThrough([Email::class, 'fromString'], UserTable::OPTIONAL_SECOND_EMAIL);
    }

    public function about(): ?string
    {
        return $this->getNullableString(UserTable::NAME);
    }
}

$userTable = new UserTable();
$userRow = new UserRow($rowFromQuery, $userTable);
```

### Building SELECT query with `DatabaseSelectBuilder`

[](#building-select-query-with-databaseselectbuilder)

`DatabaseSelectBuilder` simplifies construction of SELECT statements using `Table` objects.

```
/** @var Doctrine\DBAL\Connection $connection */
$builder = new GW\DQO\DatabaseSelectBuilder($connection);

$meTable = new UserTable('me');
$friendTable = new UserTable('friend');

$builder
    ->from($meTable)
    ->join($friendTable, "{$friendTable->id()} = {$meTable->friendId()}")
    ->where("{$meTable->username()} = :me", ['me' => 'John Doe'])
    ->select($friend->name())
    ->offsetLimit(0, 10);
```

#### SELECT column aliases

[](#select-column-aliases)

By default `TableRow` expects that table column used in SELECT part has alias as follows: `table_alias.column_name as table_alias_column_name`.

There are 2 ways to create such alias:

- Use `Table` methods creating column aliases ```
    $table = new UserTable();

    $builder = $builder->select(...$table->select(UserTable::ID, UserTable::email));
    // or
    $builder = $builder->select($table->selectField(UserTable::ID), $table->selectField(UserTable::email));
    // or
    $builder = $builder->select(...$table->selectAll());
    ```
- Use simply `$table->column()` when `select()` is after `table()` or `join()````
    $table = new UserTable();

    // first add $table to builder so it can recognize `user.id`, `user.email` and create valid aliases...
    $builder = $builder->from($table);

    // ...then simply select
    $builder = $builder->select($table->id(), $table->email());
    ```

#### Query parameters

[](#query-parameters)

Query parameters can be specified directly in `where/having` method or provided later.

```
$builder = $builder->from($user)
    ->where("{$user->name()} = :name", ['name' => 'John Doe'])
    ->having('orders > :limit', ['limit' => 10]);

// or

$builder = $builder->from($user)
    ->where("{$user->name()} = :name")
    ->withParameter('name', 'John Doe');

// or

$builder = $builder->from($user)
    ->where("{$user->name()} = :name")
    ->withParameters(['name' => 'John Doe']);
```

Query parameter types can be specified as `where()` argument.

```
$yesterday = new DateTime('yesterday');
$builder = $builder
    ->from($user)
    ->where("{$user->registered()} > :yesterday", ['yesterday' => $yesterday], ['yesterday' => 'datetime']);
```

You can also define mapping of parameter classes to proper Doctrine type.

```
$start = new DateTimeImmutable('first day of last month 00:00');
$end = new DateTimeImmutable('last day of last month 23:59');
$builder = $builder
    ->withTypes([DateTimeImmutable::class => 'datetime_immutable'])
    ->from($user)
    ->where("{$user->registered()} BETWEEN :start AND :end", ['start' => $start, 'end' => $end]);
```

#### Fetching results

[](#fetching-results)

```
/** @var array|null $result one result row or null when there are no rows */
$result = $builder->fetch();

/** @var mixed|null $result one column from first result or null when no results */
$result = $builder->fetchColumn();

/** @var array $result fetch all result rows */
$result = $builder->fetchAll();

/**
 * @var ArrayValue $result
 * @see https://github.com/gowork/values
 */
$result = $builder->wrapAll();

/** @var int $result */
@result = $builder->count();
```

Install
-------

[](#install)

```
composer require gowork/dqo
```

Setup
-----

[](#setup)

### Symfony

[](#symfony)

Add the DatabaseAccessGeneratorBundle to your application's kernel (only on `dev` environment):

```