Doctrine DTO with native SQL queries

Question

I currently have a fairly complex native SQL query which is used for reporting purposes. Given the amount of data it processes this is the only efficient way to handle it is with native SQL.

This works fine and returns an array of arrays from the scalar results.

What I'd like to do, to keep the results consistent with every other result set in the project is use a Data Transfer Object (DTO). Returning an array of simple DTO objects.

These work really well with DQL but I can't see anyway of using them with native SQL. Is this at all possible?

Answer 1

Doctrine can map the results of a raw SQL query to an entity, as shown here:

http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/native-sql.html

I cannot see support for DTOs unless you are willing to use DQL as well, so a direct solution does not exist. I tried my hand at a simple workaround that works well enough, so here are the DQL and non-DQL ways to achieve your goal.

The examples were built using Laravel and the Laravel Doctrine extension.

1. The DTO

The below DTO supports both DQL binding and custom mapping so the constructor must be able to work with and without parameters.

<?php namespace App\Dto;

/**
 * Date with corresponding statistics for the date.
 */
class DateTotal
{
    public $taskLogDate;
    public $totalHours;

    /**
     * DateTotal constructor.
     *
     * @param $taskLogDate The date for which to return totals
     * @param $totalHours The total hours worked on the given date
     */
    public function __construct($taskLogDate = null, $totalHours = null)
    {
        $this->taskLogDate = $taskLogDate;
        $this->totalHours = $totalHours;
    }

}

2. Using DQL to fetch results

Here is the standard version, using DQL.

public function findRecentDateTotals($taskId)
{
    $fromDate = new DateTime('6 days ago');
    $fromDate->setTime(0, 0, 0);
    $queryBuilder = $this->getQueryBuilder();

    $queryBuilder->select('NEW App\Dto\DateTotal(taskLog.taskLogDate, SUM(taskLog.taskLogHours))')
        ->from('App\Entities\TaskLog', 'taskLog')
        ->where($queryBuilder->expr()->orX(
            $queryBuilder->expr()->eq('taskLog.taskLogTask', ':taskId'),
            $queryBuilder->expr()->eq(0, ':taskId')
        ))
        ->andWhere(
            $queryBuilder->expr()->gt('taskLog.taskLogDate', ':fromDate')
        )
        ->groupBy('taskLog.taskLogDate')
        ->orderBy('taskLog.taskLogDate', 'DESC')
        ->setParameter(':fromDate', $fromDate)
        ->setParameter(':taskId', $taskId);

    $result = $queryBuilder->getQuery()->getResult();
    return $result;
}

3. Support for DTOs with native SQL

Here is a simple helper that can marshal the array results of a raw SQL query into objects. It can be extended to do other stuff as well, perhaps custom updates and so on.

<?php namespace App\Dto;

use Doctrine\ORM\EntityManager;

/**
 * Helper class to run raw SQL.
 *
 * @package App\Dto
 */
class RawSql
{
    /**
     * Run a raw SQL query.
     *
     * @param string $sql The raw SQL
     * @param array $parameters Array of parameter names mapped to values
     * @param string $className The class to pack the results into
     * @return Object[] Array of objects mapped from the array results
     * @throws \Doctrine\DBAL\DBALException
     */
    public static function query($sql, $parameters, $className)
    {
        /** @var EntityManager $em */
        $em = app('em');
        $statement = $em->getConnection()->prepare($sql);
        $statement->execute($parameters);
        $results = $statement->fetchAll();

        $return = array();
        foreach ($results as $result) {
            $resultObject = new $className();
            foreach ($result as $key => $value) {
                $resultObject->$key = $value;
            }
            $return[] = $resultObject;
        }
        return $return;
    }
}

4. Running the raw SQL version

The function is used and called in the same way as other repository methods, and just calls on the above helper to automate the conversion of data to objects.

public function findRecentDateTotals2($taskId)
{
    $fromDate = new DateTime('6 days ago');

    $sql = "
        SELECT
          task_log.task_log_date AS taskLogDate,
          SUM(task_log.task_log_hours) AS totalHours
        FROM task_log task_log
        WHERE (task_log.task_log_task = :taskId OR :taskId = 0) AND task_log.task_log_date > :fromDate
        GROUP BY task_log_date
        ORDER BY task_log_date DESC        
    ";

    $return = RawSql::query(
        $sql,
        array(
            'taskId' => $taskId,
            'fromDate' => $fromDate->format('Y-m-d')
        ),
        DateTotal::class
    );
    return $return;
}

5. Notes

I would not dismiss DQL too quickly as it can perform most kinds of SQL. I have however also recently been involved in building management reports, and in the world of management information the SQL queries can be as large as whole PHP files. In that case I would join you and abandon Doctrine (or any other ORM) as well.