php - Selecting from a subquery with the Doctrine query builder

Suppose I have anentries table that looks something like this:

| id | category | text | user_id |
|----|
Copy code

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 return3,{-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)

Copy code

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();
Copy code

And I get this error:

{-code-7}
Copy code

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
Copy code

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();
Copy code

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.