Doctrine - DQL - How to query a query result?

Question

I did not find how to query (with DQL) a result of a first DQL query.

I have a first DQL query like:

$query = $this->createQueryBuilder('q')
$query->[methods()]
$query->->getQuery()->getResult();

and from this query result I would like to do another DQL query. But the method getResult returns an array.

The perfect solution to get would be to do another DQL query on the result of the first query. Is there any solution for it?

Edit: I am querying in the repository file ("[Entity]Repository.php"). From my first Doctrine query, I am expecting to get a first result. And from this result I would like to perform another select query.
In another terms, I am wondering how I may 'transform' the result of my first query in a 'temporary' table from which I will perform another Doctrine query.

Answer 1

If you need to make a query using the results of another query you can use a subquery. For example:

$qb  = $this->createQueryBuilder('qb')->where('r.name = :name')->setParameter('name', $name);
$qb2 = $this->createQueryBuilder('qb2');

$qb->andWhere(
    $qb->expr()->in(
        'r.id',
        $qb2->select('u.id')
            ->from('AppBundle:User', 'user')
            ->getQuery()->getDQL()
    )
);

Answer 2

This sounds like you're looking to use the DTO functionality of Doctrine. It allows you to create a Data Transfer Object built from varying values collected from, either, a single Entity or from multiple Entities. "NEW" Operator Syntax

If you are wanting to retrieve only a single result and are not wanting to iterate over multiple results, you can use the getOneOrNullResult() method.

$query = $this->createQueryBuilder('q');
...
$query->select('NEW MyDTO(q.id, q.name, q1.id)');
$dto = $query->->getQuery()->getOneOrNullResult();

The returned DTO will contain the primitive values retrieved from the various tables you want to get.

An important thing to remember is that Doctrine ORM deals with Entities. It doesn't return just a few selected fields. You can select specific fields using partials, but they are highly discouraged. Using DTOs is preferred as they are not cached and provide a better transport vehicle.

Answer 3

You can't do exactly what you're thinking. It would be silly for Doctrine to go around creating temporary tables or other artifacts in the RDBMs for every query.

That said, your use-case isn't completely nuts; I can imagine a use case where you'd want an initial "wide" result set, and then a narrower subset thereof.

I can think of two ways you could approach this:

1. Leverage a single QueryBuilder instance to perform two queries in sequence, adding additional conditions along the way.

2. Wrap the array from getResult() in an ArrayCollection, and use the criteria API to filter the collection. The criteria API can't do everything that a raw DQL query can do, but it's quite powerful.

I'd start with the criteria API. The filtering happens in memory, avoiding another round-trip to the database, and (often more importantly) a bunch of hydration overhead.