QueryObjects.rst

Query objects

Query objects use 2 main interfaces

• Imatic\Bundle\DataBundle\Data\Query\QueryObjectInterface
  • used to mark an object as query object
• Imatic\Bundle\DataBundle\Data\Query\QueryExecutorInterface
  • used to retrieve result from given query object (data the query object describes how to access or affected number of rows by the executed query object)
  • default implementation is QueryExecutor

Querying data

First we need to create query object. Query object is object implementing Imatic\Bundle\DataBundle\Data\Query\QueryObjectInterface. We won't implement this interface directly though. Instead, we will implement interface of some driver.

Query objects can implement additional interfaces to affect how result will look like

• Imatic\Bundle\DataBundle\Data\Query\ScalarResultQueryObjectInterface
  • used to tell that a query object returns scalar result when executed
  • result of executing query by query executor will be scalar result
• Imatic\Bundle\DataBundle\Data\Query\SingleResultQueryObjectInterface
  • used to tell that a query object returns single row/object
  • result of executing query by query executor will be
    • single row
    • null in case query returned no value
    • Imatic\Bundle\DataBundle\Data\Query\NonUniqueResultException in case query returned more rows
• Imatic\Bundle\DataBundle\Data\Query\SingleScalarResultQueryObjectInterface
  • used to tell that a query object returns single scalar result when executed
  • result of executing query by query executor will be
    • single scalar value
    • Imatic\Bundle\DataBundle\Data\Query\NoResultException in case query returned no rows
    • Imatic\Bundle\DataBundle\Data\Query\NonUniqueResultException in case query returned more rows or columns

DoctrineDBAL driver

• Imatic\Bundle\DataBundle\Data\Driver\DoctrineDBAL\QueryObjectInterface
  • uses doctrine dbal to query data
  • contains method build returning query builder which has all required info to execute the query

Example of query object for retrieving active users

First we create our query object

<?php

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Query\QueryBuilder
use Imatic\Bundle\DataBundle\Data\Driver\DoctrineDBAL\QueryObjectInterface;

class ActiveUsersQuery implements QueryObjectInterface
{
    public function build(Connection $connection): QueryBuilder
    {
        return $connection->createQueryBuilder()
            ->select('u.*')
            ->from('user', 'u')
            ->where('u.active = :active')
            ->setParameter('active', true);
    }
}

Then we can execute it using query executor

DoctrineORM driver

• Imatic\Bundle\DataBundle\Data\Driver\DoctrineORM\QueryObjectInterface
  • uses doctrine orm to query data
  • contains method build returning query builder which has all required info to execute the query

Example of query object for retrieving active users

<?php

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\QueryBuilder;
use Imatic\Bundle\DataBundle\Data\Driver\DoctrineORM\QueryObjectInterface;

class ActiveUsersQuery implements QueryObjectInterface
{
    public function build(EntityManager $em): QueryBuilder
    {
        return $em->getRepository(User::class)->createQueryBuilder('u')
            ->select('u')
            ->where('u.active = :active')
            ->setParameter('active', true);
    }
}

Then we can execute it using query executor

Updating data

• query objects can be also used for updating/deleting data. Not just selecting them.

Example of deleting user with username eva using query objects

First we need to create query object that will delete users by username

<?php

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Query\QueryBuilder
use Imatic\Bundle\DataBundle\Data\Driver\DoctrineDBAL\QueryObjectInterface;

class DeleteUserByUsernameQuery implements QueryObjectInterface
{

    private $username;

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

    public function build(Connection $connection): QueryBuilder
    {
        return $connection->createQueryBuilder()
            ->delete('user', 'u')
            ->where('u.username = :username')
            ->setParameter('username', $this->username);
    }
}

Then we can execute it using query executor

<?php

$queryExecutor = $container->get('Imatic\Bundle\DataBundle\Data\Query\QueryExecutor');

$queryExecutor->execute(new DeleteUserByUsernameQuery('eva'));

QueryExecutor

• query executor is service which is able to execute given query object
• it has 3 methods
  • execute
    • used to execute given query object and retrieve result
  • count
    • used to find out how many records there will be if given query object is executed without pagination
  • executeAndCount
    • combination of previous 2 (returns results and number of all results without pagination)
• all of the methods accept 2nd optional argument $displayCriteria which specifies filtering, sorting and pagination (more on that later)

Example of using query executor

<?php

$queryExecutor = $container->get('Imatic\Bundle\DataBundle\Data\Query\QueryExecutor');

$allActiveUsers = $queryExecutor->execute(new ActiveUsersQuery());
$totalNumberOfActiveUsers = $queryExecutor->count(new ActiveUsersQuery());

Using multiple Entity Managers and Connections

In case application is using multiple connections or entity managers, connection can be specified by implementing ConnectionQueryObjectInterface and returning name of the connection in getConnectionName method.

<?php

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Query\QueryBuilder
use Imatic\Bundle\DataBundle\Data\Driver\DoctrineDBAL\QueryObjectInterface;
use Imatic\Bundle\DataBundle\Data\Query\ConnectionQueryObjectInterface;

class UserConfigQuery implements QueryObjectInterface, ConnectionQueryObjectInterface
{
    public function build(Connection $connection): QueryBuilder
    {
        // ...
    }

    public function getConnectionName(): string
    {
        return 'config';
    }
}

Entity manager can be specified by implementing ManagerQueryObjectInterface and returning name of the manager in getManagerName method.

<?php

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\QueryBuilder;
use Imatic\Bundle\DataBundle\Data\Driver\DoctrineORM\ManagerQueryObjectInterface;
use Imatic\Bundle\DataBundle\Data\Driver\DoctrineORM\QueryObjectInterface;

final class ListQuery implements QueryObjectInterface, ManagerQueryObjectInterface
{
    public function build(EntityManager $em): QueryBuilder
    {
        // ...
    }

    public function getManagerName(): string
    {
        return 'customer';
    }
}

Making query object selectable

• marking query object selectable allows us to select required rows by some unique value (usually primary key)
• it can be made selectable by implementing SelectableQueryObjectInterface
  • it has 1 method getIdentifierFilterKey which returns name of the filter we want to select rows by
  • it's used by record iterator typically used by commands to iterate over result with use of pagination

Executing query objects from console

• query objects can be executed from console using imatic:data:query-object-query console command

Example of executing query object returning list of users

./bin/console imatic:data:query-object-query 'App\Query\UserListQuery'

Example of executing query object returning single user with id passed via query object constructor

./bin/console imatic:data:query-object-query 'App\Query\UserQuery' --args 1