Getting Doctrine DQL results the SQL way

Question

When performing a DQL query such as:

SELECT u AS user, t AS transaction
FROM Model\User u
JOIN Model\Transaction t WITH t.user = u

You get alternating rows of results, such as:

• ['user' => Model\User(1)]
• ['transaction' => Model\Transaction(1)]
• ['transaction' => Model\Transaction(2)]
• ['user' => Model\User(2)]
• ['transaction' => Model\Transaction(3)]
• ['transaction' => Model\Transaction(4)]
• ['transaction' => Model\Transaction(5)]

Is it possible to get the result the SQL way, like:

• ['user' => Model\User(1), 'transaction' => Model\Transaction(1)]
• ['user' => Model\User(1), 'transaction' => Model\Transaction(2)]
• ['user' => Model\User(2), 'transaction' => Model\Transaction(3)]
• ['user' => Model\User(2), 'transaction' => Model\Transaction(4)]
• ['user' => Model\User(2), 'transaction' => Model\Transaction(5)]

It would be much easier to deal with than alternating objects.

Dinny

Answer 1

The way I do it now is:

$query = $em->createQuery('
    SELECT u, t
    FROM Model\User u
    JOIN Model\Transaction t WITH t.user = u
');

$rows = $query->getResult();
$rowCount = count($rows);

$result = [];

for ($i = 0; $i < $rowCount; $i += 2) {
    /** @var Model\User $user */
    $user = $rows[$i];

    /** @var Model\Transaction $transaction */
    $transaction = $rows[$i + 1];

    $result[] = new UserTransactionDTO($user, $transaction);
}

return $result;

Which is clean enough.

Note that this example is a bad one, as you could only return Transactions and get the User from there; but I regularly encounter use cases where a single object does not hold all the information.

Dinny

Answer 2

Unfortunately, there's no current easy way of achieve this. However, there's a way that you could get that result.

Create a class named UserTransactionDTO and accept 2 constructor arguments: User and Transaction.

Now rewrite your DQL query like this:

SELECT NEW UserTransactionDTO(user, transaction)
  FROM Model\User u
  JOIN Model\Transaction t WITH t.user = u

This should give you a result that matches your desired behavior (a list of UserTransactionDTO objects), allowing you to access both User and Transaction on a single record.

Excrescence

Thanks, I did try this, but my constructor receives the object ids, and not the objects, as arguments! Maybe the NEW keyword is only for scalars? – Dinny

From doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/… "Note that you can only pass scalar expressions to the constructor." – Patrimony

One thing I didn't mention is that I work on Doctrine project. Supporting this is a matter of opening an issue. However, the main issue should be addressed on Doctrine 3, as I'm currently working on it. – Excrescence