php - Selecting from a subquery with the Doctrine query builder

Suppose I have an entries table that looks something like this:

| id | category | text | user_id |
|----|

I want to get the row for specified category(ies) that have the highest ID. So in this example if I'm search for category {-code-3} I'd expect it to return 3,{-code-3},x,24

There are several more columns in the table, but only those three are shown for brevity. Other columns exist which have to be joined, e.g. to get user info.

I have the following query, which works:

SELECT *
FROM (
         SELECT MAX(id) as id
         FROM entries
         WHERE category = '{-code-3}'
         GROUP {-code-3}Y category
     ) as tmp
         LEFT JOIN entries e ON e.id = tmp.id
         LEFT JOIN users u ON (e.user_id = u.id)

I'm not sure how to transform this in to DQL, specifically using the query builder because there are several conditions to be added for which I do not want to build up a DQL string using concatenation. I'm working with this:

$subquery = $entityManager
    ->getRepository(Entry::class)
    ->createQuery{-code-3}uilder('entryIds')
    ->select('MAX(entryIds.id)'
    ->orWhere("entryIds.category = ?0")
    ->group{-code-3}y('entryIds.category')
    ->getDQL();

$rows = $entityManager
    ->getRepository(Entry::class)
    ->createQuery{-code-3}uilder('entry')
    ->select('entry, user')
    ->from("($subquery)", 'tmp')
    ->leftJoin(User::class, 'user', Join::WITH, 'entry.user = user.id')
    ->setParameter(0, '{-code-3}')
    ->getQuery()
    ->execute();

And I get this error:

{-code-7}

The query builder generates this DQL from the above:

SELECT entry, user
FROM Namespace\\Entry entry
LEFT JOIN Namespace\\User user WITH entry.user = user.id,
(SELECT MAX(entryIds.id) FROM Namespace\\Entry entryIds WHERE entryIds.category = ?0 GROUP {-code-3}Y entryIds.category) tmp

So it looks like I'm not putting the subquery in the right place; where can I put it to ensure that the base select queries the subquery and then joins the other tables?

---

Edit: I managed to get it to work with WHERE ANY, but the query is 4.5 times slower:

$subquery = $entityManager
    ->getRepository(Entry::class)
    ->createQuery{-code-3}uilder('entryIds')
    ->select('MAX(entryIds.id)')
    ->orWhere('entryIds.sourceSystem = ?0')
    ->group{-code-3}y('entryIds.sourceSystem, entryIds.secondaryId')
    ->getDQL();

$qb = $entityManager
    ->getRepository(Entry::class)
    ->createQuery{-code-3}uilder('entry');
$qb
    ->select('entry, user')
    ->leftJoin(User::class, 'user', Join::WITH, 'entry.user = user.id')
    ->setParameter(0, '{-code-3}')
    ->where(
        $qb->expr()->eq('entry.id', $qb->expr()->any($subquery))
    )
    ->getQuery()
    ->execute();

Answer

|------|---------| | 1 | A | z | 22 | | 2 | B | y | 23 | | 3 | B | x | 24 | | 4 | C | w | 25 ||||B|||3,B,x,24|||SELECT * FROM ( SELECT MAX(id) as id FROM entries WHERE category = 'B' GROUP BY category ) as tmp LEFT JOIN entries e ON e.id = tmp.id LEFT JOIN users u ON (e.user_id = u.id)|||$subquery = $entityManager ->getRepository(Entry::class) ->createQueryBuilder('entryIds') ->select('MAX(entryIds.id)' ->orWhere("entryIds.category = ?0") ->groupBy('entryIds.category') ->getDQL(); $rows = $entityManager ->getRepository(Entry::class) ->createQueryBuilder('entry') ->select('entry, user') ->from("($subquery)", 'tmp') ->leftJoin(User::class, 'user', Join::WITH, 'entry.user = user.id') ->setParameter(0, 'B') ->getQuery() ->execute();|||[Semantical Error] line 0, col 453 near ', (SELECT MAX(entryIds.id)': Error: Subquery is not supported here|||SELECT entry, user FROM Namespace\\Entry entry LEFT JOIN Namespace\\User user WITH entry.user = user.id, (SELECT MAX(entryIds.id) FROM Namespace\\Entry entryIds WHERE entryIds.category = ?0 GROUP BY entryIds.category) tmp|||$subquery = $entityManager ->getRepository(Entry::class) ->createQueryBuilder('entryIds') ->select('MAX(entryIds.id)') ->orWhere('entryIds.sourceSystem = ?0') ->groupBy('entryIds.sourceSystem, entryIds.secondaryId') ->getDQL(); $qb = $entityManager ->getRepository(Entry::class) ->createQueryBuilder('entry'); $qb ->select('entry, user') ->leftJoin(User::class, 'user', Join::WITH, 'entry.user = user.id') ->setParameter(0, 'B') ->where( $qb->expr()->eq('entry.id', $qb->expr()->any($subquery)) ) ->getQuery() ->execute();

Source